Query to Identify SQL Server Bottlenecks

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)