Databases Reference
In-Depth Information
LISTING 15-29:
Memory Grants Pending information
-- Memory Grants Pending value for default instance
SELECT cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
-- Memory Grants Pending above zero
-- for a sustained period is an extremely strong indicator of memory pressure
This query returns the current value for Memory Grants Pending for the default instance of SQL
Server. Memory Grants Pending is the total number of processes within SQL Server that are waiting
for a workspace memory grant. You want this value to be zero if at all possible. Any sustained value
above zero is an extremely strong indicator of memory pressure. Especially if you see any signs of
internal memory pressure from the previous three queries, take a closer look at the overall memory
usage in SQL Server by running the query shown in Listing 15-30.
LISTING 15-30:
Memory clerk information
-- Memory Clerk Usage for instance
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP(10) [type] AS [Memory Clerk Type],
SUM(pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);
-- CACHESTORE_SQLCP SQL Plans
-- These are cached SQL statements or batches that
-- aren't in stored procedures, functions and triggers
--
-- CACHESTORE_OBJCP Object Plans
-- These are compiled plans for
-- stored procedures, functions and triggers
--
-- CACHESTORE_PHDR Algebrizer Trees
-- An algebrizer tree is the parsed SQL text
-- that resolves the table and column names
This query gives you a good idea of what (besides the buffer cache) is using large amounts of
memory in SQL Server. One key item to look out for is high values for
CACHESTORE_SQLCP
, which is
the memory clerk for ad hoc query plans. It is quite common to see this memory clerk using several
gigabytes of memory to cache ad hoc query plans.
If you see a lot of memory being used by the
CACHESTORE_SQLCP
memory clerk, you can determine
whether you have many single-use, ad hoc query plans using a lot of memory in your procedure
cache by running the query shown in Listing 15-31.
Search WWH ::
Custom Search