© Tracy Boggiano and Grant Fritchey 2019
T. Boggiano, G. FritcheyQuery Store for SQL Server 2019https://doi.org/10.1007/978-1-4842-5004-4_6

6. Query Store Use Cases

Tracy Boggiano1  and Grant Fritchey2
(1)
Cary, NC, USA
(2)
Grafton, MA, USA
 

Query Store has different use cases that it is useful for. This chapter will go into detail on those use cases. We will discuss how to determine what is a normal workload on your database using Query Store by looking at the reports or catalog views and viewing changes against that baseline. Baselining a database is an essential skill for any DBA, and Query Store is a tool you can use to provide you with a baseline for your database. Sometimes you will need to identify what happened during a previous window of time. As a DBA you also will have to troubleshoot poorly performing queries or queries that have regressed in performance, and Query Store gives you the data to be able to do that as well. Query Store can help you identify the top consuming queries so you can see how to improve performance on the SQL Server database. Query Store can be used to capture queries after an upgrade of SQL Server to a different version and change the compatibility mode on each database later and fix any regressed queries, stabilizing upgrades of your SQL Server databases. Finally, we will look at how Query Store is used to track ad-hoc workloads and improve their performance.

Determining What Is a Normal Workload

With Query Store turned on, you are collecting data in the INTERVAL_LENGTH_MINUTES specified when you configured Query Store as discussed in Chapter 3. To refresh your memory on this configuration option, the data can be aggregated in 1, 5, 10, 15, 60, and 1440 minute intervals. With these data points in mind, you can let your normal workload run against your server and view the reports discussed in Chapter 4 starting with the Overall Resource Consumption Report as seen in Figure 6-1.
../images/473933_1_En_6_Chapter/473933_1_En_6_Fig1_HTML.jpg
Figure 6-1

Overall Resource Consumption report

From viewing this report, you can determine what a normal workload is for your database. From the report in Figure 6-1, you will notice a pattern of 2 days a week where activity is low. Those happen to correspond to weekends which can tell us that this database is used in a company where less activity occurs on the weekends and most activity occurs on weekdays. We can also notice a pattern, where the duration is higher than normal, and logical reads are higher than in other periods. For those you want to click on those bars and drill down to the Top Resource Consuming Queries Report, we discussed in Chapter 4 to diagnose which queries caused those unusual patterns and determine if those workloads are something you need to be concerned about.

Another way to get to data is to query the catalog views discussed in Chapter 5. The query in Listing 6-1 queries data from the catalog views for the last 10 days and pulls the top 10 queries by duration.
SELECT TOP 10 qt.query_sql_text,
      q.query_id,
      so.name,
      so.type,
      SUM(rs.count_executions * rs.avg_duration)
            AS 'Total Duration'
FROM sys.query_store_query_text qt
      INNER JOIN sys.query_store_query q
            ON qt.query_text_id = q.query_text_id
      INNER JOIN sys.query_store_plan p
            ON q.query_id = p.query_id
      INNER JOIN sys.query_store_runtime_stats rs
            ON p.plan_id = rs.plan_id
      INNER JOIN sys.query_store_runtime_stats_interval rsi
            ON rsi.runtime_stats_interval_id =
                  rs.runtime_stats_interval_id
      INNER JOIN sysobjects so on so.id = q.object_id
WHERE rsi.start_time >= DATEADD(DAY, -10, GETUTCDATE())
GROUP BY qt.query_sql_text,
      q.query_id,
      so.name,
      so.type
ORDER BY SUM(rs.count_executions * rs.avg_duration_time) DESC
Listing 6-1

T-SQL to top queries by duration in last 10 days

Note

All times are stored in UTC time so you would need to adjust the query to fit your time zone for data to exactly match your time zone.

Establishing a Baseline for Queries

Query Store gives you the ability to establish a baseline for the queries in your database. Query Store stores all the execution runtime statistics in the catalog view sys.query_store_runtime_stats so you can query this catalog view to see a number of statistics about how your queries have performed in the past and are currently performing as you work to establish a baseline to compare against. It is not how you would baseline your server.

What Is a Baseline?

First, let’s discuss what a baseline is. A baseline is a workload you run against your server that sets a starting point for comparisons against future runs of the same workload. In theory, you would run your workload to make any necessary changes to your environment; the changes could be code or hardware changes, and you would rerun the same workload and compare and see what metrics have changed.

How to Create a Baseline with Query Store

Establishing a baseline in Query Store involves knowing what data is being collected and when. When you look at the Overall Resource Consumption Report seen in Figure 6-1, it can be helpful to clear Query Store before starting your workload to establish your baseline. Two ways to do this were discussed in Chapter 3. One is to right-click on the database, go to Properties, then the Query Store tab. As seen in Figure 6-2, there is Purge Query Data that will clear all the data from Query Store so you can start your baseline. Be sure before you run your workload that your statistics collection interval is low enough to collect your data and you don’t have to wait too long between each interval to run this next workload.
../images/473933_1_En_6_Chapter/473933_1_En_6_Fig2_HTML.jpg
Figure 6-2

Database properties for Query Store

The second is to run the stored procedure in Listing 6-2.
USE [<Database>]
GO
EXEC sys.sp_query_store_flush_db;
Listing 6-2

Store procedure to clear all the data out of Query Store

Now we have a clean slate to capture data for our workload. To capture your workload so that you can replay it later, open SQL Server Profiler and pick the TSQL_Replay template and create a server-side trace as we discussed in Chapter 1. Now run your workload, and let Query Store capture all the data for the queries in the workload, and once the workload is done, stop the trace. Note the time period you ran your baseline workload for future comparisons. Now make your changes to the code or server that you want to compare. See the next section on how to compare the results.

How to Compare a Workload Back to the Baseline

The best way to compare your workload back is to use the reports from Chapter 4. If you have set your statistics collection interval to 15 minutes, for example, you can run your workload outside the next interval to collect data for your comparison. The basic steps to make sure you can compare baselines are as follows:
  1. 1.

    Run your workload to establish your baseline.

     
  2. 2.

    Apply your changes.

     
  3. 3.

    Run your workload again.

     
  4. 4.

    Compare the results by looking at the Overall Resource Consumption Report and drilling down to the Top Consuming Resources Report to check on the particular queries that may have been impacted by your changes.

     
  5. 5.

    Then you can decide rather to keep your changes or roll them back.

     

See What Happened Last Night

A common problem is to have someone come over to you and say such and such ran slow yesterday at 5 AM and without any tracing turned you would have no idea what had run. Now with Query Store, all the queries that ran are being recorded for you to be able to see what ran yesterday at any time. By looking at the Top Consuming Resources Report, you can drill down to a specific time period and look for what was running slow. Not only can you tell what was running slow you could see what query caused a CPU spike or a large number of reads if someone had complained that the SAN had slowed down. Figure 6-3 shows your configuration options for narrowing down what happened on the server during the time frame you need and by the metric you heard was the problem.
../images/473933_1_En_6_Chapter/473933_1_En_6_Fig3_HTML.jpg
Figure 6-3

Top Consuming Resources report configuration screen

Troubleshooting Regressed Queries

One of the most powerful use cases for Query Store is to be able to identify queries that have regressed in performance. Queries can regress in performance for various reasons such as a change in statistics, the data cardinality has changed, indexes have been created, indexes that existed before could have been altered or deleted, etc. In most cases, the Query Optimizer in SQL Server does pick a better plan, but there are times where it does not, and Query Store gives a quick, easy way to go in and find those queries.

What Is a Regressed Query?

A regressed query is a query in which the performance has changed due to a change on the system such as a change in statistics, the data cardinality has changed, indexes that have been created, etc. The change results in a different plan being generated than before that does not perform as well. Back in Chapter 4, we explored the Regressed Queries Report which gives you a quick way to identify regressed queries.

Viewing Plan Changes

In the Regressed Queries Report, you can identify a query with two plans and use the compare plan button at the top right-hand corner after you select both plans in the top right-hand pane. The differences will highlight in red as seen in Figure 6-4. And in Figure 6-5, you can see what difference there is between any operator you hightlight in the plans .
../images/473933_1_En_6_Chapter/473933_1_En_6_Fig4_HTML.jpg
Figure 6-4

