My T-SQL contribution for this month is about turning Query Store on everywhere for one department.
This month’s T-SQL Tuesday is hosted by Tracy Boggiano. Tracy invites us all to write about adopting Query Store.
You can read more about the invite in detail by clicking on the T-SQL Tuesday logo in this post.
Query Store recap
Just to recap, Query Store is a feature that was first introduced in Azure SQL Database. Afterwards, it was introduced for use with SQL Server 2016.
It can help you identify queries which experiencing problems because their execution plans have changed. Which is also known as query regression or plan regression.
In reality, there are a couple of ways you can view the results for on-premises installations. You can either view them visually within SQL Server Management Studio, or by running various T-SQL queries instead.
In fact, Query Store has become so popular that you can now use it with other Azure offerings. For instance, Azure SQL Datawarehouse and Azure Database for MySQL.
Occasion
For me, there’s one occasion that certainly springs to mind. Some time ago I was performance tuning some SQL Server instances for a client.
Now these instances had different workloads and import rates. So now and again query performance would change. Luckily, some of these were SQL Server 2016 instances.
With this in mind I started turning on Query Store for a couple of databases to resolve issues. Which helped identify a few issues.
However, I started doing this for other instances and databases as well. So, to save myself some time I investigated the workloads and made a big decision.
I decided to turn Query Store on for all databases on these SQL Server 2016 instances, using the sp_msforeachdb stored procedure.
In addition, I made sure others had access to the latest versions of Management Studio. So that they could view the Query Store reports in a GUI.
Afterwards, I showed others how they could use Query Store to investigate query regression.
Important point
Which brings me to a very important point I want everybody to remember.
If you are going to introduce new features like Query Store, you must educate others how to use them. Especially if you are expecting them to use it long term.
In fact, this is an important point for anything new you put in place. Because you have to make sure others are involved as well. Otherwise, the chances of them adopting it are a lot less.
Best practices
Of course, if you are going to turn on Query Store everywhere check your databases can cope first. In addition, monitor them after turning on Query Store to avoid any potential issues.
With this in mind, I want to share the link to online document about best practices with Query Store. Which you can read in detail here.
Query Store alternative
Now, I have noticed that Tracy invites those who are not yet using SQL Server 2016 this month as well.
In reality, there are probably a lot of older versions of SQL Server in use. With this in mind I thought I would share an alternative solution you can use instead called OpenQueryStore.
Which you can read about OpenQueryStore in detail at the GitHub page here.
In addition, you can find out about other free tools for SQL Server in an old post of mine here.
Final word
I hope me you enjoyed this post about turning Query Store on everywhere. In addition, I hope it has given some of you some good ideas.
Of course, I know others will have their own views about this post. With this in mind, feel free to add a comment below.
[…] Kevin Chant (b | t) – Kevin tells us how he turned on Query Store after using in a few places and seeing its benefits. Then he educated his team on how to use the reports. He also warns to check out the best practices. Then he points out that isn’t not available below 2016 so you can look at Open Query Store on GitHub (after all Query Store is forcing plan guides in the background, hint hint for what you can do on the systems not running Query Store yet). […]