{"id":591,"date":"2013-06-25T09:29:17","date_gmt":"2013-06-25T16:29:17","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=591"},"modified":"2017-09-25T19:23:06","modified_gmt":"2017-09-26T02:23:06","slug":"the-accidental-dba-day-25-of-30-wait-statistics-analysis","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-accidental-dba-day-25-of-30-wait-statistics-analysis\/","title":{"rendered":"The Accidental DBA (Day 25 of 30): Wait Statistics Analysis"},"content":{"rendered":"

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0several times each year<\/a>. You can find all the other posts in this series at\u00a0http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n

For the last set of posts in our Accidental DBA series we\u2019re going to focus on troubleshooting, and I want to start with Wait Statistics.\u00a0 When SQL Server executes a task, if it has to wait for anything \u2013 a lock to be released from a page, a page to be read from disk into memory, a write to the transaction log to complete \u2013 then SQL Server records that wait and the time it had to wait.\u00a0 This information accumulates, and can be queried using the sys.dm_os_wait_stats DMV, which was first available in SQL Server 2005.\u00a0 Since then, the waits and queues troubleshooting methodology has been a technique DBAs can use to identify problems, and areas for optimizations, within an environment.<\/p>\n

If you haven\u2019t worked with wait statistics, I recommend starting with Paul\u2019s wait stats post<\/a>, and then working through the SQL Server Performance Tuning Using Wait Statistics: A Beginners Guide<\/a>\u00a0whitepaper.<\/p>\n

Viewing Wait Statistics<\/strong><\/p>\n

If you run the following query:<\/p>\n

\r\nSELECT *\r\nFROM sys.dm_os_wait_stats\r\nORDER BY wait_time_ms DESC;\r\n<\/pre>\n
You will get back output that isn\u2019t that helpful, as you can see below:<\/p>\n
\"sys.dm_os_wait_stats<\/a>
sys.dm_os_wait_stats output<\/figcaption><\/figure>\n
It looks like FT_IFTS_SCHEDULER_IDLE_WAIT is the biggest wait, and SQL Server\u2019s waited for 1930299679 ms total.\u00a0 This is kind of interesting, but not what I really need to know.\u00a0 How I do really use<\/i> this data?\u00a0 It needs some filtering and aggregation.\u00a0 There are some waits that aren\u2019t going to be of interest because they occur all the time and are irrelevant for our purposes; we can filter out those wait types.\u00a0 To make the most of our wait stats output, I really want to know the highest wait based on the percentage of time spent waiting overall<\/i>, and the average wait time for that wait.\u00a0 The query that I use to get this information is the one from Paul\u2019s post (mentioned above).\u00a0 I won\u2019t paste it here (you can get it from his post) but if I run that query against my instance, now I get only three rows in my output:<\/p>\n
\"sys.dm_os_wait_stats<\/a>
sys.dm_os_wait_stats output with wait_types filtered out<\/figcaption><\/figure>\n
If we reference the various wait types listed in the MSDN entry for sys.dm_os_wait_stats<\/a>, we see that the SQLTRACE_WAIT_ENTRIES wait type, \u201cOccurs while a SQL Trace event queue waits for packets to arrive on the queue.\u201d<\/p>\n
Well, this instance is on my local machine and isn\u2019t very active, so that wait is likely due to the default trace that\u2019s always running.\u00a0 In a production environment, I probably wouldn\u2019t see that wait, and if I did, I\u2019d check to see how many SQL Traces were running.\u00a0 But for our purposes, I\u2019m going to add that as a wait type to filter out, and then re-run the query.\u00a0 Now there are more rows in my output, and the percentage for the PAGEIOLATCH_SH and LCK_M_X waits has changed:<\/p>\n
\"sys.dm_os_wait_stats<\/a>
sys.dm_os_wait_stats output with SQLTRACE_WAIT_ENTRIES also filtered out<\/figcaption><\/figure>\n
If you review the original query, you will see that the percentage calculation for each wait type uses the wait_time_ms for the wait divided by the SUM of wait_time_ms for all waits.\u00a0 But \u201call waits\u201d are those wait types not filtered by the query.\u00a0 Therefore, as you change what wait types you do not consider, the calculations will change.\u00a0 Keep this in mind when you compare data over time or with other DBAs in your company – it’s a good idea to make sure you’re always running the same query that filters out the same wait types.<\/p>\n
Capturing Wait Statistics<\/strong><\/p>\n
So far I\u2019ve talked about looking at wait statistics at a point in time.\u00a0 As a DBA, you want to know what waits are normal for each instance.\u00a0 And there will be waits for every instance; even if it\u2019s highly tuned or incredibly low volume, there will be waits.\u00a0 You need to know what\u2019s normal, and then use those values when the system is not performing well.<\/p>\n
The easiest way to capture wait statistics is to snapshot the data to a table on a regular basis, and you can find queries for this process in my Capturing Baselines for SQL Server: Wait Statistics<\/a> article on SQLServerCentral.com.\u00a0 Once you have your methodology in place to capture the data, review it on a regular basis to understand your typical waits, and identify potential issues before they escalate.\u00a0 When you do discover a problem, then you can use wait statistics to aid in your troubleshooting.<\/p>\n
Using the Data<\/strong><\/p>\n
At the time that you identify a problem in your environment, a good first step is to run your wait statistics query and compare the output to your baseline numbers.\u00a0 If you see something out of the ordinary, you have an idea where to begin your investigation.\u00a0 But that\u2019s it; wait statistics simply tell you where to start searching for your answer.\u00a0 Do not assume that your highest wait is the problem, or even that it\u2019s a problem at all.\u00a0 For example, a common top wait is CXPACKET, and CXPACKET waits indicate that parallelism is used, which is expected in a SQL Server environment.\u00a0 If that\u2019s your top wait, does that mean you should immediately change the MAXDOP setting for the instance?\u00a0 No.\u00a0 You may<\/em> end up changing it down the road, but a better direction is to understand why<\/em> that\u2019s the highest wait.\u00a0 You may have CXPACKET waits because you\u2019re missing some indexes and there are tons of table scans occurring.\u00a0 You don\u2019t need to change MAXDOP, you need to start tuning.<\/p>\n
Another good example is the WRITELOG wait type.\u00a0 WRITELOG waits occur when SQL Server is waiting for a log flush to complete.\u00a0 A log flush occurs when information needs to be written to the database\u2019s transaction log.\u00a0 A log flush should complete quickly, because when there is a delay in a log write, then the task that initiated the modification has to wait, and tasks may be waiting behind that.\u00a0 But a log flush doesn\u2019t happen instantaneously every single time<\/b>, so you will<\/em> have WRITELOG waits.\u00a0 If you see WRITELOG as your top wait, don\u2019t immediately assume you need new storage.\u00a0 You should only assume that you need to investigate further.\u00a0 A good place to start would be looking at read and write latencies, and since I\u2019ll be discussing monitoring IO more in tomorrow\u2019s post we\u2019ll shelve that discussion until then.<\/p>\n
You can get detailed information on what the various wait types mean from our comprehensive SQL Server Wait Types and Latch Classes library<\/a>. Here’s a link to the CXPACKET page<\/a>, for example.<\/p>\n
As you can see from these two examples, wait statistics are a starting point.\u00a0 They are very valuable \u2013 it\u2019s easy to think of them as \u201cthe answer\u201d, but they\u2019re not.\u00a0 Wait statistics do not tell you the entire story about a SQL Server implementation.\u00a0 There is no one \u201cthing\u201d that tells you the entire story, which is why troubleshooting can be incredibly frustrating, yet wonderfully satisfying when you find the root of a problem.\u00a0 Successfully troubleshooting performance issues in SQL Server requires an understanding of all the data available to aid in your discovery and investigation, understanding where to start, and what information to capture to correlate with other findings.<\/p>\n
Our online training (Pluralsight) courses that can help you with this topic:<\/p>\n