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

1. What Is Query Store?

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

Query Store keeps rolled-up aggregates of queries and statistics for workloads that are run against your SQL Server database when it is enabled. This data can be used to help you establish a baseline for performance, troubleshoot performance issues, and stabilize performance on your database. In this chapter, we go over techniques that were used before Query Store was released, including Profiler/server-side traces, Extended Events, DMVs (Dynamic Management Views), plan guides, and sp_whoisactive. Then, we will look at how and why Query Store is a game changer for doing these activities.

Query Store is like a flight recorder box for your SQL Server. Query Store stores statistics such as duration, reads, writes, CPU, etc. along with query plans in memory and then aggregates this information based on the settings that were set when Query Store was enabled on the database, with a default of 1 hour. At specified periods, the default is 15 minutes, this data is persisted to disk into the catalog views for you to query or view via the built-in reports in SQL Server Management Studio (SSMS). With the introduction of SQL Server 2017, we also can utilize the power of auto plan correction and the aggregation of wait statistics – more on this in Chapter 8.

This is where the true power of Query Store comes into play. Before this data was only available if you captured it using various other methods that proved to be much slower and cumbersome to work with. To get a better understanding of what Query Store is doing under the covers, we will explore these other methods:
  1. 1)

    Query Store Usefulness

     
  2. 2)

    Troubleshooting Without Query Store Techniques

     
  3. 3)

    Query Store: The Game Changer

     

Query Store Usefulness

Query Store has more than one way it can be utilized to help with your database. In this section, we will discuss how it can be used to help you establish a baseline for your database, troubleshoot performance issues, and stabilize performance.

Baselining Performance

One key part of any database professional’s job is being able to baseline the performance of their database server which is not an easy task. In this section of the book, we will talk about the why and how of establishing a baseline for your database server and its relation to Query Store. Query Store’s ability to capture all queries that have executed against your database and record runtime statistics in the database makes it easier to collect the baseline information you need.

What Is a Baseline?

Before we talk about how Query Store can provide you with a baseline, let’s discuss what a baseline is. A baseline is established by running a workload against a server and taking metrics in several areas to determine if anything has significantly changed over time. Areas of interest in SQL Server include, but are not limited to, CPU utilization, memory clerk usage, number of reads and writes (physical and logical), query execution times, etc. Baselines should be taken during peak and non-peak times to get an accurate measurement of the overall activity of your server. By having a baseline, you will be able to isolate performance problems better and identify bottlenecks.

How Query Store Provides a Baseline

Query Store aggregates data about queries ran against the database it is enabled on into intervals predefined when you configure it. This data is displayed in a few different reports. One example is the Overall Resource Consumption in Figure 1-1 that shows charts of the last month of duration, execution count, CPU time, and logical reads by default. This report is the best for viewing a baseline of the database.
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig1_HTML.jpg
Figure 1-1

Overall Resource Consumption report

Catalog Views

All the data collected from Query Store are stored in multiple catalog views. The aggregated runtime statistical data is stored in sys.query_store_runtime_stats. The catalog view sys.query_store_query_text stores unique query text values that have been executed against the database, and the sys.query_context_settings view stores the settings for the queries stored in the sys.query_store_query_text view that was executed under settings such as SET ANSI_NULLS ON or SET QUOTED_IDENTIFERS ON. The view sys.query_store_query stores all queries uniquely executed based on text and context settings executed so you can track them. The sys.query_store_plan catalog view stores the estimated plan for the query executed and the compile time statistics in XML format. The sys.query_store_runtime_stats_interval view stores the time intervals that the data is stored in.

More details on catalog views will be discussed in Chapter 5.

Stabilizing Performance

The next report to look at for baselines is the Regressed Queries report, which will show you queries that have regressed in performance. For each of these, you should look at why the performance has degraded. This report will show how each query plan for a specific query has performed over the last month by default.

