Category: SQL Server

by Sergi Roig 2024-05-08

How to search queries without parameterisation in Query Store

Welcome to a new article on the Aleson ITC blog. In today’s article we will show you how to search queries without parameterization in Query Store.

Recently we had a problem on a production server of one of our customers. This problem was related to Query Store and Ad–Hoc processes. It is very important to know that BY DEFAULT QUERY STORE DOES NOT FILTER ADHOC PROCESSES even if we have the option Optimize for Ad-Hoc workloads, unless we have the capture mode set to Auto or Custom.

But… how can we know if we have these types of charges in our environment?

The truth is that it is quite simple. But first we must have the capture configuration set to “all” in our Query Store. And this way we will be capturing all the queries regardless of the number of executions, CPU usage time, etc.

QUERY_CAPTURE_MODE = ALL

To see if the loads of our environment are Ad-hoc we will use the Query Store panels. Specifically the TOP queries with more executions. To do this we go to the database we want to analyse, deploy the Query Store option and open the TOP Resource Consuming Queries.

In the graph, select Execution Count and it will be sorted by the number of executions.

In our case, the difference between executions is quite noticeable:

After setting the capture mode to ALL, Query Store will start storing all kinds of queries. In the image you can see queries with many executions and others with very few or even 1 execution.

If you have many queries with 1 execution, you should consider the PARAMETERIZATION FORCED option so that SQL tries to parameterise the queries and thus reuse the execution plans.

Is it always good practice to apply this Query Store option?

The answer is NO. This option should be reviewed and applicable in cases where most of the queries are Ad-hoc but the execution plans are similar. The reason is that when you enable this option SQL will start to generate “not so good” execution plans and a higher usage of the plan cache. Also, it should be noted that when we apply this option all cached execution plans will be deleted.

What should we base this Query Store option on?

The first thing we should rely on is whether our database has a lot of Ad-hoc code.

With the following query we can perform the analysis:

--If the number of different queries is much lower than the number of queries, it means that our queries are Ad-hoc.
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query; 

--If the number of different plans is much lower than the number of plans, it means that our queries are Ad-hoc.
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;

If it is quite different, there are two options.

Apply PARAMETERIZATION FORCED for the whole database, this will make SQL try to parameterise automatically all queries launched on this database.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

The other option is to apply the option on the queries that we have detected that are Ad-hoc and create a guide plan for these queries. But this point requires much more explanation and would be the subject of another blog post.

Once finished we can change the Query Store capture option back to how we had it before.

Conclusion

We can use Query Store to detect Ad-hoc workloads and thus assess whether we should apply the PARAMETERIZATION FORCED option for the whole database to reuse execution plans.

Thanks to this tool we can optimise a SQL Server where the workload is mostly Ad-hoc queries.

Thank you very much for reading, I hope you were able to solve any doubts and I’ll see you in the next blog!

Don’t let data management for your SQL Server slow down your progress!