Database Design
Rule | Description | Value | Source | PRoblem Description |
1 | High Frequency queries having a high number of table joins. | >4 | Sys.dm_exec_sql_text Sys.dm_exec_cached_plans | High frequency queries with lots of joins can be too normalized for high OLTP scalability. |
2 | Frequently updated tables having # indexes. | >3 | Sys.indexes sys.dm_db_index_Operational_stats | Excessive index maintenance for OLTP. |
3 | Big IOs Table Scans Range Scans | >1 | Perfmon object SQLServer access Methods Sys.dm_exec_query_stats | A missing index flushes the cache. |
4 | Unused Indexes. | Index not in Sys.dm_db_index_usage_stats. If an index is NEVER used, it will not appear in the DMV sys.dm_db_index_usage_stats | Avoid Index maintenance for unused indexes. | |
CPU
Rule | Description | Value | Source | Problem Description |
1 | Signal Waits | >25% | Sys.dm_os_wait_stats | Time in runnable queue is pure CPU wait. |
2 | Plan reuse | <90% | Perfmon object SQLServer Statistics | OLTP identical transactions should ideally have >95% plan reuse. |
3 | Parallelism: Cxpacket waits | >5% | Sys.dm_os_wait_stats | Parallelism reduces OLTP throughput. CXPACKET indicates that multiple CPUs are working in parallel, dividing up the query in smaller pieces. Ordinarily a well tuned OLTP application would not parallelize unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction. |
Memory
Rule | Description | Value | Source | Problem Description |
1 | Page life expectancy | <300 sec | Perfmon object SQLServer Buffer Manager SQLServer Buffer Nodes | Page life expectancy is the average number of seconds a data page stays in cache. Low values could indicate a cache flush that is caused by a big read. Pure OLTP workloads do NOT issue big reads, thus possible missing index. |
2 | Page life expectancy | Drops by 50% | Perfmon object SQLServer Buffer Manager | Page life expectancy is the average number of seconds a data page stays in cache. Low values could indicate a cache flush that is caused by a big read. Pure OLTP workloads do NOT issue big reads, thus possible missing index. |
3 | Memory Grants Pending | >1 | Perfmon object SQLServer Memory Manager | Current number of processes waiting for a workspace memory grant. |
4 | SQL cache hit ratio | <90% | SQL cache hit ratio falls under 90% for sustained periods of time greater than 60 sec. | It is likely that large scans have to be performed, which in turn flushes out the buffer cache. |
IO
