Kunal RanpuraJul 30, 20191 minFind space used in SQL Server Buffer cache by ad-hoc queries-- 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...
Kunal RanpuraJul 30, 20191 minFind SQL Server Memory Clerk Usage-- Memory Clerk Usage for instance -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans) SELECT TOP(10) [type] AS [Memory...
Kunal RanpuraJul 30, 20191 minSQL Server Memory Grants Pending value-- Memory Grants Pending value for default instance SELECT cntr_value AS [Memory Grants Pending] FROM sys.dm_os_performance_counters WITH...
Kunal RanpuraJul 30, 20191 minFind SQL Server Memory Grant OutStanding-- Memory Grants Outstanding value for default instance SELECT cntr_value AS [Memory Grants Outstanding] FROM sys.dm_os_performance_count...
Kunal RanpuraJul 30, 20191 minFind SQL Server Page Life Expectancy-- Page Life Expectancy (PLE) value for default instance SELECT cntr_value AS [Page Life Expectancy] FROM sys.dm_os_performance_counters...
Kunal RanpuraJul 30, 20191 minCheck if your SQL Server is under Memory Pressure-- SQL Server Process Address space info --(shows whether locked pages is enabled, among other things) SELECT physical_memory_in_use_kb/1...
Kunal RanpuraJul 30, 20191 minFind SQL Server Memory total and Available including Page File Size-- Good basic information about memory amounts and state SELECT total_physical_memory_kb/1024000 Total_Physical_MM_GB,...