{"id":2870,"date":"2009-08-03T05:40:00","date_gmt":"2009-08-03T05:40:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/stolen-pages-ad-hoc-queries-and-the-sins-of-dynamic-sql-in-the-application\/"},"modified":"2018-02-08T15:27:45","modified_gmt":"2018-02-08T15:27:45","slug":"stolen-pages-ad-hoc-queries-and-the-sins-of-dynamic-sql-in-the-application","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/stolen-pages-ad-hoc-queries-and-the-sins-of-dynamic-sql-in-the-application\/","title":{"rendered":"Stolen Pages, Ad-hoc queries and the sins of dynamic SQL in the application."},"content":{"rendered":"<p>&nbsp;<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true \">\/*\r\nOne of the great advantages of doing development work on SQL Server machines with very modest hardware specifications is that mistakes are visible. The mistake of doing ad-hoc dynamic queries from an application will\u00a0\u00a0soon reveal itself. You might get a complaint from the developers that a database is running slowly. You'll notice a very high CPU usage, up to 100% but without any blocking during the periods of slow performance.\r\nIn extreme cases, You may even receive errors such as \r\n\r\nError: 701, Severity: 17, State: 1\r\nThere is insufficient system memory to run this query.\r\n\u00a0\u00a0..or..\r\nMsg 8645, Level 17, State 1, Procedure , Line 1\r\nA time out occurred while waiting for memory resources to execute the query. Re-run the query.\r\n\r\nYou may be seeing the effects of the running of too many ad-hoc queries. The high CPU can be due to the query optimiser compiling a large number of Ad-Hoc queries. The memory stress is due to the extra memory required to store these. In other words, some developer is spraying a large number of Ad-hoc queries at the database instead of using either stored procedures or prepared statements. the chances are, they're doing something 'suboptimal' or, as we used to call it, Daft.\r\n\r\nA compiled execution plan takes around 70KB depending on its size, and a stored procedure plan takes two to three times as much, depending on its complexity.\u00a0\u00a0The difference is that there will be only one plan for each stored procedure. With ad-hoc queries, you run the risk of having a separate plan for each query. We'll illustrate this in this article, and show you what to look out for.\r\n\r\nQuery plans have to be stored for re-use in the procedure cache, and SQL Server takes buffers from the LRU Buffer data store to do this. The word 'stolen' is a bit misleading as this is a perfectly legitimate exercise. Stolen pages are buffer cache pages that are 'stolen' to use for other server memory requests. Stolen pages are used for several miscellaneous server purposes such as procedure cache, sorting or for hashing operations (query workspace memory). It is also used as a generic memory store for allocations that are smaller than 8 KB, to store internal data structures such as locks, transaction context, and connection information. This is a simple way of allocating memory to routine tasks but if a server receives a huge rate of ad-hoc queries, it can lead to trouble. Unless 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. You are seeing the consequences of this with the starvation of memory for data buffers.\r\n\r\nThe first thing to check is the number of query plans being compiled. The SQL Server Performance Monitor will show many SQL Compilations\/sec are being done. Ideally, the ratio of SQL Recompilations\/sec to Batch Requests\/sec should be very low.\r\n\r\nDBCC MemoryStatus will indicate a rise in the number of stolen pages, and there are a host of DMVs that can point to the cause of the problems.\r\n\r\nOnce you are sure as to what is causing the problem, then there are several things you can do. Ideally, the best cure is to use stored procedures. If this solution isn't available for some reason, then develop parameterized queries in the applications so that SQL Server is enabled to reuse an existing plan. It is possible to force SQL Server to use a compiled plan for ad-hoc queries, but as it is so easy to form correct parameterised queries, this is hardly worth considering.\r\n\r\nLet's show you an example. First, we'll prepare a 'person' table and populate it with, say, half a million rows.\r\n*\/\r\nIF NOT EXISTS (SELECT 1 FROM information_schema.tables\r\n\u00a0\u00a0 WHERE table_name LIKE 'Person')\r\nCREATE TABLE [dbo].[Person](\r\n\u00a0\u00a0 [Person_id] [int] IDENTITY(1,1) NOT NULL,\r\n\u00a0\u00a0 [ContactTitle] [varchar](50) NOT NULL CONSTRAINT [DF_Person_ContactTitle]\u00a0\u00a0DEFAULT (''),\r\n\u00a0\u00a0 [Title] [varchar](20) NOT NULL CONSTRAINT [DF_Person_Title]\u00a0\u00a0DEFAULT (''),\r\n\u00a0\u00a0 [FirrstName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_FirstName]\u00a0\u00a0DEFAULT (''),\r\n\u00a0\u00a0 [NickName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_NickName]\u00a0\u00a0DEFAULT (''),\r\n\u00a0\u00a0 [LastName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_creator]\u00a0\u00a0DEFAULT (USER_NAME()),\r\n\u00a0\u00a0 [DateOfBirth] [datetime] NULL,\r\n\u00a0\u00a0 [insertiondate] [datetime] NULL CONSTRAINT [DF_Person_insertiondate]\u00a0\u00a0DEFAULT (GETDATE()),\r\n\u00a0\u00a0 [terminationdate] [datetime] NULL,\r\nCONSTRAINT [PK_dbo_Person] PRIMARY KEY CLUSTERED \r\n(\r\n\u00a0\u00a0 [Person_id] ASC\r\n)WITH (PAD_INDEX\u00a0\u00a0= OFF, STATISTICS_NORECOMPUTE\u00a0\u00a0= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS\u00a0\u00a0= ON, ALLOW_PAGE_LOCKS\u00a0\u00a0= ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\r\n\r\n\/*Now we are going to simulate a programmer failing to come to grips with how to get information from a table and trying to do it by iterating through the rows using Ad-Hoc queries\r\n*\/\r\nSET NOCOUNT ON --as we don't want it\r\n--Lets start with a clean sheet.\r\nCHECKPOINT --Writes all data pages that have been entered into the buffer cache and modified but not yet written to disk, for the current database to disk.\r\nGO\r\nDBCC DROPCLEANBUFFERS --remove all buffers from the buffer pool.\r\nDBCC FREEPROCCACHE --Removes all elements from the plan cache\r\nGO\r\n\r\n--We'll create a temporary table to put our results into\r\n\r\n--drop the temporary table if it exists and create it\r\nIF EXISTS (SELECT 1 FROM tempdb.information_schema.tables\r\n\u00a0\u00a0 WHERE table_name LIKE '#names%') DROP TABLE #names\r\n\r\nCREATE TABLE #names ([name] VARCHAR(80))\r\n\r\n--now let's simulate a simple loop through a table looking for a particular name in a \"Person\" table, assuming that a programmer is making the simple mistake of using procedural code\r\n\r\nDECLARE @ii INT --iteration counter\r\nDECLARE @id VARCHAR(10)\r\nSELECT @ii=1\r\nWHILE @ii&lt;2000 --and just look through 2000 rows.\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @ii=@ii+1, @id=CONVERT(VARCHAR(5),@ii)\r\n\r\n\u00a0\u00a0 EXECUTE ('insert into #names (name) Select Lastname from person where person_ID='+@id)\r\n\u00a0\u00a0 END\r\n--Hmm. Lets see what is in the query-plan cache. Just peep at a sample of plans\r\n\r\nSELECT TOP 5\u00a0\u00a0TEXT\r\nFROM sys.dm_exec_cached_plans \r\nCROSS APPLY sys.dm_exec_sql_text(plan_handle) \r\nWHERE cacheobjtype ='Compiled Plan'\r\n\r\n\/* look at that in the cache! Each execution has created a cached plan\r\ninsert into #names (name) Select Lastname from person where person_ID=2000\r\ninsert into #names (name) Select Lastname from person where person_ID=1999\r\ninsert into #names (name) Select Lastname from person where person_ID=1998\r\ninsert into #names (name) Select Lastname from person where person_ID=1997\r\ninsert into #names (name) Select Lastname from person where person_ID=1996\r\n\r\nHow much memory did that lot take up, I wonder?*\/\r\n\r\nSELECT SUM(size_in_bytes)\/1024\/1024 AS \"Megs\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SUM(size_in_bytes)\/1024)\/COUNT(*) AS [Average size(k)],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(refcounts) AS \"average ref.\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(Usecounts) AS \"average use\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ObjType\u00a0\u00a0\r\n\u00a0\u00a0FROM sys.dm_exec_cached_plans cp\r\n\u00a0\u00a0\u00a0\u00a0WHERE cacheobjtype ='Compiled Plan'\r\n\u00a0\u00a0\u00a0\u00a0GROUP BY Objtype\r\n\r\n\/*\r\nMegs\u00a0\u00a0 Average size(k) average ref. average use ObjType\r\n------ --------------- ------------ ----------- --------\r\n78\u00a0\u00a0\u00a0\u00a0 40\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Adhoc\r\n\r\nSo that simple loop in procedural code within an application took 78 megs of memory! \r\n\r\n\r\nNow we've seen the damage, let's see if using a prameterised query is any better. We'll make up a little test harness.\r\n*\/\r\nGO \r\nCHECKPOINT\r\nGO\r\nDBCC DROPCLEANBUFFERS\r\nDBCC FREEPROCCACHE\r\nGO\r\nSET NOCOUNT ON\r\n-- create a temporary table\r\nIF EXISTS (SELECT 1 FROM tempdb.information_schema.tables\r\n\u00a0\u00a0 WHERE table_name LIKE '#names%') DROP TABLE #names\r\n\r\nCREATE TABLE #names ([name] VARCHAR(80))\r\n\r\n--create a temporary timer table\r\nDECLARE\u00a0\u00a0@log TABLE\r\n\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0Log_ID INT IDENTITY(1, 1),\r\n\u00a0\u00a0\u00a0\u00a0\"Event\"VARCHAR(40),\r\n\u00a0\u00a0\u00a0\u00a0\"Stolen Pages\"INT,\r\n\u00a0\u00a0 \"iterations\"INT,\r\n\u00a0\u00a0\u00a0\u00a0InsertionDate DATETIME DEFAULT GETDATE()\r\n\u00a0\u00a0 )\r\nDECLARE @ii INT, @IterationsToDo INT\r\nDECLARE @id VARCHAR(8)\r\nDECLARE @StolenPageCounter INT\r\n\r\n--DBCC MemoryStatus\r\n--find out the stolen page counter value\r\nSELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO \r\n\u00a0\u00a0 WHERE COUNTER_NAME='STOLEN PAGES' \r\n\u00a0\u00a0 AND OBJECT_NAME LIKE '%BUFFER MANAGER%'\r\nSET @IterationsToDo=5000\r\n\r\nINSERT INTO @log (event, \"Stolen Pages\"Iterations) SELECT 'Ad-hoc queries', @StolenPageCounter,@IterationsToDo\r\nSELECT @ii=1\r\nWHILE @ii&lt;=@IterationsToDo\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @ii=@ii+1, @id=CONVERT(VARCHAR(5),@ii)\r\n\r\n\u00a0\u00a0 EXECUTE ('insert into #names (name) Select Title + '' ''+Firstname+ '' ''+Lastname from person as philfactor where person_ID='+@id)\r\n\u00a0\u00a0 END\r\n\r\nSELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO \r\n\u00a0\u00a0 WHERE COUNTER_NAME='STOLEN PAGES' \r\n\u00a0\u00a0 AND OBJECT_NAME LIKE '%BUFFER MANAGER%'\r\n\r\nINSERT INTO @log (event, \"Stolen Pages\" Iterations) SELECT 'Parameterised Queries', @StolenPageCounter,@IterationsToDo\r\n\r\nSELECT @ii=1\r\nWHILE @ii&lt;=@IterationsToDo\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @ii=@ii+1\r\n\u00a0\u00a0 \/* the first time that the sp_executesql statement is executed, \r\n\u00a0\u00a0 SQL Server generates a parameterized plan for the SELECT statement\r\n\u00a0\u00a0 from person with id as the parameter. For all subsequent executions, \r\n\u00a0\u00a0 SQL Server reuses the plan with the new parameter value *\/\r\n\u00a0\u00a0 EXEC sp_executesql N'\r\ninsert into #names (name) Select Title + '' ''+Firstname+ '' ''+Lastname \r\nfrom person as factorphil\r\nwhere person_ID=@id',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 N'@id int',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @id=@ii\r\n\u00a0\u00a0 END\r\nSELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO \r\n\u00a0\u00a0 WHERE COUNTER_NAME='STOLEN PAGES' \r\n\u00a0\u00a0 AND OBJECT_NAME LIKE '%BUFFER MANAGER%'\r\nINSERT INTO @log (event,\"Stolen Pages\" SELECT 'Completed parameterised query', @StolenPageCounter\r\n\r\n--first, we'll see what cached plans we have.\r\n\r\nSELECT\u00a0\u00a0COUNT(*) AS [No.], \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONVERT(NUMERIC(9, 2), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(size_in_bytes * 1.00) \/ 1024 \/ 1024) AS Mb, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SUM(size_in_bytes) \/ 1024)\/COUNT(*) AS [Average size (K)],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(refcounts) AS \"average ref.\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AVG(Usecounts) AS \"average use\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ObjType\r\nFROM\u00a0\u00a0\u00a0\u00a0 sys.dm_exec_cached_plans cp\r\nGROUP BY Objtype\r\nORDER BY [Mb] DESC\r\n\/*\r\nNo.\u00a0\u00a0\u00a0\u00a0 Mb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Average size (K) average ref. average use ObjType\r\n------ ------- ---------------- ------------ ----------- --------\r\n5003\u00a0\u00a0 195.82\u00a0\u00a040\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Adhoc\r\n8\u00a0\u00a0\u00a0\u00a0\u00a0 0.26\u00a0\u00a0\u00a0\u00a033\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 View\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0 0.04\u00a0\u00a0\u00a0\u00a040\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Prepared\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0 0.01\u00a0\u00a0\u00a0\u00a08\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Proc\r\n\r\n*\/\r\n\r\nSELECT\u00a0\u00a0 event, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Pages Stolen] = (SELECT \"Stolen Pages\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @log f \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE f . Log_ID = g . Log_ID + 1)-\"Stolen Pages\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Time (Ms)] = DATEDIFF(ms, [InsertionDate], \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT [InsertionDate] \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @log f \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE f . Log_ID = g . Log_ID + 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\nFROM\u00a0\u00a0\u00a0\u00a0 @log g\r\nWHERE\u00a0\u00a0\u00a0\u00a0Log_ID &lt; ( SELECT MAX (Log_ID) FROM @log )\r\n\/*\r\nso we see that it is much faster to use parameterized queries and the number of stolen pages is far less\r\nevent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Pages Stolen Time (Ms)\r\n-------------------------- ------------ -----------\r\nAd-hoc queries\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 21193\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03906\r\nParameterised Queries\u00a0\u00a0\u00a0\u00a0\u00a0 4492\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 970\r\n\r\n\r\nSo now, lets pick out the two queries from the Procedure Cache, recognising them by the two strings I embedded into them\r\n*\/\r\n\r\nSELECT\u00a0\u00a0'parameterised query' AS [query type],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(size_in_bytes)\/1024 AS [memory consumed (K)],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) AS [number of plans]\r\nFROM sys.dm_exec_cached_plans \r\nCROSS APPLY sys.dm_exec_sql_text(plan_handle) \r\nWHERE TEXT LIKE '%factorphil%'\r\nUNION ALL\r\nSELECT\u00a0\u00a0'Ad-Hoc query' AS [query type],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(size_in_bytes)\/1024 AS [memory consumed (K)],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) AS [number of plans]\r\nFROM sys.dm_exec_cached_plans \r\nCROSS APPLY sys.dm_exec_sql_text(plan_handle) \r\nWHERE TEXT LIKE '%philfactor%'\r\n\/*\r\n\r\nquery type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0memory consumed (K) number of plans\r\n------------------- ------------------- ---------------\r\nparameterised query 3072\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\r\nAd-Hoc query\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0205728\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05001\r\n\r\n*\/\r\n\r\nGO\r\n\r\nSELECT LEFT([type], 20) AS [type], SUM(single_pages_kb) AS [Total_kb]\r\nFROM sys.dm_os_memory_clerks\r\nWHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP')\r\nGROUP BY TYPE\r\nORDER BY SUM(single_pages_kb) DESC\r\n\r\n\/*\r\nwe can see the huge figure again from the DMV sys.dm_os_memory_clerks\r\ntype\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Total_kb\r\n-------------------- --------------------\r\nCACHESTORE_SQLCP\u00a0\u00a0\u00a0\u00a0 202928\r\nCACHESTORE_PHDR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0312\r\nCACHESTORE_OBJCP\u00a0\u00a0\u00a0\u00a0 8\r\n\r\n\r\nbut probably the best way of viewing the damage is from this DMV sys.dm_os_memory_cache_counters \r\n*\/\r\nSELECT\u00a0\u00a0\r\n\u00a0\u00a0 LEFT([name], 20) AS [name],\r\n\u00a0\u00a0 LEFT([type], 20) AS [type],\r\n\u00a0\u00a0 SUM([single_pages_kb] + [multi_pages_kb]) AS cache_kb,\r\n\u00a0\u00a0 SUM([entries_count]) AS No_Entries\r\nFROM sys.dm_os_memory_cache_counters \r\nWHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP')\r\nGROUP BY [type], [name]\r\nORDER BY cache_kb DESC\r\n\/*\r\nname\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cache_kb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 No_Entries\r\n------------- -------------------- -------- -------\r\nSQL Plans\u00a0\u00a0\u00a0\u00a0 CACHESTORE_SQLCP\u00a0\u00a0\u00a0\u00a0 202768\u00a0\u00a0 5005\r\nBound Trees\u00a0\u00a0 CACHESTORE_PHDR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0312\u00a0\u00a0\u00a0\u00a0\u00a0 10\r\nObject Plans\u00a0 CACHESTORE_OBJCP\u00a0\u00a0\u00a0\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n\r\nNote that: \r\nCACHESTORE_SQLCP are our ad-hoc cached SQL statements or batches that aren't in stored procedures, functions or triggers.\u00a0\u00a0Thes consist of dynamic ad-hoc SQL\u00a0\u00a0sent to the server by an application. \r\nCACHESTORE_PHDR are algebrizer trees for views, constraints and defaults.\u00a0\u00a0An algebrizer tree is the parsed SQL text that resolves the table and column names.\r\nCACHESTORE_OBJCP\u00a0\u00a0are compiled plans for stored procedures, functions and triggers.\r\n\r\n\r\nSo there you have it. This is a problem that can be very insidious, but once you know what to watch out for, it is very easy to put right. You just make sure that the developers do not try using too many ad hoc queries.\r\n*\/<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; \/* One of the great advantages of doing development work on SQL Server machines with very modest hardware specifications is that mistakes are visible. The mistake of doing ad-hoc dynamic queries from an application will\u00a0\u00a0soon reveal itself. You might get a complaint from the developers that a database is running slowly. You&#8217;ll notice a&#8230;&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2870","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2870","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2870"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2870\/revisions"}],"predecessor-version":[{"id":77185,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2870\/revisions\/77185"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2870"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2870"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2870"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2870"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}