Summary for Accidental DBA Book / Chapter 1: A Performance Troubleshooting Methodology

Chapter 1: A Performance Troubleshooting Methodology

Wait Statistics: the Basis for Troubleshooting

• Wait statistics is a good place to begin troubleshooting SQL Server performance problems. The Wait time statistics that SQL Server has to wait to execute an operation is tracked by SQLOS and can be viewed by querying sys.dm_os_wait_stats DMV. Combining this information with the information in PerfMon, and other DMVs can provide significant insight into the cause of the performance problems … p23.
• Those wait statistics are erased every time the server restarted, and keep accumulated after that. For analysis you need the period of accumulation to be not less than two weeks, in order to ensure the stats cover the entire workload … p24.
• Listing 1.1 is a query to get those waits that are non-problematic as they are a result of normal SQL Server operations … p24.
• Listing 1.2 is a query that filters out the non-problematic wait types, and finds the top ten cumulative wait events. Then a brief description what the writer conceder as a problematic wait types, each of these are covered in more details in later chapters … p25, p26, p27.
• Listing 1.3 is a command to reset wait statistics that was tracked by the server … P28.

Virtual File Statistics

• We have to examine virtual file statistics, along with wait statistics, because most SQL Servers will show signs of what looks like a disk I/O bottleneck, and using wait statistics alone, may lead to misdiagnose the root cause … P28.
• Listing 1.4 is querying sys.dm_io_virtual_file_stats function, which exposes virtual file statistics, such as providing cumulative physical I/O statistics, the number of reads and writes on each data file and on each log file (from which can be calculated the ratio of reads to write), and the number of I/O stalls and the stall time associated with the requests (which is the total amount of time sessions have waited for I/O to be completed on the file) … P29.

Performance Counters

• Performance Analysis of Logs (PAL) is a good tool to collect a large subset of windows as well as SQL Server counters. This tool is free and available by Microsoft … P30.
• Listing 1.5 is a query to sys.dm_os_performance_counters DMV which its result shows counters that provide critical information to determining how to continue with the troubleshooting process … P32, P33, P34, P35.
• In the result of the query of listing 1.5: a good situation is when “Index Searches/sec” is higher than “Full Scans/sec by a factor” by a factor of 800-1000 (both are Access Methods counter). If the number of “Full Scans/sec” is too high, then missing indexes resulting excess I/O operations … P36.
• In the result of the query of listing 1.5: One of the counters that may tell you if you have memory pressure is Page Life Expectancy (PLE), which is the number of seconds a page will remain in the data cache. To calculate the accepted value of PLE:
                   (Total RAM in GB/4) * 300
  PLE is consistently below this value value, and the server is experiencing high Lazy Writes/sec, which are page flushes from the buffer cache outside of the normal CHECKPOINT process, then the server is most likely experiencing data cache memory pressure, which will also increase the disk I/O being performed by the SQL Server … P36.
• In the result of the query of listing 1.5: The higher the number of SQL Compilations/sec in relation to the Batch Requests/sec, the more likely the SQL Server is experiencing an ad hoc workload that is not making optimal using of plan caching. The higher the number of SQL Re-Compilations/sec in relation to the Batch Requests/ sec, the more likely it is that there is an inefficiency in the code design that is forcing a recompile of the code being executed in the SQL Server… P37.
• The Memory Manager\Memory Grants Pending counter is the number of processes waiting on a workspace memory grant. If this counter is high, SQL Server may benefit from additional memory, but there may be query inefficiencies that are causing excessive memory grant requirements … P37.

Plan Cache Usage

• SQL Server stores execution information for each of the plans in the Plan Cache, until the plan is flushed from the cache. This information can be view by querying sys.dm_exec_query_stats DMV, as shown in Listing 1.6 which lists the top ten statements based on the average number of physical reads that the statements performed as a part of their execution.
  sys.dm_exec_query_stats DMV can also identify the statements that have taken the most CPU time, the longest execution time, or that have been executed the most frequently.
  Applying the plan_handle column value from the sys.dm_exec_query_stats into sys.dm_exec_query_plan() function can be used to get a specific plan which can be analyzed to identify problematic operations … P38, P39.

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786