monitor sql server

SELECT mg.session_id
, db_name(ss.database_id) as DBName
, ss.login_name
, ss.host_name
, ss.program_name
, convert(varchar(max),st.[text]) as QueryText
, rp.[name] as ResourceGroup –Welke resource governor memory pool betreft dit
, ss.login_time as [SPID_login_time]
, rs.waiter_count –Aantal wachtende processen op een grant van een query memory resource
, mg.ideal_memory_kb /1024 as [ideal_memory_MB] –Size, in kilobytes, of the memory grant to fit everything into physical memory. This is based on the cardinality estimate.
, mg.requested_memory_kb /1024 as [requested_memory_MB] –Total requested amount of memory in kilobytes.
, mg.granted_memory_kb /1024 as [granted_memory_MB] –Total amount of memory actually granted in kilobytes. Can be NULL if the memory is not granted yet.
, mg.required_memory_kb /1024 as [required_memory_MB] –Minimum memory required to run this query in kilobytes. requested_memory_kb is the same or larger than this amount.
, mg.used_memory_kb /1024 as [used_memory_MB] –Physical memory used at this moment in kilobytes.
, mg.max_used_memory_kb /1024 as [max_used_memory_MB]–Maximum physical memory used up to this moment in kilobytes.
, mg.query_cost –Estimated query cost.
, mg.timeout_sec –Time-out in seconds before this query gives up the memory grant request.
, ss.cpu_time /1000 as [SPID_CpuSec]
, ss.reads as [SPID_reads]
, ss.writes as [SPID_writes]
, ss.logical_reads as [SPID_logical_reads]
–, (SELECT CEILING(physical_memory_kb/1024.0) FROM sys.dm_os_sys_info WITH (NOLOCK)) as [Physical_Os_Memory_MB]
–, (SELECT CEILING(available_physical_memory_kb/1024.0) FROM sys.dm_os_sys_memory WITH (NOLOCK)) as [Available_Os_Memory_MB]
–, (SELECT cast([value_in_use] as int) FROM [master].[sys].[configurations] WHERE Name = ‘Max Server Memory (MB)’) as [Max_SQL_Server_Memory_MB]
–, (SELECT cntr_value /1024 FROM sys.dm_os_performance_counters WHERE counter_name = ‘Total Server Memory (KB)’) as [Total_SQL_Server_Memory_MB]
–, (SELECT cntr_value/ 1024 FROM sys.dm_os_performance_counters WHERE counter_name = ‘Target Server Memory (KB)’) as [Target_SQL_Server_Memory_MB]
FROM sys.dm_exec_query_memory_grants mg with(nolock)
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) st
inner join sys.dm_exec_sessions ss with(nolock) on mg.session_id = ss.session_id
inner join sys.dm_resource_governor_resource_pools rp with(nolock) on mg.pool_id = rp.pool_id
inner join sys.dm_exec_query_resource_semaphores rs with(nolock) on mg.resource_semaphore_id = rs.resource_semaphore_id
and mg.pool_id = rs.pool_id
WHERE [mg].[session_id] <> @@SPID
and is_small = 0 –Hide trivial resource requests

Leave a Reply

Your email address will not be published. Required fields are marked *