patternsqlMinor
Capturing query performance over time
Viewed 0 times
capturingquerytimeperformanceover
Problem
I am trying to trend query performance over time. I made an assumption that query store will help me to do this, but the version of SQL Server I am working with is 2014, so no query store is available.
Does anyone have any tips for how I can capture this information and store it in a table for trending over time, etc?
Does anyone have any tips for how I can capture this information and store it in a table for trending over time, etc?
Solution
You can take advantage of the other tools built into SQL Server for gathering query metrics. The first is Dynamic Management Views (DMV). These can be queried to retrieve aggregate data about the queries currently in cache on the system. Query once every 15 minutes or so and you can build up behavior over time. It is only aggregations though. Also, it's only the stuff in cache, so either a volatile cache or queries that don't go into cache won't show up. These are everywhere from SQL Server 2005 and up.
An alternative is to use Extended Events to capture query metrics. This has a plus and negative all in one. Extended Events can capture every single query that goes by on the system, allowing you for very precise measurements. Output is to XML, but it's simple enough to query that and import it into a table. However, Extended Events captures every query that goes by, so you're going to have a lot of data to deal with. While Extended Events were added in 2008, I don't recommend using them until you're on 2012 or greater.
Or, get a third party tool. Whether something open sourced like OpenQueryStore, cool tool, or a paid for 3rd party tool, this can make the whole process a lot easier.
An alternative is to use Extended Events to capture query metrics. This has a plus and negative all in one. Extended Events can capture every single query that goes by on the system, allowing you for very precise measurements. Output is to XML, but it's simple enough to query that and import it into a table. However, Extended Events captures every query that goes by, so you're going to have a lot of data to deal with. While Extended Events were added in 2008, I don't recommend using them until you're on 2012 or greater.
Or, get a third party tool. Whether something open sourced like OpenQueryStore, cool tool, or a paid for 3rd party tool, this can make the whole process a lot easier.
Context
StackExchange Database Administrators Q#294413, answer score: 5
Revisions (0)
No revisions yet.