From the Regressed Query report or Top Consuming Queries report, you can easily see what plans should be forced based on how they are performing. Forcing a plan is when you take an execution plan and based on the performance you see in the report decide that you want that plan to be the one the SQL Server engine uses going forward. Before Query Store, you had to use plan guides and those are discussed later in this chapter. You would force a plan when you notice plan regression. Plan regression is when the SQL Server Query Plan Optimizer compiles a new execution plan for a query that was previously running and the performance is worse than the previous plan. When a plan is forced manually, you should be aware that no other execution plan will be used for that query going forward. Also, if there is an index change or table schema change, the forced plan could start failing causing the SQL Server Query Optimizer to take extra steps to get an execution plan each time the query is executed. The reports provide a button by which you can force the plans, but you can also for change management purposes use T-SQL to force plans with the stored procedure sys.sp_force_plan .

The High Variation report shows you which queries are inconsistent in performance which gives you a way to tell which queries are performing differently at times and may need to be tuned to perform consistently.

SSMS has a Forced Plan report where you track and verify that any plans that were forced are performing as expected.

Tip

Older query plans will be deleted from Query Store based on your settings for retaining query plans, so you may want to keep separate documentation of the performance you expect to see going forward for any forced plans.

Reports will be discussed in greater detail in Chapter 4.

Troubleshooting Without Query Store Techniques

Without Query Store, there are several techniques you can use to troubleshoot problems related to bad query performance. Here we will discuss using SQL Server Profiler to run a trace on the server through the application, running a server-side trace to a file by creating a script using SQL Server Profiler, using Extended Events, pulling information from the DMVs, using a community stored procedure called sp_whoisactive, and using wait statistics.

SQL Server Profiler

SQL Server Profiler is a common tool that collects information about what is running on the server at the moment the application is being executed. Figure 1-2 shows the user interface for it. SQL Server Profiler is used to capture data for short periods of time quickly. It comes with templates to help capture data and gives you the ability to save the data to file or table in a database for later analysis. You must have the ALTER TRACE permission to run SQL Server Profiler.
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig2_HTML.jpg
Figure 1-2

SQL Server Profiler screen

Note

SQL Profiler and server-side traces as of SQL Server 2017 are being deprecated and are in maintenance mode. They will be removed in a future release of SQL Server in favor of using Extended Events and SSMS XEvent Profiler.

SQL Server Profiler is commonly used in the following use cases:

  • Finding slow-running queries and their problem areas.

  • Stepping through troublesome queries to find the root cause.

  • Capturing a series of events that cause a problem to be replayed on a test system to reproduce the problem.

  • Capturing data to compare to performance counters to diagnose problems.

  • Capturing a baseline workload to tune workloads. SQL Server Profiler files can be used with the Database Engine Tuning Advisor to recommend indexes and statistics to tune the captured workload.

Note

The Database Engine Tuning Advisor recommends indexes and statistics that are only ideal for the workload you supplied. Evaluate the recommendations, don’t blindly apply them.

SQL Server Profiler Concepts

To use SQL Server Profiler, it helps to understand the terms in the tool:

  • Event is an action that occurs in the database engine.

  • Event Class is a type of event to be traced. This contains all the data for the event; there are several Data Columns for you to select from.

  • EventCategory defines how the events are grouped in SQL Server Profiler.

  • Data Column contains the data for the Event Class captured. Each Event Class has predefined Data Columns that are available. Not all Data Columns are available to all Event Classes.

  • Template is a predefined trace with an Event Class selected for a particular troubleshooting scenario. A template predefines what events, data columns, and filters to use.

  • Trace is what SQL Server Profiler runs to capture the selected event classes, data columns, and filters.

  • Filter is a way to get a subset of data based on criteria that you specify on a Data Column.

Figure 1-3 shows the screen in SQL Server Profiler user to apply filters.
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig3_HTML.jpg
Figure 1-3

SQL Server Profiler filter screen

SQL Server Profiler Event Classes Related to Performance

