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.
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.
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.
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: