|
Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
|
|
What Query Plans are in SQL Server's Memory?By Bill Graziano on 25 July 2007 | Tags: Memory Tuning SQL Server memory is primarily used to store data (buffer) and query plans (procedure cache). In this article I'll show how much memory is allocated to the procedure cache (RAM). I'll explain how to determine what plans are in the cache and how often they're used. SQL Server stores the procedure cache in 8KB data pages. You can use the dynamic management view sys.dm_os_memory_cache_counters to provide a summary of how the cache is allocated using this query: SELECT TOP 6 LEFT([name], 20) as [name], LEFT([type], 20) as [type], [single_pages_kb] + [multi_pages_kb] AS cache_kb, [entries_count] FROM sys.dm_os_memory_cache_counters order by single_pages_kb + multi_pages_kb DESC - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - name type cache_kb entries_count -------------------- -------------------- -------------------- -------------------- SQL Plans CACHESTORE_SQLCP 669880 9613 Object Plans CACHESTORE_OBJCP 14120 97 Bound Trees CACHESTORE_PHDR 3288 63 SchemaMgr Store USERSTORE_SCHEMAMGR 2544 0 mssqlsystemresource USERSTORE_DBMETADATA 2080 241 sqlteam USERSTORE_DBMETADATA 728 302 . . . . . On the server that hosts SQLTeam.com this query actually returns 75 rows. The server has 2GB of RAM with 1GB allocated to SQL Server 2005 Express Edition. This article will focus on the first three rows in this query. These are:
Notice that there are very few compiled plans for stored procedures (CACHESTORE_OBJCP). This should work out to about one plan per active stored procedure on the server. Also notice that there are lots and lots of plans for dynamic SQL on the server (CACHESTORE_SQLCP). The forum software on the site doesn't use stored procedures. The SQL statements it generates go into the SQL plan cache. You can monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter. There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). We can see individual cache entries by using the sys.dm_exec_cached_plans dynamic management view. SELECT usecounts, cacheobjtype, objtype, plan_handle
FROM sys.dm_exec_cached_plans
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
usecounts cacheobjtype objtype plan_handle
----------- ---------------- -------------------- --------------------------------------------------
23133 Compiled Plan Proc 0x05000600E969C702B861712D000000000000000000000000
6193 Compiled Plan Adhoc 0x060005009D7DB935B841F141000000000000000000000000
6192 Compiled Plan Trigger 0x05000500E9DB0F6BB8E13359000000000000000000000000
6192 Compiled Plan Proc 0x0500050047E57235B8C1560F000000000000000000000000
6097 Compiled Plan Adhoc 0x06000500992FE330B8C16E59000000000000000000000000
6082 Compiled Plan Adhoc 0x06000500EFE7B508B861CD0D000000000000000000000000
5818 Compiled Plan Trigger 0x050005003FB5C870B8614559000000000000000000000000
4864 Compiled Plan Prepared 0x06000500F264B12FB8412F2B000000000000000000000000
4850 Compiled Plan Prepared 0x0600050017A8CC01B8812B48000000000000000000000000
3385 Compiled Plan Proc 0x05000500099CB87DB801A449000000000000000000000000
884 Parse Tree View 0x0500FF7FC1010000C800130D000000000000000000000000
. . . . .
This query lists the most used query plans. In includes plans for stored procedures, adhoc or dynamic SQL, triggers, prepared SQL and views. If we want to see the SQL associates with these plans like need to use the sys.dm_exec_sql_text dynamic management function like this: select TOP 100 objtype, p.size_in_bytes, LEFT([sql].[text], 100) as [text] from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY usecounts DESC - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - objtype size_in_bytes text ------- ------------- ------------------------------------------------------------------------------------ Proc 172032 CREATE PROC [dbo].[subtext_GetSingleEntry] ( @ID int = NULL, @EntryName nvarchar(... Adhoc 24576 SELECT F_PRIVATEFORUMS, F_SUBJECT, F_PASSWORD_NEW FROM FORUM_FORUM WHERE FORUM... View 73728 CREATE VIEW [dbo].[sqlteam_Article_View] AS SELECT bbo.sqlteam_Article.ArticleID,... Proc 4251648 CREATE PROCEDURE [BMP_proc_RetrieveBannerFromZoneSite] (@ZoneID [int],@SiteID ... Proc 425984 CREATE PROC [dbo].[sqlteam_Article_Read_ByPageName] @PageName VARCHAR(200) AS ... I manually pulled a few rows from the result set to highlight the type of results this query generates. Notice that the compile query plans are BIG. One listed in over 4MB. There many of them that are roughly 400KB. If you do a little math on the first result set in this article you'll see the average SQL plan is just under 70KB and the average stored procedure plan is 145KB. Stored procedures tend to encapsulate more complex statements so that doesn't surprise me. This should give you enough information to go through your query plans and see what's being used, what's rarely used and how much space they take up.
|
- Advertisement - |