One of my clients who is running one of the most popular web hosting services online reached out to me to help them with the Comprehensive Database Performance Health Check. Today we will discuss how I was able to help them with a query script of Query Store Status for All the Databases.
Slow Intermediate Performance
My client has over 1000s of small databases on a very powerful server. They have been running fine for over a year but recently they started to face sudden slowdown even during when their server is not busy. After doing health check we figured out the issue was with one particular database which was just too busy running dynamic query is creating problems for us. We started with that database and eventually found nearly 100s of similar database which recently started to show the symptoms of slow performance.
What Changed? – Query Store Status
As the performance problems were recent, I asked what has been changed in recent times on their server and I learned that for a few of the databases they have recently turned on Query Store. Here is the query which I ran to find out Query Store Status for All the Databases.
SELECT name, database_id, is_query_store_on FROM sys.databases
When we looked at the result it was clear that all the 100+ databases where they started to recently face performance troubles had query stored enabled on it. We disabled query store for all the databases by running following query one at a time for the databases.
USE [master] GO ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = OFF GO
After we turned off the query store, the server performance was once again restored. We did a few more tests on the server and determined that if we want to enable a query store for over 100+ databases we needed more powerful servers and also have to tune our few things during Comprehensive Database Performance Health Check.
After Server Upgrade
Yesterday my client performed the server upgrade to better hardware. As we had more resources available for our system, we decided to enable Query Store for the few of our selected database. However, this time instead of enabling it for 100s of the databases, we decided that we will enable one at a time for our most important databases.
Note: Please do not get the impression that the Query Store is bad or leads to poor performance for the system. Just like any other feature when it is enabled it also takes a part of the resource from your system and if your system is not capable to handle an additional load this feature can negatively impact the performance. Always do the capacity analysis of your system before enabling any feature on the server.
Reference:Â Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
