Boost dynamic SQL with Plan Guides

Recently a customer asked me to check why a load process from a banking application has so poor performance.
In fact this process is extremely slow, between 40 minutes up to more than one hour to load around 240’000 rows. As the application is a black box for my customer I have first to find out which queries are running during this process, which ones are the most time consuming and why.

To find out which queries are running against your instance or a specific database you have multiples solutions going from the “old school” SQL Server Profiler to a more recent one the Extended Event sessions and even the Query Store which can help. In my case the Query Store was not enable, my customer is running SQL Server 2016 Enterprise Edition, so I started it.
I created an Extended Event session where I selected the rpc_completed event and started it during the load process.
I quickly found out the guilty query which was a classical parameterized Dynamic SQL executed with sp_executesql.

The script is looking as follow:

exec sp_executesql N'SELECT ...... FROM ..... WHERE ..... GROUP BY ..... ORDER BY .....' ,N'@P0 date,@P1 date,@P2 date','2023-10-20','1900-01-01','2023-10-27'

When I ran it on my Management studio with the same parameters the duration was around 35 minutes to retrieve 6200 rows (another query runs after this one to load the 240’000 based on those ones but this query is fast enough):

If I check in my Query Store for the Top Resource Consumers for my database, I can find my query:

Now the question is: “How can I boost this query?”.
By chance I followed last year a SQL Bits session from Erik Darling about Performance Tuning and Parameter Sniffing.
During its session Erik spoke about the possibility to use the hint OPTIMIZE FOR.
Here as I have three parameters I will use the hint OPTIMIZE FOR UNKNOWN which will tell SQL Server to make a blind assumption to completely kill parameter sniffing for my three parameters.
My new script will look like:

exec sp_executesql N'SELECT ...... FROM ..... WHERE ..... GROUP BY ..... ORDER BY ..... OPTION (OPTIMIZE FOR UNKNOWN)' ,N'@P0 date,@P1 date,@P2 date','2023-10-20','1900-01-01','2023-10-27'

When I execute my script now, the magic happens, the time to execute the query is now less than 5 seconds:

The tricky part is that the query is generated automatically by the application which is not developed in house, so how can I add my hint to the query?
With SQL Server 2022 and the possibility to add hints via the Query Store the task would have been easy but with SQL Server 2016 it’s not an option.

The solution here is the Plan Guides.
Plan guides give the possibility to optimize the performance of queries when there is no possibility to directly change the text of the actual query in SQL Server. Plan Guides force the optimization of queries by adding query hints or a fixed query plan to them. They can be used when queries coming from applications provided by a third-party vendor are not performing as expected.
In the Plan Guide, you need to mention the T-SQL statement that you need to optimize and add an OPTION clause where you define the query hints you want to use or a specific query plan you want to use to optimize the query. During execution of the query, SQL Server will match the T-SQL statement of the query to the Plan Guide and will attach the OPTION clause to the query at run time or will use the specified query plan.

When we create the Plan Guide, the used T-SQL must exactly match the one provided by the third party application. This query has to be the same as the SQL Server compiler receives. Microsoft advises to use the SQL Server Profiler to capture the actual batch and parameter text. If there are some difference the Plan Guide won’t be used.
Before to know this point, I tried to use the T-SQL text coming from the Query Store or coming from the DMVs sys.dm_exec_sql_text but created Plan Guides were never used during the execution of the query from the application. To avoid to waste time please follow this Microsoft guide.

Once you have your T-SQL statement you can create your Plan Guide with the sp_create_plan_guide Stored Procedure.
My one looks like that:

EXEC sp_create_plan_guide 
@name = N'<database_name> plan guide with hint SQL coming from profiler', 
@stmt = N'the T-SQL catches with my SQL Server Profiler',
@type = N'SQL',
@params = N'@P0 date,@P1 date,@P2 date',
@hints = N'OPTION (OPTIMIZE FOR UNKNOWN)'
GO

Once created you can see your new Plan Guides on SSMS:

When the application will execute again the T-SQL, the Plan Guide will be used and the hint will boost the execution as expected:

We can see that the script duration is much faster with around 5 second instead of 30 minutes and the plan uses the new Plan Guide with the hint we passed.

Plan Guides are very powerful and can help to provide better performance for scripts where direct modifications are not an option. A second option would be to migrate to SQL Server 2022, we are also looking on this direction at my customer place but as it will take some times, Plan Guides will be used in the meantime 😉