-- 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] = N'SQLServer:Memory Manager' -- Modify this if you have named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure
-- Find single-use, ad-hoc queries that are bloating the plan cache SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes/1024 Size_MB...
-- Memory Clerk Usage for instance -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans) SELECT TOP(10) [type] AS [Memory...
-- Memory Grants Outstanding value for default instance SELECT cntr_value AS [Memory Grants Outstanding] FROM sys.dm_os_performance_count...
Comments