Am I correct in assuming that DPA only samples queries at regular intervals, it does not actually track every individual execution? It seems like a pretty basic no-brainer assumption, but it leads me to two observations:
Scenario 1: We recently had an issue on one of our servers with pretty severe blocking. Using DPA’s “blockers” tab, we found the SPID responsible for the blocking. That SPID matched what we saw blocking on the system. Clicking on the “Find Last Activity” link in DPA took us to a query that DPA suggested caused the blocking, but it was different from the query SQL Server told us last executed on the SPID. We were able to mine the transaction log and found that the SPID actually executed several small queries between the one reported by DPA and the one reported by SQL Server, one of which was responsible for the blocking we saw. I’m guessing that DPA’s sampling of system state missed these other queries because they were small, fast, and not executing when DPA probed the system.
Scenario 2: Periodically we want to profile our system and look for queries that are responsible for a significant portion of our load so they can be tuned. This is similar to what DPA does with its “Advisors” feature, but since many of our queries are coming through an ORM, they look slightly different and have different hashes even though they are essentially the same. So we have to hit the DPA tables directly and aggregate across multiple hashes to find the real pattern. Now I’m realizing that this method will miss some queries and our analysis using this method is incomplete since DPA doesn’t record every query. We’ll need to use xEvents or Profiler for this work.
Please don’t get me wrong, I’m not dissing DPA. It is still my go-to system for monitoring, alerting and keeping tabs on how are servers are doing. I love it, and that isn’t going to change – it’s saved my bacon way too many times (and we all like bacon). Almost every database performance issue will stem from a query that runs long enough and consumes enough resources that it will show up in DPA, so over the long run DPA will capture a pretty good view of system performance. But if my guess is correct, in a short-term window DPA doesn’t have a perfect view of what happened on a system because it doesn’t capture *everything*, and drilling into that window looking for details is inappropriate.
