How to use SQL Server Query Store to find database performance issues

By Michael Curry, Senior Web Software Developer
Feb 20, 2023

Application performance is important for any company, and it’s no different for us at WebCE. Not only is performance important for reducing the time that our customers wait for web pages to load and API calls to process, but also in reducing overall network and server loads.

As one of our routine maintenance tasks, we evaluate our data access code for any bottlenecks or poor performing code that may have arisen over time. One major aspect of this review is to use SQL Server’s Query Store tool to find queries with disproportionately high execution times.

What is SQL Server Query Store?

Simply put, Query Store is a tool available as part of SQL Server that can be used to track query plans and their performance over time. It can be configured and activated per database, after which it collects and aggregates data about query activity and provides several very configurable reports onto that query data.

Here are some helpful articles from Microsoft on using Query Store

Using Query Store to Identify Issues

Setting up Query Store on our database was quite simple. We kept most of the default settings, except the storage size and duration, which we adjusted to support comfortably keeping a month’s data. For our maintenance process, we primarily use the reports for Top Resource Consuming Queries by Total and Average Execution Times, and Total Executions to spot potential improvements. These will let us find queries that are taking up a large overall amount of time, a large amount of time per query, or are just queried excessively. Of course, not everything that appears in these reports will be a problem, and even those that are excessive might not be worth the effort to address, but it’s still useful to be aware of these queries.

Query Store tracks the query texts that were received by SQL Server, so it might require more analysis to find exactly what is executing a given query. Some data access libraries modify or generate SQL queries in particular ways, so it's good to know your tools and how their runtime behavior might affect your Query Store data.  Two popular libraries for working with data in .NET are Dapper and Entity Framework.

How Dapper modifies SQL Queries

Dapper is "a simple object mapper for .NET", and we've used it to get high-performance translations between SQL Server and .NET.  For the most part, Dapper doesn’t manipulate query text, but it is notable that it translates parameterized “IN” conditions in a specific way. For example, consider this "source" SQL query:

SELECT * FROM Customer WHERE Id IN @Ids;

Dapper will rewrite it into something like this:

SELECT * FROM Customer WHERE Id IN (@Ids_1, @Ids_2, …);

This feature of Dapper means that the "source query" may be translated into multiple distinct query texts if your code calls that query with "Ids" arguments with different numbers of members.

How to identify SQL queries from Entity Framework

Entity Framework, on the other hand, allows you to write data access methods in C# LINQ syntax, and then the library converts those expressions at runtime into their equivalent SQL representation. This means that it won’t always be clear which expression correlates to a given query. Typically, these generated queries can be identified by having tables aliased as something like [Extent1]. Depending on how complex and varied your LINQ-to-Entities code is, it may be easy or difficult to determine which methods are generating the queries. You can also call .ToString() on the IQueryable object in the LINQ-to-Entities code to see the generated query text. One point of note is that (at least as of Entity Framework 6), “IN” clauses are rendered with any parameter values substituted into the query text, which can result in Query Store tracking them as separate queries!

Example Cases from our Performance Maintenance

For the maintenance review we are covering in this article, we found 3 significant outliers that had relatively simple solutions for improving their performance! Future articles will cover these three cases in more detail, but the basic overview of them is:

  • Case 1
    • A simple query that was executed very frequently
    • We decided that we could cache the table’s data to minimize the number of trips to the database we need.
  • Case 2
    • A query with a very high total query time and a high average query time
    • The data access method was pulling a very "wide" result set, but only 2 columns of the returned data were being used in code (to do aggregate calculations).  We wrote a new data access method with a specialized query to aggregate and return only the data that we needed.
  • Case 3
    • A query with a very high total query time but a low average query time.
    • Our code was calling the query within a loop, filtering the returned data, and essentially doing a Loop Join in memory.  We refactored the caller to instead execute a single query that applied the filtering and join logic at the database level.

Overall Results

While these were only a few of our top resource-consuming queries, addressing them as we did saw an overall reduction in query execution time of about 35.8% during peak traffic across the sum of all our queries, as well as about a 12% reduction in average CPU usage on our SQL Servers!

These two graphs are snapshots of query performance across one hour. After these improvements we can see that we’ve improved queries Id: 81 (Case 2) and Id: 376 (Case 3) from our top poorly performing queries.

Before: Top query duration totals

-

After: Top query duration totals

-

Conclusion

Improving performance can be a tricky task, as you can almost always find something to improve at some level, but it can be difficult to decide which issues are worthwhile tackling. Query Store simplifies a lot of this process by helping us identify and evaluate queries that contribute to poor performance. Its tooling is also extremely helpful in showing regressions and performing comparative analyses of your database performance.

Here we’ve shown how we use Query Store as a key part of our maintenance procedures and how it has helped us improve the performance of our systems, contributing to our overall goal of providing a better service and experience for our customers!