T-SQL Tuesday #164: Code that made me feel happy

by dbanuggets, posted in T-SQL Tuesday

This month T-SQL Tuesday is hosted by Eric Darling asking us to write about the code that made us feel a way.

I would like to mention the Query Store hints and why I really liked it. If you have a parameter-sensitive query in your stored procedure and you need to use a hint (For example, using RECOMPILE hint in this case) to fix the issue quickly without changing the actual code, using query store hints is the best option. Not only that, but you can also use other useful hints like setting up the MAXDOP, Compat level etc. For the list of supported and unsupported hints, look here.

Remember: This is the best last option to choose when you cannot change the code. It is always best to refactor the stored procedure.

It is very easy to use. You just need to collect two things. The query ID of the stored procedure and the query hint option you would like to use. There is a special stored procedure that you can use to implement this Query hints into your stored procedures.

To find the query_id of your query, run the below code by changing the Query text in the like operator:

/* Find the query ID associated with the query. Source */
SELECT query_sql_text
,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’%query text%’
AND query_sql_text NOT LIKE N’%query_store%’;
GO

Use the Query_id to run the below-stored procedure with the hint you would like to use

EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N’OPTION(USE HINT(”RECOMPILE”))’;

Query store can also capture ad-hoc workloads. This can fill up your query store real quick if you have a lot of ad-hoc queries coming from your applications. If you can parameterize these queries, configuring PARAMETERIZATION = FORCED the database can be an option. Look more about Forced parameterization here. If you can’t parameterize those ad-hoc queries, you can set the Optimize for Ad hoc Workloads server option to save the cache memory on the queries that will execute only once. If you do not want to capture this kind of queries in the Query Store, set QUERY_CAPTURE_MODE to AUTO.

Remember:

  1. If you enable the forced parameterization and use the query hint RECOMPILE at the same time, the query engine will ignore that query hint and proceed with using any other hints used. If you are using the Azure SQL database, you will see the error with code 12461 when the RECOMPILE query hint will be ignored. Source
  2. Query Store will only store the latest hints that are active. It will not store the history of the hints that were active once. To capture that information, you can use extended events. I have written a blog post here on exactly how you can set this up so you can get the history of the hints.

I am looking forward to reading other SQL family member posts on this month’s T-SQL Tuesday post hosted by Erik Darling!

Thanks for reading!

Leave a comment