The different event classes that you would use to collect data related to query performance are as follows:
  • Under the Performance Event Category:

    • Auto Stats occurs when statistics for index or columns are automatically updated. It will also occur when the optimizer loads statistics to be used.

    • Performance Statistics are used to get the performance statistics of queries, stored procedures, and triggers executing. Six event subclasses construct the lifetime of these actions in the system. Using the subclasses and the following DMVs, you can get the performance history of any query, stored procedure, or trigger: sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats.

    • Showplan All occurs when a SQL Statement is executed. It contains a subset of information in the Showplan XML Statistics Profile or Showplan XML event classes.

    • Showplan All for Query Compile occurs when a SQL Statement is compiled. This event class is used when you want to identify the Showplan operators. This is a subset of information in the Showplan XML for Query Compile event class.

    • Showplan Statistics Profile occurs when a SQL statement is executed. This is a subset of information in the Showplan XML Statistics Profile event class.

    • Showplan Text occurs when a SQL statement is executed. This is a subset of the information available in the Showplan All, Showplan XML Statistics Profile, or Showplan XML event classes.

    • Showplan Text (Unencoded) is the same as the Showplan Text event class, except the data is formatted as text instead of as binary data.

    • Showplan XML occurs when a SQL statement is executed. This event class is used when you want to identify the Showplan operators. The data in this event class is a defined XML document.

    • Showplan XML for Query Compile occurs when a SQL statement is compiled. This event class is used when you want to identify the Showplan operators.

    • Showplan XML Statistics Profile occurs when a SQL statement is compiled. This event class is used when you want to identify the Showplan operators. This event class records complete, compile-time data.

      Tip For all Showplan event classes, limit the number of them in use, because they can cause significant performance overhead. Showplan Text or Showplan Text (Unencoded) are the event classes that will affect performance the least but still should be used sparingly.

    • Plan Guide Successful has three conditions that have to be true for this event to fire:
      1. 1.

        The batch or module in the plan guide definition must match the batch or module that is being executed.

         
      2. 2.

        The query in the plan guide definition must match the query that is being executed.

         
      3. 3.

        The compiled query honors the hints in the plan guide.

         
    • Plan Guide Unsuccessful occurs when a plan guide unsuccessfully produces an execution plan. Three conditions have to be true for this event to fire:
      1. 1.

        The batch or module in the plan guide definition must match the batch or module that is being executed.

         
      2. 2.

        The query in the plan guide definition must match the query that is being executed.

         
      3. 3.

        The compiled query did not honor the hints in the plan guide.

        Note There is more on how to use plan guides later in this chapter.

         
  • Under the Stored Procedures Event Category:

    • SP:Completed occurs when a stored procedure finishes executing.

    • SP:Starting occurs when a stored procedure starts executing.

    • SP:StmtCompleted occurs when a T-SQL statement inside the stored procedure finishes executing.

    • SP:StmtStarting occurs when a T-SQL statement inside the stored procedure starts executing.

  • Under the T-SQL Event Category:

    • SQL:BatchCompleted occurs when a T-SQL batch finishes executing.

    • SQL:BatchStarting occurs when a T-SQL batch starts executing.

    • SQL:StmtCompleted occurs when a T-SQL statement finishes executing.

    • SQL:StmtStarting occurs when a T-SQL statement starts executing.

In Figure 1-4 you can see the trace properties screen from SQL Profiler.
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig4_HTML.jpg
Figure 1-4

SQL Server Profiler Event Class screen

Server-Side Traces

Server-side traces are a way to run a SQL Server Profiler session without running the SQL Server Profiler application. This is preferred over running SQL Profiler. You use system stored procedures to specify all the events, data columns, and filters for the trace to capture. You use SQL Profiler to create a script for a server-side trace by opening SQL Server Profiler and specify events and columns you want to capture. Then go under the File menu and select, then Export ➤ Script Trace Definition ➤ For SQL Server 2005 – 2016. (Figure 1-5 shows an example of selecting that menu item).
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig5_HTML.jpg
Figure 1-5

Script out server-side trace definition from SQL Server Profiler

Server-side traces are considered in maintenance mode, meaning they are planned to be removed in a future version of SQL Server. Thus, you should be trying to switch to Extended Events, which we discuss next.

The list of system stored procedures used to create a server-side trace is as follows:

Disadvantages of SQL Server Profiler or Server-Side Traces over Query Store

