Pivoting Wait Time is one of my favorite troubleshooting techniques. When you click into a Database Instance, you will see a more detailed and powerful analysis of wait time in the Database Response Time resource.
This resource provides multidimensional performance analysis: - By Individual SQL queries
- SQL Queries Aggregated by Wait Types
- SQL Queries Aggregated by Applications
This is useful because sometimes the root cause is an individual query and sometimes it's several queries from an application or a resource constraint affecting all.
In the first pivot (1st image) of wait time, I can see that there is no one SQL that is the cause of most of the wait time because the queries with the most wait time are very small relative to all remaining queries.
In the second pivot (2nd image), I've pivoted by wait type. I can see that Memory and CPU are the most significant wait type. That means real work is happening as opposed to waiting on storage or locks. Now you may not know all the hundreds of wait types you'll see, but DPA does.
When it comes to wait types, click on the  (2nd image) and DPA tells you (3rd image) - What the wait type means
- Who typically resolves this wait type
- Options for reducing waits of this type
In the third pivot (4th image), I've pivoted by application and see that "Ignite" is the application with the most significant waits and ".NET SQLClient Data Provider" is a distant second. Note that ".NET SQLClient Data Provider" is the default name you will see if your .NET developer did not specify an application name in the database connection string.
Now since I want to find something to tune, I pivot back to SQL and exclude "Remaining queries" by un-checking it (5th image). Now I know the 5 queries causing me the most wait time and that is something we can work on. Of course none of these examples show a lot of wait time. I'm only doing this to demonstrate how I would use this resource. What if you wanted to know more about a query? Click on it and you will see a nice report analyzing historical performance of this one query (6th image). So with wait time analysis, AKA response time analysis, you can find needles in the haystack: - If your most significant wait types are related to storage sub-system performance, don't jump to the conclusion that you need faster storage. Optimizing queries for storage may resolve performance issues. Or maybe a single database file needs lower latency, but the rest of the files are OK.
- If a single application is suffering but other applications using the same database instance are not, then maybe your application's database needs special attention, not the whole instance.
- Maybe a single query is causing grief to everyone but it's application is happy and thus off your radar. For example, maybe someone has used Excel to query your database instance and allowing it to dominate database resources for an hour.
And just to reiterate, you don't need to know what all of those wait types mean. When you don't know, click on the  icon and DPA will tell you what it means, what can be done about it and who would typically do that. This is so huge, I had to say it twice. :-)
And because wait time analysis may be new to you, here's some information on the technique.
| 
