| 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 |
