Since Tuesday, I have the chance to follow the SQLBits 2022 conference. Sadly, I have to do it remotely this year but I promise next time I will come in-person 😉
SQLBits is one of the largest SQL Server conference in the world with some famous speakers like Brent Ozar, Bob Ward, Erik Darling…
In this blog post, I will come back on one of the tips Erik gave us during his training session of one day about The Professional Performance Tuning Blueprint and show you how dynamic SQL can save our life, here save the performance, when parameter sniffing is not good enough.

We have a Stored Procedure, here called MySP, where we select the top 10 values from Posts table join to Users table where column called ParentID in the Posts table equals the supplied ParentID column.
CREATE OR ALTER PROCEDURE
dbo.MySP
(
@ParentId int
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SET STATISTICS XML ON;

SELECT TOP (10)
u.DisplayName,
p.*
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE p.ParentId = @ParentId
ORDER BY u.Reputation DESC;

SET STATISTICS XML OFF;

END;
GO
We have also a non-clustered index on the Posts table where index keys are ParentId and OwnerUserId:

CREATE INDEX
grmpf
ON dbo.Posts
(ParentId, OwnerUserId)
WITH
(MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO
After having removed the plans in cache with DBCC FREEPROCCACHE, we run the Stored Procedure with ParendID equals to 184618 and check the execution time and query plan:

We can see that the query runs very fast, 7ms, it uses index seek on Posts table with our non-clustered index, the Posts clustered index for the lookup to find the others needed columns and do an index seek also on Users table with the clustered index of this table.
Nothing special to tell here.
Lets run the same Stored Procedure with ParentId equals to 0 and check the result:

The result is not exactly the same… The execution time increases dramatically with more than 11 minutes.
The query retrieves more than 6 Millions rows instead of 518… it takes a lot of time processing these rows for the fourth operators.
Here we have issues with parameter sniffing where a plan is very good for a small number of rows but bad for a large number of rows.
The first question is “can we fix the key lookup?”, here we want to retrieve all columns of the Posts table so we will not create an index which will cover the start of a query.
If we remove again the plans cached, execute the second query first (with ParentID = 0) and after the one with ParentId equals 184618 we have a completely different results:

Execution for ParentId equals 0 is now really fast compare to the previous one and the one with ParentId equals 184618 is still acceptable.
But the plan has changed and if the query is executed in the bad order performance problem will come back.
A way to solve this issue would be to add the hint OPTION(RECOMPILE) in the Stored Procedure to RECOMPILE our query and recreate a fresh query plan each time the SP is executed.
But Erik showed us that doing recompilation each time the Stored Procedure is executed can used CPU and it’s not where we want to use our CPU!
To fix our problem we can try to create a selective index on the Posts table, put the column ParentID first and have a look of that happens. But if we check the distribution of the data in the Posts table:

The selectivity of our index will be more than poor for 99% of the records…
To avoid this recompilation problem and to boost our SP the best way is to use Dynamic SQL.
We will write a Dynamic SQL which will force the recompilation if we execute our Stored Procedure for ParentId equals 0:
CREATE OR ALTER PROCEDURE
dbo.MySP
(
@ParentId int
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

DECLARE
@sql nvarchar(MAX) = N'';

SET @sql += N'
SELECT TOP (10)
u.DisplayName,
p.*
/*dbo.MySP*/
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE p.ParentId = @iParentId
ORDER BY u.Reputation DESC';
IF @ParentId = 0
BEGIN
SET @sql += N'
OPTION(RECOMPILE);';
END;
ELSE
BEGIN
--We're so tidy.
SET @sql += N';';
END;

EXEC sys.sp_executesql
@sql,
N'@iParentId int',
@iParentId = @ParentId;

END;
GO
In this code we check if the parameter @ParentId of the Stored Procedure is 0 and in this case we force a recompilation. So we will have a complete new plan for the value 0 but not for the other ones where the number of records is really low and where we don’t have a chance to have a bad plan.
If we execute our 2 queries:

Now both plans are perfectly fast, we can observe on the second one that we have our option recompile which generated an optimized parallelized plan for ParentId equals 0.
This kind of pattern is efficient in our case but will not be if we don’t know the values which cause problem…
Erik gave us another way to create a dynamic SQL script which will solve the problem when problem values are unknown.
The goal here is to create a new plan for any query that comes in but without recompilation.
For that Erik don’t use anymore the RECOMPILE hint but will use the OPTIMIZE FOR hint with a funny value which will be replace for the execution by the value of the ParentId parameter.
As SQL Server caches execution plan based on the query text, a new plan will be create for each value of the parameter.
CREATE OR ALTER PROCEDURE
dbo.mySP
(
@ParentId int
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN

DECLARE
@sql nvarchar(MAX) = N'';

SET @sql += N'
SELECT TOP (10)
u.DisplayName,
p.*
/*dbo.OptionalRecompile*/
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE p.ParentId = @iParentId
ORDER BY u.Reputation DESC
OPTION(OPTIMIZE FOR(@iParentId = [@@@]));
' + NCHAR(10);

SET @sql =
REPLACE
(
@sql,
N'[@@@]',
@ParentId
);

--to print the script
RAISERROR(@sql, 0, 1) WITH NOWAIT;

SET STATISTICS XML ON;

EXECUTE sys.sp_executesql
@sql,
N'@iParentId int',
@ParentId;

SET STATISTICS XML OFF;

END;
GO
If we execute our Stored Procedure now with our two values:

We have a specific plan for each value, we can see our hint OPTIMIZE FOR (@ParentID = with the value of our parameter. Plans are the same than before with the RECOMPILE hint but this dynamic SQL is more generic and can be used with unknown problem values.
We can verify that cached plan is used for each parameter values and that we don’t have a new plan after each execution (like with recompile) with the sp_BlitzCache Stored Procedure from Brent Ozar:

That did the trick!

I hope this tip can help you 😉
I would like to warmly thanks Erik Darling for those tips and the excellent training session (The Professional Performance Tuning Blueprint) he did during the SQLBits 2022.

Post Views: 1,987
Thumbnail [60x60]
by
Stéphane Savorgnano