Query Store without SQL Server 2016

Query Store is a relatively new feature provided by Microsoft which can help a lot in troubleshooting query related issues and also to monitor the performance of queries over a period of time. But the thing it is SQL Server 2016 onward feature and there isn’t anything from MS for prior versions.

We all have clients who use any outdated version of SQL Server in production, irrespective of whether that version is even supported or not. And similar to that I have a client who uses only 2012 and 2014 versions for some application, these applications are legacy one and were developed by a vendor and now neither they have the source code neither support from vendor to have the applications updated to support newer versions of SQL Server.

And when supporting such systems it becomes difficult to provide support when for some instances you can leverage Query Store and for some you are still relying on just DMVs.

To the rescue comes the community created open source project “Open Query Store“. You wont be able to force the plans but still able to see and monitor the query plan changes.

The installation and working is very simple, it creates some tables and stored procedures. The data collection depend upon the choice you have made during installation it come with option of either storing the data on the same local database or on a central database. It also create a SQL Server Agent job to collect the data and push the data into the tables.

It also comes with PowerBi dashboard and rdl reports that can be used from SSMS.

Open Query Store Dashboard

Using this you’ll be able to know about the kind of load and queries are running, visual queues are always good.

I hope it helps you too!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s