For Day 18 of this series, we start out with Query #41, which is Memory Clerk Usage. This query retrieves information from the sys.dm_os_memory_clerks dynamic management view about total memory usage by your active memory clerks. Query #41 is shown in Figure 1.
1: -- Memory Clerk Usage for instance (Query 41) (Memory Clerk Usage)
2: -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
3: SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
4: CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
5: FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
6: GROUP BY mc.[type]
7: ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
8:
9: -- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory
10:
11: -- CACHESTORE_SQLCP SQL Plans
12: -- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
13: -- Watch out for high values for CACHESTORE_SQLCP
14:
15: -- CACHESTORE_OBJCP Object Plans
16: -- These are compiled plans for stored procedures, functions and triggers
Figure 1: Query #41 PLE by NUMA Node
This query shows you which memory clerks are using the most memory on your instance. With SQL Server 2012 or newer, your top memory clerk by memory usage should be MEMORYCLERK_SQLBUFFERPOOL, meaning memory usage by the SQL Server Buffer Pool. It is very common to see a high value for the CACHESTORE_SQLCP memory clerk, indicating that you have multiple GB of cached ad hoc or prepared query plans in the plan cache. If you see that, then you should look at the next query more closely, for several things you can do to help mitigate this issue.
Query #42 is Ad hoc Queries. This query retrieves information from the sys.dm_exec_cached_plans dynamic management view and the sys.dm_exec_sql_text dynamic management function about the single-use ad hoc and prepared query plans. Query #42 is shown in Figure 2.
1: -- Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 42) (Ad hoc Queries)
2: SELECT TOP(50) [text][/text] AS [QueryText], cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS [Plan Size in KB]
3: FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
4: CROSS APPLY sys.dm_exec_sql_text(plan_handle)
5: WHERE cp.cacheobjtype = N'Compiled Plan'
6: AND cp.objtype IN (N'Adhoc', N'Prepared')
7: AND cp.usecounts = 1
8: ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
9:
10: -- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache
11: -- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)
12: -- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
13: -- Enabling forced parameterization for the database can help, but test first!
14:
15: -- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
16: -- https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
Figure 2: Query #42 Ad hoc Queries
This query will show you which single-use ad hoc or prepared query plans are using the most space in the plan cache. Once you know who the culprits are, you can start investigating them more closely. Perhaps these queries can be converted to stored procedures or parameterized SQL. At the very least, I think you should enable “optimize for ad hoc workloads” at the instance level pretty much as a default setting. On top of this, it is usually a good idea to periodically flush that particular cache, using the DBCC FREESYSTEMCACHE (‘SQL Plans’); command.
Query #43 is Top Logical Reads Queries. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function and the sys.dm_exec_query_plan dynamic management function about the cached query plans that have the highest total logical reads. Query #43 is shown in Figure 3.
1: -- Get top total logical reads queries for entire instance (Query 43) (Top Logical Reads Queries)
2: SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text][/text], 50) AS [Short Query Text],
3: qs.total_logical_reads AS [Total Logical Reads],
4: qs.min_logical_reads AS [Min Logical Reads],
5: qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
6: qs.max_logical_reads AS [Max Logical Reads],
7: qs.min_worker_time AS [Min Worker Time],
8: qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
9: qs.max_worker_time AS [Max Worker Time],
10: qs.min_elapsed_time AS [Min Elapsed Time],
11: qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
12: qs.max_elapsed_time AS [Max Elapsed Time],
13: qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
14: --,t.[text][/text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
15: FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
16: CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
17: CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
18: ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
19:
20:
21: -- Helps you find the most expensive queries from a memory perspective across the entire instance
22: -- Can also help track down parameter sniffing issues
Figure 3: Query #40 Top Logical Reads Queries
Having logical reads means that you are finding the data you need to satisfy a query in the SQL Server Buffer Pool rather than having to go out to the storage subsystem, which is a good thing. Queries that have high numbers of logical reads are creating extra internal memory pressure on your system. They also indirectly create read I/O pressure, since the data that is in the buffer pool has to be initially read from the storage subsystem. If you are seeing signs of memory pressure, then knowing which cached queries (across the entire instance) that have the highest number of total logical reads can help you understand which queries are causing the most memory pressure.
Once you understand this, then you can start looking at individual queries in more detail. Perhaps there is a missing index that is causing a clustered index scan that is causing high numbers of logical reads in a query. Perhaps there is an implicit conversion in a JOIN or in a WHERE clause that is causing SQL Server to ignore a useful index. Maybe someone is pulling back more columns than they need for a query. There are lots of possibilities here.
These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results: