1).
select * from sys.sysprocesses where status = 'suspended' and spid >10 and hostname <> 'MonitoringServer' order by blocked desc
go
SELECT session_id, plan_handle FROM sys.dm_exec_requests where status ='suspended' or status = 'runnable' and session_id >10
go
select * from sys.dm_os_wait_stats where wait_type not in
(select wait_type
from sys.dm_os_waiting_tasks wt
join sys.dm_exec_sessions s on s.session_id = wt.session_id
where is_user_process = 0)
order by wait_time_ms desc
go
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName <> 'master' and Status = 'runnable' and HostName <> 'MonitoringServer'
-- Add any sorting of the results here :
ORDER BY CPUTime desc
DROP TABLE #sp_who2
go
1a). The below query will provide query execution planes from plane_handle (You should be able to see missing indexes in the execution plane.
SELECT query_plan FROM sys.dm_exec_query_plan(sql_handle)
1b). The Following query will provide query text from plane_handle
SELECT * FROM sys.dm_exec_sql_text(sql_handle)
תגובות