I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events.
Now, that’s pretty unlikely and I’m sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it’s worth sharing here too.
Achieving Synergy
Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to achieve that synergy. We use the DMVs and Extended Events (despite my constant pushing, we’re still not leveraging Query Store). Personally, I think we’re doing a fantastic job and make a terrific tool. Yet, I wouldn’t say we have full synergy. The fact is, there’s too much. There’s too much detail.
To a large degree, you have to think about the tools as separate tools. They all kind of do the same thing, yes. However, they’re all very focused in different ways.
The DMVs are quite good for an immediate overview. What’s happening right now, or, what’s happened relatively recently. However, the recent past represented by the DMVs is limited to what’s in cache and it’s an aggregate. If you need info on stuff that was never in cache or has been flushed out, you need to use a different tool.
Query Store is great for an historical view of query behavior. It’s fantastic because it shows you a history of execution plans, as well as query performance metrics. Best of all, while the data is aggregated, the aggregations are broken up into time windows which gives us that all important “before and after” measurement capability. Before the recompile and after. Before the edit to the procedure and after. Before the new index and after. You get the idea. Query Store also introduces a vital missing bit of info from the DMVs, the standard deviation of it’s aggregations. It’s a great tool. However, it’s an aggregate again. What do you do when you need the detail. Not simply, for example, what parameter a given procedure was compiled on, but specifically what parameter was used when it ran really long at 2AM? That’s not there in Query Store or the DMVs.
Enter Extended Events. Here, we get all the detail. We can capture every single call. Every single statement. Recompiles, stats updates, cache misses, cache hits, more and more. Further through Causality Tracking, we can combine this information easily in ways we’ve never been able to before. Love me some Extended Events. However, consuming this data is a giant PITA. I teach a bunch of ways to deal with it. It’s still not easy. Plus, once you’re getting all the detail, you’re getting all the detail. It’s on you now to aggregate that info again, because, as shown above in the other tools, aggregation is useful.
Each of these tools serves a different purpose. Think of them like a hammer, a screwdriver and a wrench. All three are for attaching something. Nails, screws & bolts. Each has a specific purpose. To a degree, and poorly, you can even try using each tool to do the others job. However, really, your best bet is to focus on the purpose and use the tool to do the thing you need rather than try to make any one tool do all of them.
Your choices.
Personally, I’d recommend getting a 3rd party monitoring tool (I can recommend a very good one if you like). It gets you some of the synergy you’re looking for. Then, as needed, you can plunge into the tools to do what they uniquely do. That’s my 2 cents on it.
Conclusion
This is absolutely an opinion piece, right. As we all know, opinions are like your bottom. Everyone has one and they all stink. Yeah, there’s a cruder word used normally, but I think the idea carries without vulgarity.
I get it. Your opinion may differ. It’s entirely possible for you to get everything you ever need for performance monitoring from only one of these tools. Everyone’s experience is different. Everyone’s needs are different. Your opinion on these tools will vary. That’s OK.
I also get it. You’ll never, ever, ever, use Extended Events (good luck in Azure SQL Database btw). My honest opinion is, you’re wrong. However, that’s just an opinion. That’s OK too.
The facts are simple. These tools do different things. The synergy is going to be best achieved by recognizing that fact and then using the tools for the specific job you need them to do and, in fact, not try too hard to combine them. Rather, use one to determine when and where you need to use the other.
Although an opinion piece, I hope this was in some way helpful.