Plan Cache bloat is generally bad – it wastes CPU, it wastes memory. Plan Cache bloat happens when SQL Server stores multiple query plans for seemingly identical queries….except they’re not identical. Let’s look at an example.
Before we begin, let’s ditch the plan cache as this will help highlight the impact of our code. DON’T DO THIS IN PRODUCTION!
Now let’s run a simple query using dynamic SQL. This one happens to output the file sizes for tempdb. Note how we’re outputting the current date/time using a variable we’ve declared higher up – this is important and we’ll come back to that later!
DECLARE @SQL nvarchar(max); DECLARE @Now datetime = GETDATE(); SET @SQL = ' USE tempdb SELECT ''' + CONVERT(varchar, @Now, 113) + ''' AS CaptureDateTime, df.name AS FileName, CAST(SUM(DF.size * 8. / 1024) AS decimal(18, 2)) AS TotalSize FROM tempdb.sys.database_files AS DF GROUP BY df.name'; EXEC sp_executesql @SQL;
Perfectly balanced….like tempdb should be………..SNAP!
Now let’s examine our recently-purged plan cache:
SELECT t.text, C.usecounts FROM sys.dm_exec_cached_plans as c OUTER APPLY sys.dm_exec_sql_text(c.plan_handle) as t WHERE c.objtype = 'adhoc'
Our query is in there, as we’d expect, ready to be re-used:
Now, let’s re-run our dynamic SQL query, and examine the plan cache once again:
Oops. Rather than re-use the plan from last time, we’ve now got two plans, for what should be the same query! Upon inspection, the reason why this occurs is pretty obvious – the way the date/time variable is being used means that the queries have different text when they’re compiled!
This is an example of Cache Plan Bloat, and we want to avoid it if we can.
To fix it, we need to ensure that the format of the SQL text is the same on each execution. In our current example, that’s really easy:
DECLARE @SQL nvarchar(max); SET @SQL = ' USE tempdb SELECT CONVERT(varchar, GETDATE(), 113) AS CaptureDateTime, df.name AS FileName, CAST(SUM(DF.size * 8. / 1024) AS decimal(18, 2)) AS TotalSize FROM tempdb.sys.database_files AS DF GROUP BY df.name'; EXEC sp_executesql @SQL
The results of our query are the same, but crucially, the statement is now always the same too, which helps to ensure plan re-use on each execution. As we know, this means less compilations, faster executions, and a smaller memory footprint for our plan cache! Win Win Win!
To extend this slightly, let’s pretend we need to have the@Now variable declared outside of the dynamic SQL.
Well, then we simply parameterize the dynamic SQL statement, and pass @Now into it:
DECLARE @SQL nvarchar(max); DECLARE @Now datetime = GETDATE(); DECLARE @ParmDefinition nvarchar(500) = N'@Now datetime'; SET @SQL = ' USE tempdb SELECT @Now, df.name AS FileName, CAST(SUM(DF.size * 8. / 1024) AS decimal(18, 2)) AS TotalSize FROM tempdb.sys.database_files AS DF GROUP BY df.name'; EXEC sp_executesql @SQL, @ParmDefinition, @Now = @Now
Once again, we get plan reuse:
This is a basic example, but it illustrates the impact that subtle differences in a query can have. The same behaviour can be seen by introducing something as simple as an extra space in the query text!
Of course, you may purposefully seek to avoid plan re-use in some scenarios because different execution plans are desirable (e.g. to avoid parameter-sniffing issues!).
Thanks for reading!