Database Design
Rule | Description | Value | Source | PRoblem Description |
1 | Excessive sorting and RID lookup Operations should be reduced with covered indexes. | Sys.dm_exec_sql_text Sys.dm_exec_cached_plans | Large data warehouse can benefit from more indexes. Indexes can be used to cover queries and avoid sorting. The cost of index overhead is only paid when data is loaded. | |
2 | Excessive fragmentation: Average fragmentation_in_percent should be <25% | >25% | sys.dm_db _index_physical_stats | Reducing index fragmentation through index rebuilds can benefit big range scans, common in data warehouse and Reporting scenarios. |
3 | Scans and ranges are common. Look for missing indexes | >= 1 | Perfmon object SQLServer access Methods Sys.dm_db_missing_index_group_stats Sys.dm_db_missing_index_groups Sys.dm_db_missing_index_details | A missing index flushes the cache. |
4 | Unused Indexes should be avoided | If an index is NEVER used, it will not appear in the DMV sys.dm_db_index_usage_stats | Index maintenance for unused indexes should be avoided. | |
Resource issue: 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 | Avoid plan reuse | > 25% | Perfmon object SQLServer Statistics | Data warehouse has fewer transactions than OLTP, each with significantly bigger IO. Therefore, having the correct plan is more important than reusing a plan. Unlike OLTP, data warehouse queries are not identical. |
3 | Parallelism: Cxpacket waits | <10% | Sys.dm_os_wait_stats | Parallelism is desirable in data warehouse or reporting workloads. |
Resource issue: Memory
Rule | Description | Value | Source | Problem Description |
1 | Memory grants pending | >1 | Perfmon object SQLServer Memory Manager | Memory grant not available for query to run. Check for Sufficient memory and page life expectancy. |
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. Look for possible missing index. |
Resource issue: IO
Rule | Description | Value | Source | Problem Description |
1 | Average Disk sec/read | >20 ms | Perfmon object Physical Disk | Reads should take 4-8ms without any IO pressure. |
2 | Average Disk sec/write | >20 ms | Perfmon object Physical Disk | Writes (sequential) can be as fast as 1 ms for transaction log. |
3 | Big scans | >1 | Perfmon object SQLServer Access Methods | A missing index flushes the cache. |
4 | If Top 2 values for wait stats are any of the following: ASYNCH_IO_COMPLETION IO_COMPLETION LOGMGR WRITELOG PAGEIOLATCH_x | Top 2 |
