Top 20 SQL Statements by Cache Size

If you have arrived at this page as a result of running the Server Health report, congratulations you are on the first step to improving your server health.

 

At SQL Saturday 114 in Vancouver BC, after presenting the SQL Server Health Reports, it was suggested that I add a new report for the top statements by cache size.  Based on this suggestion I have added a new report called Top 20 SQL Statements by Cache Size.

To get this report, just download the Server Health report project and try it out.

Cache Explained

The plan cache is the area of memory that SQL Server uses to store queries that have been parsed, and could be reused again.  For re-use when the exact same query is sent to the SQL Server, if that query has already been parsed in the plan cache, that plan will be used which saves time (and memory) when the query is being run.

The size of the plan cache varies depending on the amount of memory on your SQL server, and the amount of one-time use queries, and the amount of queries that are being re-used.

Here is the TSQL to check the current size of your plan cache.

select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed
from sys.dm_os_memory_clerks
where name = 'SQL Plans'
group by name;


Plan cache is a good thing, and a large plan cache can even be a good thing as long as the plan cache is holding queries that are likely to be re-used again.  If the plan cache has hundreds of MB or even more than a Gb of queries that will never be used again, or not likely to be used again, then it is being wasted, and your queries will not be running as fast as it possibly can.

 

Take the following 3 queries:

 SELECT d.* FROM [Departments] d WHERE d.department like 'Swimming';

go
 SELECT d.*
 FROM [Departments] d
 WHERE d.department like 'Clearance';

go
 SELECT d.*
 FROM [Departments] d
 WHERE d.department like 'Gifts';

These three queries above will each take up 3 different parsed query plans in the plan cache, and will never be used again unless they are called with the exact same parameter (Swimming, Clearance, or Gifts).  But if instead you parameterize the queries as shown below with a stored procedure, then the plan will exist in memory only once, and it will be re-used every time it is called.   The Stored Procedure method for parameterization is not optimal, but it makes a good demo.  The better way to parameterize is the use the language specific parameterization methods from the program that is calling the database query.

-- same functionality with only one query in the cache
CREATE PROCEDURE [dbo].[TestParams]
@Param1 varchar(1024)
AS
BEGIN
SELECT d.*
FROM [Departments] d
WHERE d.department like @Param1;
END
GO
exec TestParams 'Swimming';
GO
exec TestParams 'Clearance';
GO
exec TestParams 'Gifts';
GO


 

Parameterization can be done in classic ASP, ASP.NET and PHP, along with many other programming languages and environments.

Example Scenario

Imagine if you were working on a database for a web based system that had 10,000 customers who regularly used the system.   In this example each of those customers accessed several web pages that in an average session ran a total of 50 different queries to get at the data, pages, and information that they were looking for.  Also keep in mind that those 50 queries each contained their customer ID (non-parameterized), thus making it a different query than other customers.  The consider that average cache plan for those queries was 512kb (not very large for a cache plan).

Here’s the math on what would be required in the cache plan for those customers, not counting other queries that were being run on the server.

 

The first example the amount of memory is proportional to the number of active customers, in the second example it doesn’t matter how many active customers you have, the plan cache is the same.

Also, with this example you may be looking and thinking, that can’t be right 244GB, I don’t have that much memory.  Well with less than enough memory, what is going to happen each time a plan is parsed and put into the cache, another plan is going to be pushed out, and your cache will simply become a place for storing discarded queries.

I have seen examples where as much as 25% of the entire server memory was taken up with plan cache, then after parameterization, less than 5% of the server memory was being used for cache, freeing up 20% to use for other parts of SQL Server.

Summary

Parameterization is not always possible, for instance if you are running a commercial application that you don’t have access to the source code to make changes.  In this case your only options is to run a bit slower, or to add more memory to compensate for the cache size.

Always use parameterized queries to avoid the cache thrashing problem, and use the Top 20 SQL Statements by Cache Size to find the biggest offenders on your system, and work on fixing those first.

Getting Help from Steve and the Team

Contact us for your Free 30 minute consultation OR
schedule your appointment now.