In the last post we talked about the concept of creative laziness and how SQL Server’s query optimizer notices when the user has asked for something that requires no actual work due to a logical contradiction. In this post, we’ll look at another example of where the optimizer works smarter not harder and can reduce unnecessary work.

The Query

Let’s say we have this query which finds the newest user by ID for a given location and then gives us a count of all the posts that user has made. For this example, we’ll use the 2010 version of the Stack Overflow Database, provided under cc-by-sa 4.0 license from Stack Exchange Data Dump and running in compatibility level 160 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM.

DECLARE @User NVARCHAR(100);

SELECT	@User = DisplayName
FROM	dbo.Users
WHERE	[Location] = 'USA'
ORDER BY Id DESC;

SELECT	COUNT(*)
FROM	dbo.Posts
WHERE	LastEditorDisplayName = @User;

It’s a simple query and we use a variable in this example, though in real life there would be other ways to achieve this also. We get a result of 5:

There are five posts where the last edit made was by the most recent user from USA.

Let’s change the location:

DECLARE @User NVARCHAR(100);

SELECT	@User = DisplayName
FROM	dbo.Users
WHERE	[Location] = 'Hopefully no-one has chosen this location'
ORDER BY Id DESC;

SELECT	COUNT(*)
FROM	dbo.Posts
WHERE	LastEditorDisplayName = @User;

This time our result is 0:

There are no posts where the last edit was made by the most recent user from “Hopefully no-one has chosen this location”.

Let’s have a look at the IO stats for the query, exposed by adding SET STATISTICS IO, TIME ON:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'Users'. Scan count 9, logical reads 7778, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 43 ms.

Table 'Posts'. Scan count 9, logical reads 815844, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1109 ms,  elapsed time = 278 ms.

We can see over 800k page reads have been performed and a look at the execution plan shows us this is because we don’t have a suitable index and so the entire Users and Posts tables are scanned:

Let’s add a RECOMPILE hint:

DECLARE @User NVARCHAR(100);

SELECT	@User = DisplayName
FROM	dbo.Users
WHERE	[Location] = 'Hopefully no-one has chosen this location'
ORDER BY Id DESC;

SELECT	COUNT(*)
FROM	dbo.Posts
WHERE	LastEditorDisplayName = @User
OPTION (RECOMPILE);

We get the same result:

However, let’s look at our stats:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'Users'. Scan count 9, logical reads 7778, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 36 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Our page reads have gone down to 7778, which are the reads associated with the first query as before, however, the reads for the Posts table have completely gone and our execution time is right down. If we look at the execution plan, we can see why this is:

The second query is reduced to a constant scan that returns the scalar value of 0 (our query result). Let’s break down what has happened here:

We added an OPTION(RECOMPILE) hint. What this does is tell SQL Server to compile an execution plan for the query at execution time, meaning it will compile a plan at each execution. As compilation is deferred until execution time, SQL Server now knows the value of @User and can use parameter embedding optimization (PEO) to use it in the plan as though it had been entered as a literal.

In our original ‘Hopefully no-one has chosen this location’ query, without the hint:

DECLARE @User NVARCHAR(100);

SELECT	@User = DisplayName
FROM	dbo.Users
WHERE	[Location] = 'Hopefully no-one has chosen this location'
ORDER BY Id DESC;

SELECT	COUNT(*)
FROM	dbo.Posts
WHERE	LastEditorDisplayName = @User;

When we hit execute, SQL Server will compile the execution plan for both SELECT queries above. At that time, SQL Server has not yet executed the queries so it does not know what the value of @User will be. Therefore for the COUNT(*) query, SQL Server will compile a plan which it deems appropriate for most given values.

So what was the value of @User?

It probably comes as no surprise that no users have a location of “Hopefully no-one has chosen this location” and therefore the value of @User is NULL:

DECLARE @User NVARCHAR(100);

SELECT	@User = DisplayName
FROM	dbo.Users
WHERE	[Location] = 'Hopefully no-one has chosen this location'
ORDER BY Id DESC;

SELECT @User;

Therefore the query

SELECT	COUNT(*)
FROM	dbo.Posts
WHERE	LastEditorDisplayName = @User;

will not return 0 as we cannot test NULL for equality.

When we add OPTION(RECOMPILE) SQL Server compiles the second query using the value of @User as though it was a literal, given it knows what that value is because the first query has now executed.

At this point, SQL Server knows we cannot use NULL in an equality predicate and therefore the value of 0 is guaranteed so it decides not to even bother reading the Posts table and just returns a literal value of 0, saving the 800k reads and the associated CPU / execution time.

Conclusion

So should we just add OPTION(RECOMPILE) to every query? Not so fast – there are reasons it’s not on by default and it has drawbacks which we’ll discuss another time, however, the point of this post was to share an example of how SQL Server can be clever behind the scenes, thus saving itself work, and allowing us, the end user, to reap the benefits.

References / Further Reading

Microsoft – Query hints (Transact-SQL)

Microsoft – Recompile a Stored Procedure

Paul White – Be Careful with LOBs and OPTION (RECOMPILE)

Posted in