For Day 18 of this series, we start out with Query #41<\/strong>, which is Memory Clerk Usage. This query retrieves information from the sys.dm_os_memory_clerks<\/a> dynamic management view about total memory usage by your active memory clerks. Query #41 is shown in Figure 1.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/div>\n<\/div>\n Figure 1: Query #41 PLE by NUMA Node<\/strong><\/p>\n 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.<\/p>\n <\/p>\n Query #42<\/strong> is Ad hoc Queries. This query retrieves information from the sys.dm_exec_cached_plans<\/a> dynamic management view and the sys.dm_exec_sql_text<\/a> dynamic management function about the single-use ad hoc and prepared query plans. Query #42 is shown in Figure 2.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/div>\n<\/div>\n Figure 2: Query #42 Ad hoc Queries<\/strong><\/p>\n 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 \u201coptimize for ad hoc workloads\u201d 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.<\/p>\n <\/p>\n Query #43<\/strong> is Top Logical Reads Queries. This query retrieves information from the sys.dm_exec_query_stats<\/a> dynamic management view, the sys.dm_exec_sql_text<\/a> dynamic management function and the sys.dm_exec_query_plan<\/a> dynamic management function about the cached query plans that have the highest total logical reads. Query #43 is shown in Figure 3.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/div>\n<\/div>\n Figure 3: Query #40 Top Logical Reads Queries<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results: <\/p>\n SQL Server 2014 DMV Diagnostic Queries \u2013 Part 1<\/a><\/p>\n<\/blockquote>\n SQL Server 2014 DMV Diagnostic Queries \u2013 Part 2<\/a><\/p>\n<\/blockquote>\n 1:<\/span> -- Memory Clerk Usage for instance (Query 41) (Memory Clerk Usage)<\/span><\/pre>\n 2:<\/span> -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)<\/span><\/pre>\n 3:<\/span> SELECT<\/span> TOP<\/span>(10) mc.[type] AS<\/span> [Memory Clerk Type], <\/pre>\n 4:<\/span> CAST<\/span>((SUM<\/span>(mc.pages_kb)\/1024.0) AS<\/span> DECIMAL<\/span> (15,2)) AS<\/span> [Memory Usage<\/span> (MB)] <\/pre>\n 5:<\/span> FROM<\/span> sys.dm_os_memory_clerks AS<\/span> mc WITH<\/span> (NOLOCK)<\/pre>\n 6:<\/span> GROUP<\/span> BY<\/span> mc.[type] <\/pre>\n 7:<\/span> ORDER<\/span> BY<\/span> SUM<\/span>(mc.pages_kb) DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n 8:<\/span> <\/pre>\n 9:<\/span> -- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory<\/span><\/pre>\n 10:<\/span> <\/pre>\n 11:<\/span> -- CACHESTORE_SQLCP SQL Plans <\/span><\/pre>\n 12:<\/span> -- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers<\/span><\/pre>\n 13:<\/span> -- Watch out for high values for CACHESTORE_SQLCP<\/span><\/pre>\n 14:<\/span> <\/pre>\n 15:<\/span> -- CACHESTORE_OBJCP Object Plans <\/span><\/pre>\n 16:<\/span> -- These are<\/span> compiled plans for<\/span> stored procedures, functions and<\/span> triggers<\/pre>\n 1:<\/span> -- Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 42) (Ad hoc Queries)<\/span><\/pre>\n 2:<\/span> SELECT<\/span> TOP<\/span>(50) [text][\/text] AS<\/span> [QueryText], cp.cacheobjtype, cp.objtype, cp.size_in_bytes\/1024 AS<\/span> [Plan<\/span> Size<\/span> in<\/span> KB]<\/pre>\n 3:<\/span> FROM<\/span> sys.dm_exec_cached_plans AS<\/span> cp WITH<\/span> (NOLOCK)<\/pre>\n 4:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(plan_handle) <\/pre>\n 5:<\/span> WHERE<\/span> cp.cacheobjtype = N'Compiled Plan'<\/span> <\/pre>\n 6:<\/span> AND<\/span> cp.objtype IN<\/span> (N'Adhoc'<\/span>, N'Prepared'<\/span>) <\/pre>\n 7:<\/span> AND<\/span> cp.usecounts = 1<\/pre>\n 8:<\/span> ORDER<\/span> BY<\/span> cp.size_in_bytes DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n 9:<\/span> <\/pre>\n 10:<\/span> -- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache<\/span><\/pre>\n 11:<\/span> -- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)<\/span><\/pre>\n 12:<\/span> -- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this<\/span><\/pre>\n 13:<\/span> -- Enabling forced parameterization for the database can help, but test first!<\/span><\/pre>\n 14:<\/span> <\/pre>\n 15:<\/span> -- Plan cache, adhoc workloads and clearing the single-use plan cache bloat<\/span><\/pre>\n 16:<\/span> -- https:\/\/www.sqlskills.com\/blogs\/kimberly\/plan<\/span>-cache-adhoc-workloads-and<\/span>-clearing-the-single-use<\/span>-plan<\/span>-cache-bloat\/<\/pre>\n 1:<\/span> -- Get top total logical reads queries for entire instance (Query 43) (Top Logical Reads Queries)<\/span><\/pre>\n 2:<\/span> SELECT<\/span> TOP<\/span>(50) DB_NAME(t.[dbid]) AS<\/span> [Database<\/span> Name], LEFT<\/span>(t.[text][\/text], 50) AS<\/span> [Short Query Text],<\/pre>\n 3:<\/span> qs.total_logical_reads AS<\/span> [Total Logical Reads<\/span>],<\/pre>\n 4:<\/span> qs.min_logical_reads AS<\/span> [Min<\/span> Logical Reads<\/span>],<\/pre>\n 5:<\/span> qs.total_logical_reads\/qs.execution_count AS<\/span> [Avg<\/span> Logical Reads<\/span>],<\/pre>\n 6:<\/span> qs.max_logical_reads AS<\/span> [Max<\/span> Logical Reads<\/span>], <\/pre>\n 7:<\/span> qs.min_worker_time AS<\/span> [Min<\/span> Worker Time<\/span>],<\/pre>\n 8:<\/span> qs.total_worker_time\/qs.execution_count AS<\/span> [Avg<\/span> Worker Time<\/span>], <\/pre>\n 9:<\/span> qs.max_worker_time AS<\/span> [Max<\/span> Worker Time<\/span>], <\/pre>\n 10:<\/span> qs.min_elapsed_time AS<\/span> [Min<\/span> Elapsed Time<\/span>], <\/pre>\n 11:<\/span> qs.total_elapsed_time\/qs.execution_count AS<\/span> [Avg<\/span> Elapsed Time<\/span>], <\/pre>\n 12:<\/span> qs.max_elapsed_time AS<\/span> [Max<\/span> Elapsed Time<\/span>],<\/pre>\n 13:<\/span> qs.execution_count AS<\/span> [Execution Count<\/span>], qs.creation_time AS<\/span> [Creation Time<\/span>]<\/pre>\n 14:<\/span> --,t.[text][\/text] AS<\/span> [Complete Query Text], qp.query_plan AS<\/span> [Query Plan<\/span>] -- uncomment out these columns if not copying results to Excel<\/span><\/pre>\n 15:<\/span> FROM<\/span> sys.dm_exec_query_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n 16:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(plan_handle) AS<\/span> t <\/pre>\n 17:<\/span> CROSS<\/span> APPLY sys.dm_exec_query_plan(plan_handle) AS<\/span> qp <\/pre>\n 18:<\/span> ORDER<\/span> BY<\/span> qs.total_logical_reads DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n 19:<\/span> <\/pre>\n 20:<\/span> <\/pre>\n 21:<\/span> -- Helps you find the most expensive queries from a memory perspective across the entire instance<\/span><\/pre>\n 22:<\/span> -- Can also help track down parameter<\/span> sniffing issues<\/pre>\n\n
\n
\n