[SQL Server] Afficher le contenu de SQL Monitor de SQL Server
/*
Liste process en cours (l'écran de SQL Monitor)
*/
WITH profiled_sessions as (
SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT Environnement =CASE @@SERVERNAME WHEN 'instanceSQL1' THEN 'DEV'
WHEN 'instanceSQL2' THEN 'PRD'
ELSE 'INCONNU' END,
'DBCC INPUTBUFFER (' + cast(s.session_id as varchar(25)) + ')',
T1.text AS Requete,CAST(T2.query_plan AS XML) AS query_plan,
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = CASE WHEN s.database_id=0 THEN N'' ELSE ISNULL(db_name(s.database_id),N'') END,
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * (8192 / 1024),
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0),
[Workload Group] = ISNULL(g.name, N''),
[Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
OUTER APPLY
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want.
SELECT TOP 1 *
FROM sys.dm_os_waiting_tasks wt
WHERE wt.waiting_task_address = t.task_address
ORDER BY wt.wait_duration_ms desc
) w
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) T1
CROSS APPLY sys.dm_exec_text_query_plan(r.plan_handle,r.statement_start_offset,r.statement_end_offset) T2
where ISNULL(t.task_state, N'')<>'' and s.session_id<>@@SPID
ORDER BY s.session_id;
/*
Active Expensive Queries (l'écran de SQL Monitor de SQL Server
Requêtes couteuses actives
*/
WITH profiled_sessions as (
SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT TOP 10 SUBSTRING(qt.TEXT, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2)+1) as [Query],
CAST(pl.query_plan AS XML) AS query_plan,
er.session_id as [Session Id],
er.cpu_time as [CPU (ms/sec)],
db.name as [Database Name],
er.total_elapsed_time as [Elapsed Time],
er.reads as [Reads],
er.writes as [Writes],
er.logical_reads as [Logical Reads],
er.row_count as [Row Count],
mg.granted_memory_kb as [Allocated Memory],
mg.used_memory_kb as [Used Memory],
mg.required_memory_kb as [Required Memory],
/* We must convert these to a hex string representation because they will be stored in a DataGridView, which can't handle binary cell values (assumes anything binary is an image) */
master.dbo.fn_varbintohexstr(er.plan_handle) AS [sample_plan_handle],
er.statement_start_offset as [sample_statement_start_offset],
er.statement_end_offset as [sample_statement_end_offset],
profiled_session_id as [Profiled Session Id]
FROM
sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
ON er.session_id = mg.session_id
LEFT OUTER JOIN profiled_sessions
ON profiled_session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt
CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle,er.statement_start_offset,er.statement_end_offset) pl,
sys.databases db
WHERE db.database_id = er.database_id
AND er.session_id <> @@spid