A few disadvantages to SQL Profiler or server-side traces over Query Store when getting a baseline are that the data is not aggregated for you with you writing queries yourself or using a third-party tool. Running the SQL Server Profiler application can be a big performance hit in itself to your system under heavy workloads; you should run server-side traces instead if you are not going to use Extended Events (which we will discuss next). Due to the length of time you could be running either one to capture every event, the files or data in the SQL Server Profiler application can get quite large and will take time to process the data to get the desired information. Query Store automatically captures all your queries and aggregates them for you without any extra processes and has a minimal impact in performance on most systems.

Extended Events

Extended Events was introduced in SQL Server 2008 and are a more lightweight way alternative to server-side traces. Extended Events can provide you with more insight into the database engine than SQL Server Profiler and server-side traces. In Figure 1-6, you can see with the newest version of SSMS, you have two built-in Extended Events under the tree labeled XEvent Profiler. One thing to note is once you start a session, they do not stop running when you close SSMS, you must manually stop a session. You can stop can XEvent Profiler session by hitting the red stop button on the toolbar before exiting SSMS.
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig6_HTML.jpg
Figure 1-6

XEvent Profiler in SSMS

Session definitions can be found in your installation path of SSMS, such as C:Program Files (x86)Microsoft SQL Server140ToolsTemplatessqlxevent. In Figure 1-7, you can find a list of the extended event session provided. The T-SQL template “Captures all Transact-SQL statements that are submitted to SQL Server by clients and the time issued. Use to debug client applications.” The Standard template is a “Generic starting point for creating a trace. Captures all stored procedures and Transact-SQL batches that are run. Use to monitor general database server activity.”
../images/473933_1_En_1_Chapter/473933_1_En_1_Fig7_HTML.jpg
Figure 1-7

Template directory for extended events templates included with SSMS

Extended Events Concepts

To use Extended Events, it helps to understand the terms used to define them.
  • Packages contain objects used for getting and processing data for an Extended Events session. Packages can contain the following:

    • Events are points that you want to monitor in the execution path of the SQL Server. They can be asynchronous or synchronous. To get a list of events that correspond to the Event Classes in SQL Server Profiler, you can execute the query shown in Listing 1-1.

USE MASTER;
GO;
SELECT DISTINCT
   tb.trace_event_id,
   te.[name] AS 'Event Class',
   em.package_name AS 'Package',
   em.xe_event_name AS 'XEvent Name',
   tca.[name] AS 'Profiler Category'
FROM (sys.trace_events te
       LEFT OUTER JOIN sys.trace_xe_event_map em
              ON te.trace_event_id =
                   em.trace_event_id)
              LEFT OUTER JOIN sys.trace_event_bindings tb
              ON em.trace_event_id = tb.trace_event_id
       INNER JOIN sys.trace_categories tca
              ON tca.category_id = te.category_id
WHERE tb.trace_event_id IS NOT NULL
              AND tca.[name] in ('Stored Procedures',
                                 'TSQL',
                                 'Performance')
ORDER BY tb.trace_event_id;
Listing 1-1

Code to get a list of corresponding Extended Events for SQL Profiler Events

The same cautions exist as with SQL Server Profiler capturing lots of events, especially with execution plans, can cause performance problems. Below is a list of common events that you may want to capture in an Extended Events session.

  • Sp_statement_completed is captured when a statement in a stored procedure completes execution.

  • Sql_statement_completed is captured when a SQL statement has completed execution.

  • Query_post_compilation_showplan occurs after a SQL statement is compiled and returns the estimated plan in XML format.

  • Targets tell the session where to store the data.

  • Actions tell the package what action to take if a certain event happens, such as capturing a stack dump or grabbing an execution plan.

  • Types are the type of bytes strung together and the length and characteristics of the data to be able to interpret the data. The different types are as follows:

    • event

    • action

    • target

    • pred_source

    • pred_compare

    • type

  • Predicates are like WHERE clauses to help filter out events that are captured.

  • Maps is a table that lets you know what an internal value means.

  • Targets provides a place where the data for a session will be stored.

  • Event counter is used to keep a count of all events that occurred that you specified for your session synchronously.

  • Event file writes all event session data from memory to disk asynchronously.

  • Event pairing determines when paired events do occur in a matched set asynchronously to capture.

  • Event Tracing for Windows (ETW) correlates events to Windows or applications event data synchronously.

  • Histogram is used to keep a count of a specified event and occurs asynchronously.

  • Ring buffer holds data in memory in first-in-first-out (FIFO) method asynchronously.

  • Engine implements Extended Events by enabling the definition of events, processing event data, managing Extended Events services and objects, and keeping a list of sessions and managing access to the list.

  • Sessions set up with events in them to collect the information you need to troubleshoot an issue. This pulls everything together to give you the data you need to troubleshoot your issue by allowing to specify actions, targets, and predicates.

Disadvantages of Extended Events over Query Store

Some disadvantages to Extended Events are similar to that of SQL Server Profiler in that the data is not aggregated unless you are doing a single column. It must be run for the entire period of the workload you are measuring which causes the files to get quite large and takes time for you to process the data.

Disadvantage of SQL Server Profiler and Server-Side Traces over Extended Events

SQL Server Profiler and server-side traces process each event that occurs to narrow down to the specific events you have indicated to capture which can cause a performance hit. Due to the performance hit and the lightweight nature of Extended Events it is recommended you use Extended Events. Remember also SQL Server Profiler and server-side traces are being deprecated.

Lightweight Show Plan Trace Flag

Starting in SQL Server 2014, Trace Flag 7412 was introduced to allow to access the actual execution plan of queries that are currently running via the sys.dm_exec_query_statistics_xml DMV. This data can also be accessed from the Activity Monitor by right-clicking on any running process and selecting Show Live Execution Plan. Improvements were made to make it even more lightweight in SQL Server 2016 SP1. This method is by far more lightweight than capturing the estimated plans with SQL Server Profiler, server-side traces, Extended Events, and Query Store. So, it’s recommended you patch your SQL Server instances and enable this trace flag. It is enabled by default in SQL Server 2019. There is a new query hint as well, query_plan_profile, to enable at the query level in SQL Server 2017 CU11 and SQL Server 2017 CU3. The overhead of enabling this trace flag is estimated to be about a 2% CPU performance hit. Figure 1-8 shows how to show the Live Execution Plan in the Activity Monitor by right-clicking any currently running process.

Note

See this blog post by SQL Server Tiger Team for more information about the performance impacts: https://bit.ly/2DKR7qg Also, you can only view the plan while the query is running.

../images/473933_1_En_1_Chapter/473933_1_En_1_Fig8_HTML.jpg
Figure 1-8

Show Live Execution Plan in Activity Monitor

sys.dm_exec_query_plan_stats

In SQL Server 2019, DMF (Dynamic Management Function) was introduced which allows you to retrieve the last actual execution plan for a query. This is a compliment to the Lightweight Show Plan Trace Flag above but you don’t have to catch the query running to be able to get the execution plan. It requires you to turn on Trace Flag 2451. Then you can run the code in Listing 1-2 to view the execution plan in column last_actual_exec_plan.
SELECT er.session_id,
       er.start_time,
       er.status,
       er.command,
       st.text,
       qp.query_plan AS cached_plan,
       qps.query_plan AS last_actual_exec_plan
FROM sys.dm_exec_requests AS er
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan_stats(er.plan_handle) qps
WHERE session_id > 50
       AND status IN ('running', 'suspended');
GO
Listing 1-2

Query for retrieving last actual execution plan for running processes

DMVs

