3

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?

5

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.

2
4

Maybe this can help you:

https://github.com/OpenQueryStore/OpenQueryStore

OpenQueryStore (OQS) is a collection of scripts that add Query Store like functionality to pre-SQL Server 2016 Instances!

OQS is being built from the ground up to allow all versions and editions of SQL Server from 2005 up to and including 2014 to have a Query Store like functionality. The data collection, retention and cleanup will be easily configurable to allow for complete control of the OQS data storage.

The plan is to offer a fully integrated OQS functionality that requires minimal administration, including persistence of query data across restarts and backups/restores.

A further option will allow for a centralized query store, to collect all query statistics for an entire SQL Server instance.

Finally, a set of integrated reports inside SQL Server Management Studio will allow for analysis of the collected data.

Supported versions: SQL Server 2008 - 2014
SQL Server Management Studio versions 16.x and 17.x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.