Performance check on SQL Server

/***********Memory Grants Outstanding ***********/

SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Outstanding]                                                                                                     

FROM sys.dm_os_performance_counters WITH (NOLOCK)

WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances

AND counter_name = N'Memory Grants Outstanding' OPTION (RECOMPILE);

 

/***********Memory Grants Pending value for current instance ***********/

SELECT @@SERVERNAME AS [Server Name], [object_name], 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 a very strong indicator of memory pressure

/***********Find all queries waiting in the memory queue ***********/

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

/***********Find who uses the most query memory grant ***********/

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan

FROM sys.dm_exec_query_memory_grants AS mg

CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp

ORDER BY 1 DESC OPTION (MAXDOP 1)

/***********Search cache for queries with memory grants ***********/

SELECT t.text, cp.objtype,qp.query_plan

FROM sys.dm_exec_cached_plans AS cp

JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t

WHERE qp.query_plan.exist(‘declare namespace n=”http://schemas.microsoft.com/sqlserver/2004/07/showplan“; //n:MemoryFractions’) = 1

/***********The following query retrieves information from sys.dm_os_sys_memory***********/

SELECT total_physical_memory_kb,available_physical_memory_kb,

system_memory_state_desc

FROM sys.dm_os_sys_memory

/***********The following query is one we frequently run to identify memory usage for the queries running on our systems.  ***********/

SELECT session_id, request_id,requested_memory_kb,required_memory_kb,

used_memory_kb,ideal_memory_kb,sql_handle, plan_handle

FROM sys.dm_exec_query_memory_grants

/***********Page life expectancy (PLE)***********/

SELECT [object_name],

[counter_name]

,[cntr_value] AS PLE_SECS

,[cntr_value]/ 60 AS PLE_MINS

,[cntr_value]/ 3600 AS PLE_HOURS

,[cntr_value]/ 86400 AS PLE_DAYS

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Manager%'

AND [counter_name] = 'Page life expectancy'