Dynamic Management Views (DMVs) were introduced in SQL Server 2005 and made troubleshooting issues with SQL Server easier. There are several DMVs related to performance, but only a few that are related to what is running queries against the SQL Server. We will discuss those DMVs below:

  • sys.dm_exec_cached_plans stores the estimated execution plan for each query when it is executed until the cache clears it out.

  • sys.dm_exec_connections contains information about all the connections to the SQL Server. For Azure SQL Database, it returns connections to the SQL Database. It contains relationships with sys.dm_exec_requests and sys.dm_exec_sessions.

  • sys.dm_exec_cursors contains information about cursors that are open in the databases on the SQL Server.

  • sys.dm_exec_query_stats contains aggregated performance data for plans in the plan cache. When the query is purged from the plan cache for any reason, so is the data. Similar to sys.dm_exec_requests, this DMV contains the query_hash and query_plan_hash fields . The query_hash field allows you to find queries with same logic. The query_plan_hash field allows you to find queries with similar execution plans.

  • sys.dm_exec_procedure_stats contains aggregated performance data for cached stored procedures in SQL Server. Similar to the above DMV, when the stored procedure’s plan is purged from the cache for any reason, so is the data.

  • sys.dm_exec_query_memory_grants stores information on all queries that have asked for and are waiting for a memory grant or have been granted a memory grant.

  • sys.dm_exec_query_plan contains the Showplan in XML format for the batch for the plan_handle provided.

  • sys.dm_exec_query_stats contain aggregated performance data for cached query plans in SQL Server. It contains one row per query statement within a plan until a plan is removed from the cache. This DMV contains information on performance including execution_count, worker_time, physical_reads, logical_writes, clr_time, elapsed_time, rows, and dop. This data is provided in totals, lasts, minimums, and maximums. It also contains information on memory grants, threads, columnstore usage, and tempdb spills. The query_hash field can be used to identify queries with same logic and aggregate data together. The query_plan_hash field is used to identify similar query plans and sum up the cost of queries with similar plans.

  • sys.dm_exec_requests shows you each request that is running on the SQL Server. This DMV contains information on performance including cpu_time, reads, writes, logical_reads, row_count, wait_time, and wait_type. The query_hash field can be used to identify queries with similar logic and aggregate data together. The query_plan_hash field is used to identify similar query plans and sum up the cost of queries with similar plans. The statement_context_id field is the foreign key for the sys.query_context_settings DMV.

  • sys.dm_exec_sessions contains a row for each session to the SQL Server. It contains information on what is currently running for the session and the resources the session is using.

  • sys.dm_exec_sql_text contains the text of the SQL batch uniquely identified by the sql_handle field . The sql_handle field can be used to get information from the following DMVs:

    • sys.dm_exec_query_stats

    • sys.dm_exec_requests

    • sys.dm_exec_cursors

    • sys.dm_exec_xml_handles

    • sys.dm_exec_query_memory_grants

    • sys.dm_exec_connections

    The plan_handle field can be used to uniquely identify the query plan for a batch from the plan cache and obtain information from the following DMVs:

    • sys.dm_exec_cached_plans

    • sys.dm_exec_query_stats

    • sys.dm_exec_requests

  • sys.dm_exec_xml_handles contains information about active handles that are open from using sp_xml_preparedocument.

Disadvantages of DMVs

There are some disadvantages to using DMVs over Query Store. One is that the data is not persisted to disk; it is only in memory. So, if you restart SQL Server, the data is lost for troubleshooting purposes. Two, the plan cache is allocated memory and plans will be purged from the cache as needed to make room for new plans, so you lose the ability to see everything that has run on the server since it was started. Lastly, you have to build some system to persist the data to disk yourself and to analyze it to get the most value out of the data over time.

sp_whoisactive

sp_whoisactive is a stored procedure written by Adam Machanic that is very useful in capturing information from several DMVs. The procedure and several blog posts about how to use can be found at http://WhoIsActive.com . This procedure combines the DMVs discussed above all into one stored procedure with parameters for you to able to pull back different information. This is useful for getting the estimated plans for processes that are running on your system at the moment. One useful trick is to capture this data into a table periodically, such as every minute, so that you capture long running queries that you can troubleshoot. Article number 25 in the blog series on the web site describes how to capture the data into a table. In Listing 1-3 you will find code with some recommended parameters for capturing the text of the query and execution plans in XML format, along with other useful information, including wait statistics, which will talk about next:
EXEC dbo.sp_WhoIsActive
      @get_plans = 1,
      @get_full_inner_text = 1,
      @format_output = 0,
      @get_task_info = 2,
      @destination_table = 'DBA.dbo.WhoIsActiveOutput';
Listing 1-3

Recommended parameters for running sp_whoisactive into a table for troubleshooting

There are several other parameters you may find useful to explore all the articles and decide for yourself what is most beneficial for you. There is a @help parameter that prints out documentation of all the parameters.

Wait Statistics

Wait statistics provide another way to troubleshoot what is happening on your SQL Server. Wait statistics were introduced in SQL Server 2005 and offered us a whole new way of troubleshooting. Wait statistics essentially tell you what the database engine is waiting on when it is trying to do work. Wait statistics are in two categories: signal waits and resource waits. When SQL Server is waiting on a thread to become available, it considered a signal wait; this usually indicates that the CPU is running high on the server. Other waits are considered resource waits, such as waiting for the lock of a page. There are hundreds of resource waits. Wait statistics can be queried from the DMV sys.dm_os_wait_stats. With this information, you are to tell what your queries are waiting on to execute. You can’t tell by an individual query, but with the introduction of Query Store, you will see later it will accumulate per query.

Tip

There is a library online that explains all the wait types available from SQLskills at the following URL: www.sqlskills.com/help/waits/ . A query that will show you the percent of each wait statistics that has been used since SQL Server was started is available on Paul Randal’s blog at https://bit.ly/2wsQHQE .

Stabilizing Performance Before Query Store Techniques

There were a few ways to stabilize the performance of query plans before Query Store was introduced in SQL Server 2016. One way was to use plan guides and the USE PLAN hint with the query. A second was to keep your statistics up to date as best as possible to ensure the optimizer had the best data available to create a plan. Another was to UPDATE STATISTICS hoping SQL Server would create a better query plan on next execution. Then you also could recompile a stored procedure or function to get to see if SQL Server would generate a better plan. Lastly, you could remove a plan from the cache so that a new plan would be created the next time the query was executed to see if a better plan was created.

Plan Guides/USE PLAN Hint

Plan guides are used to stabilize the performance of a query when you cannot or do not want to change the query. Plan guides work by influencing the optimizer by using query hints or a fixed query plan. Third-party applications can benefit from the use of plan guides because you cannot change the queries. To use a plan guide, you specify the T-SQL you want to optimize and provide query hints for the specific query to use, and SQL Server will match the text of the T-SQL and use the hints when executing that statement. Specifying the plan guide is done using the OPTION clause on the query.

There are different types of plan guides:

  • OBJECT plan guides

    This type of plan guide matches to object types, such as stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

  • SQL plan guides

    This type of plan guide matches queries based on the T-SQL you provide. They must be parameterized in the correct format. They have to match exactly down the spacing. They apply to stand-alone T-SQL and batches.

  • TEMPLATE plan guides

    This type of plan guide matches stand-alone queries parameterized to a certain form. They are used to override a database PARAMETERIZATION option. They can create in the following situations:

    • When the database SET option for PARAMETERIZATION is set to FORCED, but you want queries to compile using the rules for Simple Parameterization.

    • And when you want the opposite effect, the database SET option for PARAMETERIZATION is SIMPLE and you are to use Forced Parameterization.

Plan guide matching occurs at the database level. For SQL or TEMPLATE-based plan guides, SQL Server matches the parameters @module_or_batch and @params to a query character by character.

When you create a plan guide, it will remove the current plan from the plan cache. When you create an OBJECT or SQL plan guide for a batch, SQL Server removes the query plan that has the same hash value. When you create a TEMPLATE plan guide, SQL Server removes all the single-statement batches in the plan cache for that database.

After you create a plan guide, you use the OPTION clause to specify the USE PLAN parameter to specify a query hint.

Listing 1-4 is the original query:
SELECT count(*) AS Total
FROM Sales.SalesOrderHeader h
      INNER JOIN Sales.SalesOrderDetail d
            ON h.SalesOrderID = d.SalesOrderID
GO
Listing 1-4

A SELECT statement for USE PLAN

Listing 1-5 is the same query with the USE PLAN query hint specified:
SELECT count(*) AS Total
FROM Sales.SalesOrderHeader h
      INNER JOIN Sales.SalesOrderDetail d
            ON h.SalesOrderID = d.SalesOrderID
