How Query Store Drives Progress Along the SQL Server Maturity Curve

We’ve been learning about SQL Server Query Store lately. We’ve delved into a few benefits of the feature, as well as how to enable it. In today’s post, we’ll discover how this feature can play a role in maturing your SQL Server environment.

A Refresher on the SQL Server Maturity Curve

First, let’s remind ourselves of what the progression of the SQL Server maturity curve looks like, as discussed in this blog.

1. Reactive—Firefighting
2. Managed—Gaining control
3. Optimized—Running proactively
4. Strategic—Turning data into advantage

How SQL Server Query Store Helps Businesses Take Control

Query Store’s features can help shift businesses away from break/fix firefighting. Because the Query Store collects runtime metrics for query performance, as well as query plans, businesses aren’t running blind anymore.

You want to know what was going on last Tuesday at 3:15PM when the app was slow? Look at Query Store to see what queries were running and what plans the queries were using. There may have been a change in the query plan, and after that is when people reported that the app was slow. Those query plans may also reveal missing indexes that can help performance.

Additionally, the Query Store report called “Top Resource Consuming Queries” will show how much CPU and RAM were involved in those queries. The Query Wait Statistics report will represent the resources that queries were waiting on as very visible bar graphs. This report enables IT professionals to see that issues like locks or disk IO are affecting their queries.

This type of data gives you the insight needed to start gaining control over your environment.

Moving from Managed to Strategic with Query Store

Now your business can move to the next stage of maturity, which is being proactive. For example, now you can start rewriting queries that were running when the application was slow.  Those missing index recommendations found in various query plans can be evaluated and compared to existing indexes. Perhaps a recommended and existing index can be combined into a single index that can be tested in a lower environment to see if it has a positive impact on the query plan, resulting in lower query durations or lower resource usage.

If the application is from a vendor, then you likely can’t make changes to the queries or indexing, but you now have data to provide the vendor when approaching them with performance problems.

Additionally, you can do things like forcing certain query plans, and in SQL Server 2022, you can apply query hints within Query Store so that even if an application is from a 3^rd party vendor, you can still make improvements without changing the application code. Query Hints are stored within the Query Store and applied after the code is submitted to SQL Server.

Query Store, then, empowers your business to be strategic. The team can see where the pain points are, test and prioritize issues and solutions, and hand over data to vendors when they can’t make changes themselves. This proactive, strategic work can literally save your business a lot of money by lowering resource usage, allowing for a smaller SQL Server instance. This saves money on licensing and, if you are in the cloud, saves money spent on the size of resources provisioned.

Want to Work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.