Performance Profile of OLTP applications

Performance and Tuning Blue Prints

We will look at different types of applications, how they use resources, and how one would approach performance tuning each.  The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application.  It is helpful to understand these differences and the objectives for high performance.

OLTP blueprint

For example, OLTP applications are characterized by high volumes of small identical transactions.  These can include SELECT, INSERT, UPDATE and DELETE operations.  The implications are significant in terms of database design, resource utilization and system performance.  

OLTP Performance blue print objectives.  There are performance problems if any of the following are true.  Note: Actual value used in Value column can be debated

Resource issue Rule Description Value Source Problem Description

Database Design Rule 1 High Frequency queries having # table joins >4 Sys.dm_exec_sql_text, High Frequency queries with lots of joins may be too normalized for high OLTP scalability

Sys.dm_exec_cached_plans

Rule 2 Frequently updated tables having # indexes >3 Sys.indexes, sys.dm_db_operational_index_stats Excessive index maintenance for OLTP

Rule 3 Big IOs >1 Perfmon object Missing index, flushes cache

Table Scans SQL Server Access Methods

Range Scans Sys.dm_exec_query_stats

Rule 4 Unused Indexes index not in* * Sys.dm_db_index_usage_stats Index maintenance for unused indexes

CPU Rule 1 Signal Waits > 25% Sys.dm_os_wait_stats Time in runnable queue is

pure CPU wait.

Rule 2 Plan re-use < 90% Perfmon object OLTP identical transactions should ideally have >95% plan re-use

SQL Server Statistics

Rule 3 Parallelism: Cxpacket waits >5% Sys.dm_os_wait_stats Parallelism reduces OLTP throughput

Memory Rule 1 Avg page life expectancy < 300 (seconds) Perfmon object Cache flush, due to big read

SQL Server Buffer Manager Possible missing index

SQL Server Buffer Nodes

Rule 2 Avg page life expectancy Drops by 50% Perfmon object Cache flush, due to big read

SQL Server Buffer Manager Possible missing index

Rule 3 Memory Grants Pending >1 Perfmon object Current number of processes waiting for a workspace memory grant

SQL Server Memory Manager

IO Rule 1 Avg Disk seconds / read > 20 ms Perfmon object Reads should take 4-8ms with NO IO pressure

Physical Disk

Rule 2 Avg Disk seconds / write > 20 ms Perfmon object Writes (sequential) can be as fast as 1ms for transaction log.

Physical Disk

Rule 3 Big IOs >1 Perfmon object Missing index, flushes cache

Table Scans SQL Server Access Methods

Range Scans

Rule 4 If Top 2 values for wait stats are any of the following: Top 2 Sys.dm_os_wait_stats If top 2 wait_stats values include IO, there is an IO bottleneck

1. ASYNCH_IO_COMPLETION

2. IO_COMPLETION

3. LOGMGR

4. WRITELOG

5. PAGEIOLATCH_x

Blocking Rule 1 Block percentage > 2% Sys.dm_db_index_operational_stats Frequency of blocks

Rule 2 Block process report 30 sec Sp_configure, profiler Report of statements

Rule 3 Avg Row Lock Waits > 100ms Sys.dm_db_index_operational_stats Duration of blocks

Rule 4 If Top 2 values for wait stats are any of the following: Top 2 Sys.dm_os_wait_stats If top 2 wait_stats values include locking, there is a blocking bottleneck

1. LCK_x