Search This Blog

A Performance Profile of OLTP applications

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:

1.LCK_x

Top 2 Sys.dm_os_wait_stats If top 2 wait_stats values include locking, there is a blocking bottleneck

 

No comments: