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.
- 1)
Query Store Usefulness
- 2)
Troubleshooting Without Query Store Techniques
- 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
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
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.
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
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.
SQL Server Profiler Event Classes Related to Performance
- 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.
The batch or module in the plan guide definition must match the batch or module that is being executed.
- 2.
The query in the plan guide definition must match the query that is being executed.
- 3.
The compiled query honors the hints in the plan guide.
- 1.
- 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.
The batch or module in the plan guide definition must match the batch or module that is being executed.
- 2.
The query in the plan guide definition must match the query that is being executed.
- 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.
- 1.
- 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.
Server-Side Traces
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.
sp_trace_create – creates the trace.
sp_trace_generated_event – creates a user-defined even in SQL Server.
sp_trace_setevent – adds and removes Data Columns to the trace. You must specify the Event Number. A complete list can be found online at https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setevent-transact-sql?view=sql-server-2017 .
sp_trace_set_filter – applies a filter to a trace.
sp_trace_setstatus – stops and starts a trace based on the id of the trace you specify. You can query sys.traces to find your id.
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 Concepts
- 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.
Code to get a list of corresponding Extended Events for SQL Profiler Events
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.
sys.dm_exec_query_plan_stats
Query for retrieving last actual execution plan for running processes
DMVs
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
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.
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.
A SELECT statement for USE PLAN
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.
UPDATE STATISTICS for a table
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.
Find the plan handle for the SQL text you are looking for
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
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
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.