DPA Wait Time Explained.

cdpete

So im new to using DPA, i am inheriting it as i am a new admin withing the company. I feel as i have a good general understanding of how is works, except the wait times, for some reason that doesn't make sense to me. So my question is, how are they calculated, what are they looking it, how can i have over 28 hours of wait time on a given day?, are these time calculated per query, per person, etc? any insight to better understand wait times would be greatly appreciated!

Thanks in advance.

mandevil

First of all, glad you asked! Understanding what you are seeing is key to successfully using DPA. So, let's jump in.

The reason you can have more wait time than hours in a day is due to concurrency. Let's say you have 10 sessions that accumulate 10 minutes of wait time each all in the same hour, you would have 100 minutes of wait in a 60 minute timeframe.

Now to our methodology: We sample each instance once per second. We grab a very limited set of data for only ACTIVE sessions - you can think of that limited data set as the dimension tabs at the top of the trend page when you click into an instance (SQLs, Waits, Programs, etc.). Each dimension then gets 1 second of wait time added for that poll as appropriate.

The trend page will display the top N of whichever dimension tab you are in for that timeframe. So the top 15 SQL statements (ranked by observed waits) on your 30 day trend may very well be different than if you are drilled into 1 hour.

As you drill into a specific timeframe, you can set your context (meaning be in this hour, filtered to program X, coming from this client machine - how click on SQL tab to see what SQL is originating from that context or what waits are accumulating or ...). You can think of the waits as a proxy for end user experience. This helps determine what activity your database engine is doing and how long it's taking to do it. When we talk about performance analysis, there should be a time component to it.

HTH! Reach out for anything else - this may indeed prompt more questions. 8 )