{"id":1765,"date":"2014-02-20T00:00:00","date_gmt":"2014-02-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/fixing-cache-bloat-problems-with-guide-plans-and-forced-parameterization\/"},"modified":"2021-06-03T16:44:08","modified_gmt":"2021-06-03T16:44:08","slug":"fixing-cache-bloat-problems-with-guide-plans-and-forced-parameterization","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/fixing-cache-bloat-problems-with-guide-plans-and-forced-parameterization\/","title":{"rendered":"Fixing Cache Bloat Problems With Guide Plans and Forced Parameterization"},"content":{"rendered":"
\n

‘Cache bloat’ is a problem \u00a0that I’ve found \u00a0in the vast majority of those SQL Server instances that I have been asked to work on in order to tune their performance. Despite all the recommendations published on the web that warn people to be careful about this, it is very common to find that developers still run ad-hoc queries in such a way that they bloat the cache with lots of query plans. \u00a0It is frustrating to see because it is so easily avoided. Sometimes, however, you’re not in a position where you can change the code.<\/p>\n

Why is it important to avoid Cache Bloat? How can the DBA avoid the consequences of this problem without requiring the developer to change the application? \u00a0We’ll answer these questions in this article.<\/p>\n

So, what is cache bloat<\/h2>\n

‘Cache bloat’ is a term used to describe a growth in the size of \u00a0SQL Server’s procedure cache, using memory ‘stolen’ from the buffer cache. \u00a0There is a memory area that is used to store execution plans that are created by SQL Server. It is also used for a variety of other volatile storage such as locks, connections and cached data. SQL Server will take space from this shared buffer pool for the extra plans that need to be stored, and at a certain point this will start to restrict the memory available for other processes. This could cause a very high CPU usage without any blocking, slow performance, and finally errors in executing queries. \u00a0<\/p>\n

The procedure cache is used by the relational engine to avoid having to compile an execution plan every time a particular query is executed. Before a plan is created for a query, the cache is searched to see if there is already a plan for the same query. If so, that plan is reused.<\/p>\n

Sometimes this plan cache area of memory becomes filled with queries that the query optimizer doesn’t reckon to be the same, but which seem obviously the same to the programmer. \u00a0To reuse a plan, a query need to be equal to what’s in the cache: for instance, \u00a0if a whitespace or an upper\/lower letter is different it will not reuse the plan, and it will trigger a plan compilation. Although the algorithm for determining whether the queries are the same is very efficient, certain things, such as unqualified object references, will lead it to consider them different.<\/p>\n

The most obvious ‘surface’ difference between two queries that actually disguises the fact that they are the same is the ‘literal’ or value in a SQL query. \u00a0So the query..<\/p>\n

SELECT * FROM Sales.SalesOrderHeaderWHERE OrderDate ='12 Mar 2004'<\/pre>\n
… should really be considered the same as..<\/p>\n
SELECT * FROM Sales.SalesOrderHeaderWHERE OrderDate ='11 Apr 2004'<\/pre>\n
The literal date ’12 Mar 2004′ is different but the query plan should be the same. The Relational engine uses a technique called ‘auto-parameterization’ to turn the literal value into a parameter \u00a0so that subsequent calls of the same SQL with a different date literal will be matched, but it only does so if it judges that they should be handled by the same query plan.<\/p>\n
Normally, if you are using procedures, functions or parameterized queries\/batches, you’re providing a strong hint to the query engine about where the parameters are and whether it is safe to reuse a plan. Ad-hoc queries don’t provide many clues, and SQL Server likes to play safe and assume that each one requires a new plan. \u00a0Unless SQL Server determines that it can automatically parameterize a query, or it determines that it is the same query, it is forced to generate a new execution plan. Apart from wasting resources to compile the query (which consumes a lot of CPU), it will store each new plan in cache. If a server receives a large number of ad-hoc queries, it will take pages of memory from the buffer cache pool \u00a0to store them and this can lead to a more general memory stress.<\/p>\n
What’s the problem with ad-hoc queries?<\/h2>\n
Here is a sample of an ad-hoc query:<\/p>\n
\tDECLARE @Var VarChar(10), @SQL VarChar(200)\r\n\tSET @Var = CONVERT(VarChar(10), GETDATE() - (CheckSUM(NEWID()) \/ 1000000), 112)\r\n\t\r\n\tSET @SQL = 'SELECT * FROM OrdersBig WHERE OrderDate = ' + '''' + @Var + ''''\r\n\tEXEC (@SQL)\r\n\tGO\r\n<\/pre>\n
You’ll see that the user is mistakenly concatenating the user input to the SQL code ‘on the fly’ to create the query. If you are forced to use dynamic sql for some reason, sp_executeSQL can be used, with parameters , to allow plan reuse<\/a> and minimise \u00a0SQL Injection<\/a> risks. \u00a0Even better would be a stored procedure, whose execution plan only needs to be created once, if a plan for it is not found in the procedure cache. \u00a0Every time it is called, the relational engine reuses the same query plan.<\/p>\n
In order to show how Ad-hoc queries \u00a0can be a problem, I’ll use SQL Query Stress<\/a> to simulate many users running a query at the same time and let’s see what happens with CPU and memory. The query uses the SQL Server demo database AdventureWorks2012<\/a>, and is as follows:<\/p>\n
\tSELECT SalesOrderHeader.SalesPersonID,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(DISTINCT SalesOrderHeader.CustomerID), \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(SalesOrderDetail.OrderQty)\r\n\t FROM Sales.SalesOrderHeader\r\n\t\u00a0INNER JOIN Sales.SalesOrderDetail\r\n\t\u00a0\u00a0 ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID\r\n\t\u00a0INNER JOIN Production.Product\r\n\t\u00a0\u00a0 ON Product.ProductID = SalesOrderDetail.ProductID\r\n\t\u00a0WHERE Product.Name = 'C1DCB640-A394-4C33-95B0-D8EFF1DE6895'\r\n\t\u00a0GROUP BY SalesOrderHeader.SalesPersonID\r\n<\/pre>\n
The result and logic of the query don’t matter here, I only want to show a query that receives a parameter filter dynamically, to do this lets run it using EXEC and concatenating the filter on Product.Name. \u00a0Here is the dynamic query:<\/p>\n
\tDECLARE @Var VarChar(250), @SQL VarChar(MAX)\r\n\tSET @Var = NEWID()\r\n\t\r\n\tSET @SQL = \r\n\t'SELECT SalesOrderHeader.SalesPersonID,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(DISTINCT SalesOrderHeader.CustomerID), \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(SalesOrderDetail.OrderQty)\r\n\t FROM Sales.SalesOrderHeader\r\n\t\u00a0INNER JOIN Sales.SalesOrderDetail\r\n\t\u00a0\u00a0 ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID\r\n\t\u00a0INNER JOIN Production.Product\r\n\t\u00a0\u00a0 ON Product.ProductID = SalesOrderDetail.ProductID\r\n\t\u00a0WHERE Product.Name = ' + '''' + @Var + '''\r\n\t\u00a0GROUP BY SalesOrderHeader.SalesPersonID'\r\n\t\r\n\tEXEC (@SQL)\r\n<\/pre>\n
Every time the query above is executed, SQL Server will run a different query (because of the NEWID that creates a GUID), and consequently will compile a new plan for the query.<\/p>\n
If I run the code on SQLQueryStress<\/b>using 5 iterations on 200 threads looks what happens:<\/p>\n
\"1943-clip_image001-620x370.jpg\"<\/p>\n
Since SQL Server has to create an execution plan for each query, it is taking lot of time (more than 23 seconds) and resource (note CPU on 100%) to run the queries. In addition, if you look at the waits for the queries we can see that it is waiting “for the query to compile” (wait  resource_semaphore_query_compile<\/code>), following is the result of  sp_whoisactive<\/code> when the queries were running.<\/p>\n
\"1943-clip_image002-620x393.jpg\"<\/p>\n
The main points here are that you’re going to have to avoid<\/p>\n