Regressed Query peport comparing plan operator differences

The differences seen in Figure 4-24 have a not equal sign highlighted in yellow.
../images/473933_1_En_6_Chapter/473933_1_En_6_Fig5_HTML.jpg
Figure 6-5

Regressed Query report comparing report details

As we have seen in Chapter 4, when viewing a query with multiple plans, you can force the plan manually from the report screen. If you find a plan that needs to be forced, you can force the plan from the report and monitor performance.

Identify Top Consuming Resource Queries

In Chapter 4, we discussed the Top Consuming Resource Report where you visually see what queries are taking up the most resources on the SQL Server database. We can view the following metrics when troubleshooting what the top consuming resource queries on the SQL Server database are:

  • Execution count

  • Duration (ms) (default)

  • CPU Time (ms)

  • Logical reads (KB)

  • Logical writes (KB)

  • Physical reads (KB)

  • CLR time (ms)

  • DOP

  • Memory consumption (KB)

  • Row count

  • Log memory used (KB)

  • Tempdb memory usage (KB)

  • Wait time (ms)

They can be aggregated into totals, averages, minimums, maximums, and standard deviation. When looking for top consumers, total is a good starting place. If you are seeing high CPU, start with looking at the total with CPU time. The report will display by default the top 25 queries with their plans. You can then see if there is anything that could be tuned in those queries or perhaps a plan that can be forced.

Stabilizing SQL Server Upgrades

Another great benefit of Query Store is to help stabilize SQL Server upgrades. Every SQL Server upgrade due to changes to the query optimizer has the potential to cause regression in queries if you change the compatibility mode of a database. Since it is recommended best practice to change the compatibility mode of a database when you upgrade SQL Server so you can take advantage of the query optimizer enhancements and new T-SQL functions, it becomes even more critical to be able to stabilize the upgrade of SQL Server. Also, SQL Server 2014 introduced significant changes to the cardinality estimator causing some regressed queries when changing the compatibility mode.

Changing Compatibility Mode Effects

Before SQL Server 2014, changing compatibility modes didn’t affect the query optimizer because compatibility mode did not affect the cardinality estimator because there had not been a new one released since SQL Server 7.0. So the only benefits you got form changing compatibility modes were any enhancements made to the query optimizer and new T-SQL functions.

Cardinality Estimation Changes Explained

Starting with the release of SQL Server 2014, Microsoft started making changes to the cardinality estimator (CE) which affects how queries estimate how many rows are being returned by the query. In most cases, the queries ran faster under the new CE, but some queries had some significant regressions. Your only recourse was to revert the compatibility mode or use trace flags to control the behavior.

In SQL Server 2016, Microsoft introduced database scoped configurations that allowed you to control MAXDOP, LEGACY_CARDINALITY ESTIMATION, PARAMETER_SNIFFING, and QUERY_OPTIMIZER_HOTFIXES at the database level. The two most important ones in this list for what we are talking about are LEGACY_CARDINALITY ESTIMATION and QUERY_OPTIMIZER_HOTFIXES. If you set the LEGACY_CARDINALITY ESTIMATION, it will use the old CE no matter what compatibility level the databases is set to. The advantage to this is you can still get to use the new T-SQL functions in the database and not have the query regressions. The QUERY_OPTIMIZER_HOTFIXES option is the same thing as setting the trace flag 4199 which activates the all the query optimizer fixes put in the engine via a new version or patch or CU. Before 2016, you had to set trace flag 4199, and it applied to all the databases, and you had no easy way to troubleshoot and fix regressed queries.

Process for Testing and Completing Upgrades Using Query Store

Now that you are familiar with how changing the compatibility mode effects the query optimizer and CE and what may cause you regressed queries, let’s talk about how to upgrade SQL Server the most effective way using Query Store to stabilize any regressed queries after the upgrade. This is best illustrated in Figure 6-6.
../images/473933_1_En_6_Chapter/473933_1_En_6_Fig6_HTML.jpg
Figure 6-6

Steps to upgrade SQL Server using Query Store