OPTION (USE PLAN N'
<ShowPlanXML xmlns:=
"http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5"
Build="9.00.1187.07">
  <BatchSequence>
    <Batch>
      <Statements>
       ...
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
')
GO
Listing 1-5

A USE PLAN Example

Updating Statistics

Using UPDATE STATISTICS to update statistics for a table or index can help improve performance if you know a lot of data has changed in the table or index. Beware that updating statistics does recompile all the plans that the table or index are referenced in, so you will not want to do this too frequently unless necessary. Prior to SQL Server 2014, 20% of a table had to be updated before the auto updated statistic would be triggered unless you used Trace Flag 2371 which was introduced in SQL Server 2008 SP1 to lower this; 20% is not ideal for large tables such as data warehouse tables. In SQL Server 2016, this trace flag is on by default.

To update statistics for a table, use Listing 1-6.
USE DATABASE;
GO
UPDATE STATISTICS SchemaName.TableName;
GO
Listing 1-6

UPDATE STATISTICS for a table

To update statistics for a particular index, use Listing 1-7.
USE DATABASE;
GO
UPDATE STATISTICS SchemaName.TableName IndexName;
GO
Listing 1-7

UPDATE STATISTICS for an index

Recompiling Stored Procedures

Whenever you can identify a stored procedure, trigger, or function that is performing poorly due to parameter sniffing or other issues, you can use sp_recompile to recompile the object on the next execution of the procedure. Parameter sniffing occurs when an execution plan is generated that is optimal for one workload, but not optimal for all workloads based on the parameters used for the query. Running this procedure will effectively remove the current plan from the plan cache so a new plan can be compiled on the next execution of the query. The new plan may be the same as the old plan though depending on the parameters passed in.

Removing Plans from the Plan Cache

You can query sys.dm_exec_cached_plans and sys.dm_exec_sql_text to retrieve a plan handle to remove a specific plan from the cache that is not part of a stored procedure, function, or trigger by using the following code.

First, in Listing 1-8 you select the text you need to find that is part of your query:
SELECT cp.plan_handle, st.
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE  LIKE N'%/* MyTable %';
Listing 1-8

Find the plan handle for the SQL text you are looking for

Then copy and paste the plan handle into the FREEPROCCACHE procedure in the code provided in Listing 1-9:
DBCC FREEPROCCACHE (<plan_handle>);
Listing 1-9

DBCC FREEPROCCACHE to remove SQL text plan from the plan cache

Query Store: The Game Changer

Query Store changes how you troubleshoot bad query plans because now everything is collected, stored on disk, and persisted over time to provide you trends in performance. It provides all the estimated plans that have been used in the retention period you have defined which allows you to troubleshoot the query with a graphical interface easily and efficiently. Query Store changes how you can troubleshoot performance, baseline your system, and stabilize the performance of your SQL Server significantly.

What Information Is Gathered

Query Store gathers runtime statistics for each query that ran and their estimated plans. The following data is collected per query and aggregated per time interval you have specified in SQL Server 2019:

  • Execution count

  • Duration

  • CPU time

  • Logical reads

  • Logical writes

  • Physical reads

  • CLR time

  • DOP

  • Memory consumption

  • Row count

Each statistic is available in totals, averages, maximums, minimums, and standard deviations. More information about what data will be discussed in Chapters 4 and 5.

What Information Query Store Provides for Us:Use Cases

There are different use cases for Query Store besides just collecting performance information. They include

  • Finding and fixing regressed queries

  • Finding and identifying top resource consuming queries

  • A/B testing

  • Stabilizing performance when upgrading SQL Server

  • Finding and identifying ad hoc workloads so you can improve them

These will be discussed in more detail in Chapter 6.

Automatic Plan Correction

SQL Server 2017 introduced Automatic Plan Correction that will automatically force plans and unforce plans based on plan regression. So, you no longer have to force plans, although you still can if necessary manually. Chapter 8 will talk more about how this feature works.

Wait Statistics

SQL Server 2017 introduced wait statistics that are being collected per query in 23 different categories, such as buffer and CPU. A detailed list is provided in Chapter 8 of which wait statistics are in each category. Chapter 8 will talk more about this feature.

Conclusion

Query Store offers an automated and easy way to collect performance information about queries that are running on your SQL Server. There are several use cases for using Query Store besides just seeing performance information. It provides Automatic Plan Correction and wait statistics collection starting in SQL Server 2017, which are invaluable new tools. The rest of the book will go into much more detail about Query Store so you can benefit the most from using it.

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

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