|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 |
/* 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 soon 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. In extreme cases, You may even receive errors such as Error: 701, Severity: 17, State: 1 There is insufficient system memory to run this query. ..or.. Msg 8645, Level 17, State 1, Procedure , Line 1 A time out occurred while waiting for memory resources to execute the query. Re-run the query. You 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. A 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. The 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. Query 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. The 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. DBCC 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. Once 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. Let's show you an example. First, we'll prepare a 'person' table and populate it with, say, half a million rows. */ IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name LIKE 'Person') CREATE TABLE [dbo].[Person]( [Person_id] [int] IDENTITY(1,1) NOT NULL, [ContactTitle] [varchar](50) NOT NULL CONSTRAINT [DF_Person_ContactTitle] DEFAULT (''), [Title] [varchar](20) NOT NULL CONSTRAINT [DF_Person_Title] DEFAULT (''), [FirrstName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_FirstName] DEFAULT (''), [NickName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_NickName] DEFAULT (''), [LastName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_creator] DEFAULT (USER_NAME()), [DateOfBirth] [datetime] NULL, [insertiondate] [datetime] NULL CONSTRAINT [DF_Person_insertiondate] DEFAULT (GETDATE()), [terminationdate] [datetime] NULL, CONSTRAINT [PK_dbo_Person] PRIMARY KEY CLUSTERED ( [Person_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] /*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 */ SET NOCOUNT ON --as we don't want it --Lets start with a clean sheet. CHECKPOINT --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. GO DBCC DROPCLEANBUFFERS --remove all buffers from the buffer pool. DBCC FREEPROCCACHE --Removes all elements from the plan cache GO --We'll create a temporary table to put our results into --drop the temporary table if it exists and create it IF EXISTS (SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#names%') DROP TABLE #names CREATE TABLE #names ([name] VARCHAR(80)) --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 DECLARE @ii INT --iteration counter DECLARE @id VARCHAR(10) SELECT @ii=1 WHILE @ii<2000 --and just look through 2000 rows. BEGIN SELECT @ii=@ii+1, @id=CONVERT(VARCHAR(5),@ii) EXECUTE ('insert into #names (name) Select Lastname from person where person_ID='+@id) END --Hmm. Lets see what is in the query-plan cache. Just peep at a sample of plans SELECT TOP 5 TEXT FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cacheobjtype ='Compiled Plan' /* look at that in the cache! Each execution has created a cached plan insert into #names (name) Select Lastname from person where person_ID=2000 insert into #names (name) Select Lastname from person where person_ID=1999 insert into #names (name) Select Lastname from person where person_ID=1998 insert into #names (name) Select Lastname from person where person_ID=1997 insert into #names (name) Select Lastname from person where person_ID=1996 How much memory did that lot take up, I wonder?*/ SELECT SUM(size_in_bytes)/1024/1024 AS "Megs" (SUM(size_in_bytes)/1024)/COUNT(*) AS [Average size(k)], AVG(refcounts) AS "average ref." AVG(Usecounts) AS "average use" ObjType FROM sys.dm_exec_cached_plans cp WHERE cacheobjtype ='Compiled Plan' GROUP BY Objtype /* Megs Average size(k) average ref. average use ObjType ------ --------------- ------------ ----------- -------- 78 40 2 1 Adhoc So that simple loop in procedural code within an application took 78 megs of memory! Now we've seen the damage, let's see if using a prameterised query is any better. We'll make up a little test harness. */ GO CHECKPOINT GO DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO SET NOCOUNT ON -- create a temporary table IF EXISTS (SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#names%') DROP TABLE #names CREATE TABLE #names ([name] VARCHAR(80)) --create a temporary timer table DECLARE @log TABLE ( Log_ID INT IDENTITY(1, 1), "Event"VARCHAR(40), "Stolen Pages"INT, "iterations"INT, InsertionDate DATETIME DEFAULT GETDATE() ) DECLARE @ii INT, @IterationsToDo INT DECLARE @id VARCHAR(8) DECLARE @StolenPageCounter INT --DBCC MemoryStatus --find out the stolen page counter value SELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO WHERE COUNTER_NAME='STOLEN PAGES' AND OBJECT_NAME LIKE '%BUFFER MANAGER%' SET @IterationsToDo=5000 INSERT INTO @log (event, "Stolen Pages"Iterations) SELECT 'Ad-hoc queries', @StolenPageCounter,@IterationsToDo SELECT @ii=1 WHILE @ii<=@IterationsToDo BEGIN SELECT @ii=@ii+1, @id=CONVERT(VARCHAR(5),@ii) EXECUTE ('insert into #names (name) Select Title + '' ''+Firstname+ '' ''+Lastname from person as philfactor where person_ID='+@id) END SELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO WHERE COUNTER_NAME='STOLEN PAGES' AND OBJECT_NAME LIKE '%BUFFER MANAGER%' INSERT INTO @log (event, "Stolen Pages" Iterations) SELECT 'Parameterised Queries', @StolenPageCounter,@IterationsToDo SELECT @ii=1 WHILE @ii<=@IterationsToDo BEGIN SELECT @ii=@ii+1 /* the first time that the sp_executesql statement is executed, SQL Server generates a parameterized plan for the SELECT statement from person with id as the parameter. For all subsequent executions, SQL Server reuses the plan with the new parameter value */ EXEC sp_executesql N' insert into #names (name) Select Title + '' ''+Firstname+ '' ''+Lastname from person as factorphil where person_ID=@id', N'@id int', @id=@ii END SELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO WHERE COUNTER_NAME='STOLEN PAGES' AND OBJECT_NAME LIKE '%BUFFER MANAGER%' INSERT INTO @log (event,"Stolen Pages" SELECT 'Completed parameterised query', @StolenPageCounter --first, we'll see what cached plans we have. SELECT COUNT(*) AS [No.], CONVERT(NUMERIC(9, 2), SUM(size_in_bytes * 1.00) / 1024 / 1024) AS Mb, (SUM(size_in_bytes) / 1024)/COUNT(*) AS [Average size (K)], AVG(refcounts) AS "average ref." AVG(Usecounts) AS "average use" ObjType FROM sys.dm_exec_cached_plans cp GROUP BY Objtype ORDER BY [Mb] DESC /* No. Mb Average size (K) average ref. average use ObjType ------ ------- ---------------- ------------ ----------- -------- 5003 195.82 40 2 1 Adhoc 8 0.26 33 1 4 View 1 0.04 40 2 5000 Prepared 1 0.01 8 1 5000 Proc */ SELECT event, [Pages Stolen] = (SELECT "Stolen Pages" FROM @log f WHERE f . Log_ID = g . Log_ID + 1)-"Stolen Pages" [Time (Ms)] = DATEDIFF(ms, [InsertionDate], (SELECT [InsertionDate] FROM @log f WHERE f . Log_ID = g . Log_ID + 1 ) ) FROM @log g WHERE Log_ID < ( SELECT MAX (Log_ID) FROM @log ) /* so we see that it is much faster to use parameterized queries and the number of stolen pages is far less event Pages Stolen Time (Ms) -------------------------- ------------ ----------- Ad-hoc queries 21193 3906 Parameterised Queries 4492 970 So now, lets pick out the two queries from the Procedure Cache, recognising them by the two strings I embedded into them */ SELECT 'parameterised query' AS [query type], SUM(size_in_bytes)/1024 AS [memory consumed (K)], COUNT(*) AS [number of plans] FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE TEXT LIKE '%factorphil%' UNION ALL SELECT 'Ad-Hoc query' AS [query type], SUM(size_in_bytes)/1024 AS [memory consumed (K)], COUNT(*) AS [number of plans] FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE TEXT LIKE '%philfactor%' /* query type memory consumed (K) number of plans ------------------- ------------------- --------------- parameterised query 3072 2 Ad-Hoc query 205728 5001 */ GO SELECT LEFT([type], 20) AS [type], SUM(single_pages_kb) AS [Total_kb] FROM sys.dm_os_memory_clerks WHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP') GROUP BY TYPE ORDER BY SUM(single_pages_kb) DESC /* we can see the huge figure again from the DMV sys.dm_os_memory_clerks type Total_kb -------------------- -------------------- CACHESTORE_SQLCP 202928 CACHESTORE_PHDR 312 CACHESTORE_OBJCP 8 but probably the best way of viewing the damage is from this DMV sys.dm_os_memory_cache_counters */ SELECT LEFT([name], 20) AS [name], LEFT([type], 20) AS [type], SUM([single_pages_kb] + [multi_pages_kb]) AS cache_kb, SUM([entries_count]) AS No_Entries FROM sys.dm_os_memory_cache_counters WHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP') GROUP BY [type], [name] ORDER BY cache_kb DESC /* name type cache_kb No_Entries ------------- -------------------- -------- ------- SQL Plans CACHESTORE_SQLCP 202768 5005 Bound Trees CACHESTORE_PHDR 312 10 Object Plans CACHESTORE_OBJCP 8 0 Note that: CACHESTORE_SQLCP are our ad-hoc cached SQL statements or batches that aren't in stored procedures, functions or triggers. Thes consist of dynamic ad-hoc SQL sent to the server by an application. CACHESTORE_PHDR are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names. CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers. So 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. */ |
Load comments