Common SQL server performance analysis statements

-- View deadlocks

Selectdistinct
'Process id' = STR (A. spid, 4)
, 'Process ID status' = convert (char (10), a. Status)
, 'Deadlock process id' = STR (A. Blocked, 2)
, 'Workstation name' = convert (char (10), a. hostname)
, 'COMMAND execution user' = convert (char (10), suser_name (A. UID ))
, 'Database name' = convert (char (10), db_name (A. dbid ))
, 'Application name' = convert (char (10), a. program_name)
, 'COMMAND being executed '= convert (char (16), a. cmd)
, 'Login name' = A. loginame
, 'Statement executed '= B. Text
From master .. sysprocesses a cross apply
SYS. dm_exec_ SQL _text (A. SQL _handle) B
Where a. Blocked in (select blocked
From master .. sysprocesses)
-- And blocked <> 0
Orderbystr (spid, 4)

-- Check the connection information (spid: 57, 58)
Select connect_time, last_read, last_write, most_recent_ SQL _handle
From SYS. dm_exec_connections where session_id in (57,58)
-- View session information
Select login_time, host_name, program_name, login_name, last_request_start_time, last_request_end_time
From SYS. dm_exec_sessions where session_id in (57,58)
-- View blocking ongoing requests
Select
Session_id, blocking_session_id, wait_type, wait_time, wait_resource
From
SYS. dm_exec_requests
Where
Blocking_session_id> 0 -- ID of the session in which the request is being blocked. If this column is null, the request will not be blocked
/*
Session_id, blocking_session_id, wait_type, wait_time, wait_resource
58 57 lck_m_s 2116437 key: 6: 72057594039435264 (020068e8b274)
*/
-- View the SQL statement being executed
Select
A. session_id, SQL. Text, A. most_recent_ SQL _handle
From
SYS. dm_exec_connections
Cross apply
SYS. dm_exec_ SQL _text (A. most_recent_ SQL _handle) as SQL -- the execution statement can also be obtained through the fn_get_ SQL function most_recent_ SQL _handle.
Where
A. session_id in (57,58)
-- Query lock type

Select process id = A. req_spid
, Database = db_name (rsc_dbid)
, Type = case rsc_type when1then 'null Resource (not used )'
When2then 'database'
When3then 'file'
When4then 'index'
When5then 'table'
When6then 'page'
When7then 'key'
When8then 'Extended disk region'
When9then 'rid (row ID )'
When10then 'application'
End
, Object ID = rsc_objid
, Object name = B. obj_name
, Rsc_indid
From master .. syslockinfo A leftjoin # t B on A. req_spid = B. req_spid

 

---- View the SQL statement executed by the SA user
Select 'process ID [spid] '= STR (A. spid, 4)
, 'Process status' = convert (char (10), a. Status)
, 'Multipart process id' = STR (A. Blocked, 2)
, 'Server name' = convert (char (10), a. hostname)
, 'Execution user' = convert (char (10), suser_name (A. UID ))
, 'Database name' = convert (char (10), db_name (A. dbid ))
, 'Application name' = convert (char (10), a. program_name)
, 'COMMAND being executed '= convert (char (16), a. cmd)
, 'Accumulative CPU time' = STR (A. CPU, 7)
, 'Io' = STR (A. physical_io, 7)
, 'Login name' = A. loginame
, 'Execute SQL '= B. Text
From master .. sysprocesses a cross apply
SYS. dm_exec_ SQL _text (A. SQL _handle) B
Where blocked <> 0or A. loginame = 'sa'
Orderby spid

Main dynamic management views:

SYS. sysprocesses (compatible with sql2k)

SYS. dm_exec_connections

SYS. dm_exec_sessions

SYS. dm_exec_requests

 

Source: http://www.cnblogs.com/ilovexiao/archive/2010/05/21/1740645.html