Query Store: How it works? How to use the new Query Store Catalog Views?

Part 2

/*Fix 2017.03.28: Not Query Store DMVs but Catalog Views. Thanks Kalen Delaney for point it*/

“The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. “msdn.

If you want to know, how to enable it, you can find information here: Query Store and Query Store Settings and Limits

If you want to know about how query store works (Part 1) Query Store How It works? How to use it? Part 1

 

This post is focus in how to use query store Catalog Views.

 

Catalog Views:

Query store Catalog Views can be divided into 2 groups: Plan Store: it is accountable for capture all information that is related to query compilation or Runtime Stats Store: that one is probably the most frequently updated store. These statistics represent query execution data.

 

Let’s talk about Plan Store:

1.Sys.Query_Store_Query_Text – It contains the Transact-SQL text and the SQL handle of the query. (https://msdn.microsoft.com/en-us/library/dn818159.aspx)

Query:

[sql] SELECT * FROM sys.query_store_query_text [/sql]

 

Some important columns from a troubleshooting standpoint ( table 1) :

table_1v1

 

SQL_HANDLE? Yes, that is true, what you are thinking ( I hope, at least). You could join this column with sql_handle from sys.dm_exec_query_stats.

 

For example:

[sql]
Use Adventureworks

Go

SELECT * FROM Person.Address WHERE AddressID in (1,2)

SELECT * FROM Person.Address WHERE AddressID in (2,1)

[/sql]

If SQL Server is using the default mode of parametrization, the simple one. We will find in the cache 2 plans for this single ad hoc query:

[sql]

SELECT
s.sql_handle

, s.statement_start_offset

, s.statement_end_offset

,s.creation_time

, s.last_execution_time

, t.text

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t

[/sql]

We can check the result at figure 1:

figure_0_qds

Figure 1: Query stats ad hoc

 

If we join sys.dm_exec_query_stats with sys.query_store_query_text:

[sql]

SELECT query_hash

, t.query_sql_text

, t.statement_sql_handle

, qs.total_logical_reads

, qs.total_logical_writes

, qs.execution_count

, (qs.total_logical_reads/qs.execution_count ) avg_logical

FROM sys.query_store_query_text t

JOIN sys.dm_exec_query_stats qs

ON t.statement_sql_handle = qs.statement_sql_handle;

[/sql]

 

We can check the result at figure 2:figure1_qds

Figure 2 Query Store and Query Stats execution.

 

Note: Query Hash information, it is also known as fingerprint of the query. The figure 1 example shows 2 execution plans with different handles and the same fingerprint or query hash. “SQL Server database engine has a powerful new feature that generates an identifier for each query. The identifier is independent of any inline parameter values, so it serves as a very effective query identifier. This identifier – sometimes called a “query fingerprint” – enables a fairly robust method of identifying the most expensive queries on your server based on nothing but DMV queries. “ (https://blogs.msdn.microsoft.com/bartd/2008/09/03/query-fingerprints-and-plan-fingerprints-the-best-sql-2008-feature-that-youve-never-heard-of/) . Summarizing, that is a powerful way to discovery if your environment has a lot of ad hocs query with different execution plans, but they are in matter of fact the same query.

 

If we look for this information at the Query Store report TOP Resource Consumption queries, it is possible to notice 2 different plans for the same query.

What is expected since we are using simple parametrization (figure 3 and 4):

figure_3

Figure 3 Histogram

 

figure_4

Figure 4 Histogram Queries

 

Ok. Let’s add more information in this query.

2. Sys.Query_Store_Query – It contains information about the query and its associated overall aggregated runtime execution statistics. ( https://msdn.microsoft.com/en-us/library/dn818156.aspx )

Query:

[sql] SELECT * FROM sys.query_store_query [/sql]

 

Some important columns from a troubleshooting standpoint (table 2):

table_2_v1

 

Query:

[sql]
SELECT qq.query_hash

, qq.initial_compile_start_time

, qq.last_compile_start_time

, qq.last_execution_time

, qq.avg_compile_memory_kb

, qq.last_compile_memory_kb

, qq.max_compile_memory_kb

, t.query_sql_text

, t.statement_sql_handle

, qq.query_parameterization_type

, qs.total_logical_reads

, qs.total_logical_writes

, qs.execution_count

, (qs.total_logical_reads/qs.execution_count ) avg_logical

FROM sys.query_store_query_text t

JOIN sys.query_store_query qq

ON t.query_text_id = qq.query_text_id

JOIN sys.dm_exec_query_stats qs

ON t.statement_sql_handle = qs.statement_sql_handle;

[/sql]

 

The result will be something like the figure 5 and 6:

 

figure_5

Figure 5: Query Partial Result 1

figure_6

Figure 6: Query Partial Result 2

 

So now, I have more information for troubleshooting. I can easily find the last time the T-SQL statement that I am troubleshooting was compiled (or recompiled, note different values for initial compile and last compile). I can also find how much of memory it was consumed from the query compilation perspective.

And again, in terms of an environment with lot of ad hocs, now I have the fingerprint of my query (Query_hash) stored in Query Store. As this information is in the Query Store catalog view, so it survives a crash.

 

Ok, so once more let’s try to add more information in this query.

 

3. Sys.Query_Store_Plan – it contains information about each execution plan associated with a query. ( https://msdn.microsoft.com/en-us/library/dn818155.aspx_

Query:

[sql] SELECT * FROM sys.query_store_plan [/sql]

 

Some important columns from a troubleshooting standpoint (table 3):

table_3v1

 

So, there are a lot of interesting columns in this Catalog View as you probably noticed. To properly explain about it, let’s work with some possible scenarios:

 

Scenarios:

1. Scenario 1: You migrated from SQL Server 2012 to SQL Server 2016. You are aware of a change in the Cardinality Estimator between SQL Server 2012 and 2014 (and later versions). Now, that you are using SQL Server 2016 you want to check the execution plan in 2012 and 2016 for a query.

This scenario has a query that presents a different estimate number for rows in the same database, but with a different compatibility level (Example extract from the Microsoft white paper: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator)

[sql]
ALTER DATABASE CONTOSO_MAIN SET COMPATIBILITY_LEVEL = 110

GO

SELECT[AddressID],

[AddressLine1],

[AddressLine2]

FROM Person.[Address]

WHERE [StateProvinceID] = 9 AND

[City] = N'Burbank' AND

[PostalCode] = N'91502'

ALTER DATABASE CONTOSO_MAIN SET COMPATIBILITY_LEVEL = 130

GO

SELECT[AddressID],

[AddressLine1],

[AddressLine2]

FROM Person.[Address]

WHERE [StateProvinceID] = 9 AND

[City] = N'Burbank' AND

[PostalCode] = N'91502'

OPTION (RECOMPILE)

[/sql]

Note: The Recompile option to regenerate the execution plan for the second query.

Capture Mode Query Store settings should be: All (https://blogs.technet.microsoft.com/dataplatform/2016/10/25/query-store-settings-and-limits/)

 

Figure 7 will show the query result, you may noticed the same query with 2 different compatibility level ( 130, 110), and figure 8 and 9 you can check the same information in the Query Store reports ( Top Resource Consumption).

figure_7

Figure 7: Compatibility level result

 

figure_8

Figure 8: Compatibility level - Query Store Report  1

 

figure_9

Figure 9: Compatibility level Query 2

 

You may notice the both queries have different estimated number of rows, as expected. That is because the way that SQL Server estimates the number of rows change in SQL Server 2014. For more information about the new CE: ( https://msdn.microsoft.com/en-us/library/dn673537.aspx )

Query:

[sql]

SELECT qq.query_hash

, qq.initial_compile_start_time

, qq.last_compile_start_time

, qq.last_execution_time

, qq.avg_compile_memory_kb

, qq.last_compile_memory_kb

, qq.max_compile_memory_kb

, qp.compatibility_level

, cast (qp.query_plan as XML) query_plan

, t.query_sql_text

, t.statement_sql_handle

, qp.Query_id

FROM sys.query_store_query_text t

JOIN sys.query_store_query qq

ON t.query_text_id = qq.query_text_id

JOIN sys.query_store_plan qp

ON qp.query_id = qq.query_id

ORDER BY QQ.last_compile_start_time DESC

[/sql]

2.Scenario 2: You saw a lot of waits for CXPACKET in your environment and you want to quickly check how many parallel and trivial queries you have.

Note:

Parallel: SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). (https://technet.microsoft.com/en-us/library/ms178065(v=sql.105).aspx )

Trivial: When the plan is a "trivial plan." A trivial plan results when the query optimizer determines that given the tables referenced in the query and the indexes existing on them, only one plan is always optimal. (https://msdn.microsoft.com/en-us/library/ee343986(sql.100).aspx )

 

Query:

[sql]

SELECT

qq.query_hash

, qp.is_trivial_plan

, qp.is_parallel_plan

, qp.compatibility_level

, cast (qp.query_plan as XML) query_plan

, t.query_sql_text

,COUNT(*)

FROM sys.query_store_query_text t

JOIN sys.query_store_query qq

ON t.query_text_id = qq.query_text_id

JOIN sys.query_store_plan qp

ON qp.query_id = qq.query_id

GROUP BY qq.query_hash

, qp.is_trivial_plan

, qp.is_parallel_plan

, qp.compatibility_level

, query_plan

, t.query_sql_text

[/sql]

 

3. Scenario 3: You are using SQL Server 2016. You follow the steps in this post (https://blogs.technet.microsoft.com/dataplatform/2017/01/31/query-store-how-it-works-how-to-use-it/) to force a plan. For some unknow reason the force plan failed, you want to troubleshoot that.

Query:

[sql]

SELECT

qq.query_hash

,qp.last_force_failure_reason

,qp.last_force_failure_reason

,cast (qp.query_plan as XML) query_plan

,t.query_sql_text

FROM sys.query_store_query_text t

JOIN sys.query_store_query qq

ON t.query_text_id = qq.query_text_id

JOIN sys.query_store_plan qp

ON qp.query_id = qq.query_id

WHERE qp.force_failure_count >0

[/sql]

 

4. Scenario 4: You have native compile stored procedure and you want a column that you could quickly check if the procedure is natively compile or not.

  1. Note: For Query Store track native compile stored procedure you need to enable it: use the procedure to enable [sys].[sp_xtp_control_query_exec_stats]
  2. ( https://msdn.microsoft.com/en-us/library/dn435917.aspx )

Query:

[sql]

SELECT

qq.query_hash

,qp.last_force_failure_reason

,qp.last_force_failure_reason

,cast (qp.query_plan as XML) query_plan

,t.query_sql_text

FROM sys.query_store_query_text t

JOIN sys.query_store_query qq

ON t.query_text_id = qq.query_text_id

JOIN sys.query_store_plan qp

ON qp.query_id = qq.query_id

WHERE qp.is_natively_compiled = 1

[/sql]

Another relevant information related to Query Store Catalog Views is they are normalized. A change in a Set of options ( for example, Set Ansi Null on or off). it will influence a creation of a new execution plan.

4. sys.query_context_settings: It contains information about the semantics affecting context settings associated with a query. There are a number of context settings available in SQL Server that influence the query semantics (defining the correct result of the query). The same query text compiled under different settings may produce different results (depending on the underlying data).

 

Query:

[sql]  SELECT * FROM sys.query_context_settings [/sql]

You can check the set context options using the column set_options ( table 4).

 

table_5v1

And now, some information about Runtime Stats Store

 

1.sys.query_store_runtime_stats: It contains information about the runtime execution statistics information for the query. (https://msdn.microsoft.com/en-us/library/dn818147.aspx )

 

Query:

[sql] SELECT * FROM sys.query_store_runtime_stats [/sql]

Some important columns from a troubleshooting standpoint (table 5):

table_4

So, basically the stats information for the execution plan is stored in this Catalog View. Note a new information that was not available in other versions, standard deviation. Another interesting column for troubleshooting perspective is execution type column. Suppose you want to be able to track: query abortion, timeout errors... Now, you can:

 

Query:

[sql]

SELECT

qq.query_hash

,qrt.execution_type

,qrt.execution_type_desc

,cast (qp.query_plan as XML) query_plan

,t.query_sql_text

FROM sys.query_store_query_text t

JOIN sys.query_store_query qq

ON t.query_text_id = qq.query_text_id

JOIN sys.query_store_plan qp

ON qp.query_id = qq.query_id

JOIN sys.query_store_runtime_stats qrt

ON qrt.plan_id = qp.plan_id

WHERE execution_type ! = 0

[/sql]

 

I simulate a deadlock situation in the Adventureworks database, and the result you can check at figure 10:

figure_10

Figure 10: Deadlock error tracked

 

Another Catalog View for this store is the sys.query_store_runtime_stats.

2. sys.query_store_runtime_stats_interval: it contains information about the start and end time of each interval over which runtime execution statistics information for a query has been collected.  (https://msdn.microsoft.com/en-us/library/dn818158.aspx )

Query:

[sql] SELECT * FROM sys.query_store_runtime_stats_interval [/sql]

 

And that is it! A summary of the new query store Catalog Views and how to use.

Liliam Leme

UK SQL PFE