I need to confess: I’m proud of the article Query Store and Parameterization Problems I wrote. Today it’s no news at all (although some people may not know the details about plan guides I included in the article) but I wrote it in 2017, based on SQL Server 2016, when the idea of identifying parameter sniffing with query store was very fresh.
Jump to today: Query Store got a new feature, Query Store Hints. This feature is already being the base for many performance improvements in SQL Server. The idea is the possibility to include query hints on the query store queries.
In my article about parameterization, I made use of plan guides exactly because it was the only way to include hints on the queries. Query Store Hints change that, and in my opinion, they make plan guides obsolete. What other reason would make you still use a plan guide today? I would love to hear about it on the comments.
Preparing the Environment
Let’s prepare an environment for some tests.
First Step: Provision a demonstration database
When you are provisioning an Azure SQL Database you can choose to provision a demo database, AdventureWorksLT. Let’s start with one.
Second Step: Creating a view to replate spt_values
We need to make the tables bigger for the example. Many years ago, Adam Machanic created a script called MakeBigAdventure.sql. The script create two tables inside the Adventureworks database, Bigproduct and BigTransactionHistory.
The script was made to work on AdventureWorks in a SQL Server on premises. It uses the table master..spt_values to randomize the records.
On Azure SQL, we don’t have access to the master..spt_values table, so we need to replace it with something else. We can create a view for this purpose. The code of the view will be the following:
AS
WITH int1(z)
AS (SELECT 0
UNION ALL
SELECT 0),
int2(z)
AS (SELECT 0
FROM int1 a
CROSS JOIN int1 b),
int4(z)
AS (SELECT 0
FROM int2 a
CROSS JOIN int2 b),
int8(z)
AS (SELECT 0
FROM int4 a
CROSS JOIN int4 b),
int16(z)
AS (SELECT TOP 2048 0
FROM int8 a
CROSS JOIN int4 b)
SELECT Cast (NULL AS NVARCHAR(35)) [name],
Row_number()
OVER (
ORDER BY z) – 1 AS [number],
Cast (‘P’ AS NCHAR(3)) [type],
Cast (NULL AS INT) [low],
Cast (NULL AS INT) [high],
0 [status]
FROM int16
Original link: https://devio.wordpress.com/2018/06/03/generating-a-range-of-numbers-and-dates-in-t-sql/
Third Step: Fix the script to work with AdventureWorksLT
AdventureWorks has the schema Product, while AdventureWorksLT has the shema SalesLT. You need to replace the Product schema by SalesLT. A simple find/replace solves the problem.
Fourth Step: Fix the name of spt_values in the script
A simple replace from master..spt_values to dbo.spt_values will do the work.
Fifth Step: Create an Index
Let’s create an index over a field that we know will have an uneven distribution and due to that will cause the parameter sniffing problem.
You can use the following statement:
ON [dbo].[BigProduct] ([listprice])
go
Establishing the problem
This is the same problem describe on the article Query Store and Parameterization Problems, but we will use a different solution.
Let’s create a stored procedure which causes the parameterization problem. The procedure code is below:
AS
SELECT *
FROM dbo.bigproduct
WHERE listprice = @listprice
If you check the estimated execution plan of the code below, you will notice each procedure execution has a different query plan. This happens because the distribution of values in the field is not even. Some values appear way more frequently than others. The use of With Recompile will ensure the plans of these executions will not be stored in the cache
9.99 WITH recompile
EXEC Filterprice
337.22 WITH recompile
Causing the problem
The result of the execution of the queries again, without using the With Recompile is both queries will be using the same plan, which will be wrong for one of them.
9.99
EXEC Filterprice
337.22
When one of these procedures is executed without the recompile option, the plan will be included in the query plan cache. The next execution will use the same plan again, causing a problem, because the plan may not be the best choice to the value.
On the original article, we solved the problem using a plan guide to force the recompile of the query. Let’s solve the problem this time using Query Store Hints.
Using Query Store Hints to solve the problem
The Query Store hints are applied over the queries. We need to discover the id of this query inside query store. The following statement can do the work:
q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id
WHERE query_sql_text LIKE N’%bigproduct%’
go
Even the query being inside the stored procedure, it will be individually recorded in query store. Once we know the id of the query, we can make the application of the query hint using a stored procedure:
@query_id=1,
@value = N’OPTION(RECOMPILE)’;
go
Monitoring
Once you decide to apply query store hints, there are some monitoring tasks you need to execute:
- You need to monitor the hints. In case of a failure in a hint, you need to react to that.
- You need to monitor query regressions. If a hint is not needed anymore, you can suffer from a query regression and this will appear as part of the monitoring.
The following query can be used to monitor the hints:
query_id,
query_hint_text,
last_query_hint_failure_reason,
last_query_hint_failure_reason_desc,
query_hint_failure_count,
source,
source_desc
FROM sys.query_store_query_hints;
go
The Future
SQL Server 2022 is bringing many new optimization features. They are becoming more intelligent and being built over previous existing features. Some of the new optimization features will be automatically creating query store hints.
Conclusion
The SQL Server optimization features are becoming more intelligent on each version. Each new optimization feature, however, brings new and more advanced needs for monitoring.
The post Query Store and hints: More Powerful than ever appeared first on Simple Talk.
from Simple Talk https://ift.tt/2ZzeP5s
via
No comments:
Post a Comment