SQL SERVER – Same Result Same Query Plan – Different Entry in Cache

This question was recently discussed with my client during the Comprehensive Database Performance Health Check. The question I was asked was about a query that had the same result same query plan but the different entry in the cache, is that possible. Yes, it is totally possible.

Let us see an example for the same.

Here are two queries, which will generate exactly the same result and execution plan.

USE AdventureWorks
GO
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE OrderQty IN (1,2);
GO
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE OrderQty IN (0,1,2);
GO

Additionally, note the query two has a different where condition but there is no OrderQty value as 0 so it will not return any additional results. However, if there was an OrderQty difference, it will have different results.

Now let us check the execution plan, both the execution plan are identical and the results were the same as well.

Here is the blog post where you can download the query to inspect your cache memory: SQL SERVER – List Query Plan, Cache Size, Text and Execution Count. However, when I run the query to check the entries in the cache, there are two different entries for both the queries. Now, this can be easily believable as both the queries are technically different queries.

Now let us keep both the queries absolutely the same but just change the upper case to lower case for a few of the operators of the query.

USE AdventureWorks
GO
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE OrderQty IN (1,2);
GO
select *
from [Sales].[SalesOrderDetail]
where OrderQty IN (1,2);
GO

Well, there we go, even though the query is not the same, still the cache entry is different. This is because when we change anything in the query even a white space that is not used, the query will generate a new plan. If you are using an entity framework or generating the dynamic query every single time, it is quite possible you have lots of cache entries for a single query.

Note: It is recommended that you use the same way to write a single query or a stored procedure, otherwise, there will be plenty of entry of your execution plan in the cache and it is indeed a nightmare for SQL Server Performance tuning experts like me to identify the real troublemaker.

If you have situations like queries running fast sometimes and suddenly getting slow and they are ad-hoc queries, there are quite possibilities that you are facing issues of your cache plan bloating.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• Copy Database – SQL in Sixty Seconds #169
• 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
• Excel – Sum vs SubTotal – SQL in Sixty Seconds #167

Reference: Pinal Dave (http://blog.SQLAuthority.com)