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