The first step is to upgrade to SQL Server 2016 or higher. Next, you would enable Query Store on your database(s). Let your workload run on the database(s) as long as you think is necessary to capture a good baseline of your workload. Then set the compatibility mode to the newest mode on the database(s). Finally, monitor the Regressed Queries report discussed in Chapter 4 for plans that you need to force due to regression. Chapter 7 covers Automatic Plan Correction where starting in SQL Server 2017, Query Store will automatically force plans for you. See Chapter 7 for more details.

Finding and Improving Ad-hoc Workloads

Finally, Query Store can be used to find your ad-hoc queries and improve them. If you have a lot of ad-hoc queries, when you look at the Top Consuming Resources report, you will not see a large number of resources being consumed by queries with execution counts greater than one. If your systems are mostly ad-hoc workloads, it will not be ideal for working with Query Store as you are sending in different queries, and it is not able to aggregate data for you or force plans to help performance. While your application is generating ad-hoc queries, SQL Server is spending its time compiling queries for every new query that is executed, bloating the plan cache and consuming resources extra than if they were already in the plan cache. This will also cause bloat of Query Store because it will be storing different plans and aggregated rows of runtime statistics for each query. This will cause the background processes that clean up Query Store to take up more resources as well to keep the space at a level where it can add data.

Instead, of using the Top Consuming Resources report, there is T-SQL you can run to first get the total number of query texts, queries, and plans, and then compare the query_hash and query_plan_hash to find your ad-hoc queries that are similar as seen in Listing 6-3.
--Total Query Texts
SELECT COUNT(*) AS CountQueryTextRows
FROM sys.query_store_query_text;
--Total Queries
SELECT COUNT(*) AS CountQueryRows
FROM sys.query_store_query;
--Total distinct query hashes (different queries)
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows
FROM  sys.query_store_query;
--Total plans
SELECT COUNT(*) AS CountPlanRows
FROM sys.query_store_plan;
--Total unique query_plan_hash (different plans)
SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows
FROM sys.query_store_plan;
Listing 6-3

T-SQL to check for ad-hoc workload

If you see a difference in CountQueryRows and CountDifferentQueryRows or CountPlanRows and CountDifferentPlansRows, that is an indication you have similar queries running, and you would benefit from writing them in a parameterized way such as in stored procedures if you can control the application code, so it can be compiled once and stored in memory and stored efficiently in Query Store. If you cannot manage the application code, you can have two other options. One is to use plan guides; a template for a plan guide is in Listing 6-4.
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
    N'<your query text goes here>',
    @stmt OUTPUT,
    @params OUTPUT;
EXEC sp_create_plan_guide
    N'TemplateGuide1',
    @stmt,
    N'TEMPLATE',
    NULL,
    @params,
    N'OPTION (PARAMETERIZATION FORCED)';
Listing 6-4

Template for implementing a plan guide for PARAMETERIZATION FORCED

Alternately you can turn on PARAMETERIZATION FORCED at the database level using Listing 6-5.
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;
Listing 6-5

Turn on PARATERIZATION FORCED at database level

If in Listing 6-3 the counts remained close to the same, then you will not want to turn on PARAMETERIZATION FORCED. Instead, you will want to enable optimize for ad hoc workloads and set the QUERY_CAPTURE_MODE to AUTO for Query Store instead of ALL. See Listing 6-6 to set these options. This will keep the plans from bloating the plan cache because the first time a query is executed it will only store a stub and store the plan on the second execution for future reuse. The AUTO capture mode will let Query Store not capture these queries that consumed insignificant amount of resources to limit the amount that is stored. 
EXEC sys.sp_configure N'show advanced options', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);
Listing 6-6

Enable optimize for ad-hoc workloads and set capture mode to AUTO for Query Store

Conclusion

In this chapter, we explored several use cases for Query Store. We discussed how to establish a baseline so you can discover how your system normally performs and see what happens when you make changes. Then we discussed who to view what happened last night or any previous point in time for a problem. Next we discussed what regressed queries were and how to troubleshoot them and force plans. Then we discussed identifying top consuming queries so you could look at improving them. Next we looked at how you could use Query Store to stabilize the upgrade of SQL Server and why this is an important feature to use for this. Finally, we discussed who to use Query Store to improve your ad-hoc workloads.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.219.217.55