查询SQL阻塞语句

SELECT SPID=p.spid,
       DBName = convert(CHAR(20),d.name),
       ProgramName = program_name,
       LoginName = convert(CHAR(20),l.name),
       HostName = convert(CHAR(20),hostname),
       Status = p.status,
       BlockedBy = p.blocked,
       LoginTime = login_time,
       QUERY = CAST(TEXT AS VARCHAR(MAX))
FROM   MASTER.dbo.sysprocesses p
       INNER JOIN MASTER.dbo.sysdatabases d
         ON p.dbid = d.dbid
       INNER JOIN MASTER.dbo.syslogins l
         ON p.sid = l.sid
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE  p.blocked = 0
       AND EXISTS (SELECT 1
                   FROM   MASTER..sysprocesses p1
                   WHERE  p1.blocked = p.spid)
                   
                   
exec sp_who_lock

CREATE PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN

DECLARE @spid INT ,
@bl INT ,
@intTransactionCountOnEntry INT ,
@intRowcount INT ,
@intCountProperties INT ,
@intCounter INT,
@sql_handle VARBINARY(64)

DECLARE @tmp_lock_who TABLE
(
id INT IDENTITY(1, 1) ,
spid SMALLINT ,
bl SMALLINT,
sql_handle VARBINARY(64)
)

IF @@ERROR <> 0
RETURN @@ERROR
;
WITH tb_blocked AS(
SELECT spid, blocked, sql_handle FROM master..sysprocesses WHERE blocked > 0
)
INSERT INTO @tmp_lock_who
( spid ,
bl, sql_handle
)
SELECT DISTINCT blocked,0, p_bl.sql_handle
FROM tb_blocked
CROSS APPLY (SELECT p_bl.sql_handle FROM master..sysprocesses p_bl WHERE p_bl.spid = tb_blocked.blocked) p_bl
WHERE NOT EXISTS ( SELECT *
FROM tb_blocked a
WHERE tb_blocked.blocked = a.spid )
UNION ALL
SELECT spid, blocked, sql_handle FROM tb_blocked

IF @@ERROR <> 0
RETURN @@ERROR

-- 找到临时表的记录数
SELECT @intCountProperties = COUNT(*),
@intCounter = 1
FROM @tmp_lock_who

IF @@ERROR <> 0
RETURN @@ERROR

IF @intCountProperties = 0
SELECT '现在没有阻塞和死锁信息' AS message
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid, @bl = bl, @sql_handle = sql_handle
FROM @tmp_lock_who
WHERE id = @intCounter
BEGIN
IF @bl = 0
BEGIN
SELECT '阻塞根源' + CAST(@spid AS VARCHAR(10)) AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
END
ELSE
BEGIN
SELECT CAST(@spid AS VARCHAR(10)) + '被' + CAST(@bl AS VARCHAR(10)) + '阻塞' AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
END
DBCC INPUTBUFFER(@spid)
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END

RETURN 0
END
GO

SELECT top 10(total_elapsed_time / execution_count) / 1000 N'平均时间ms',
total_elapsed_time / 1000 N'总花费时间ms',
total_worker_time / 1000 N'所用的CPU总时间ms',
total_physical_reads N'物理读取总次数',
total_logical_reads / execution_count N'每次逻辑读次数',
total_logical_reads N'逻辑读取总次数',
total_logical_writes N'逻辑写入总次数',
execution_count N'执行次数',
creation_time N'语句编译时间',
last_execution_time N'上次执行时间',
SUBSTRING(st.text,
(qs.statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN
DATALENGTH(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset) / 2) + 1) N'执行语句',
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE SUBSTRING(st.text,
(qs.statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN
DATALENGTH(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset) / 2) + 1) not like
'%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

 

--获得IO高的查询
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
 ,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;




--获得I/O统计
Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type


查询当前I/O锁
select DB_NAME(database_id), file_id, io_stall,io_pending_ms_ticks,scheduler_address from sys.dm_io_virtual_file_stats(NULL, NULL) t1,sys.dm_io_pending_io_requests as t2 where t1.file_handle = t2.io_handle


看是那5条语句导致I/O高
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,(total_logical_writes/execution_count) as avg_logical_writes,(total_physical_reads/execution_count) as avg_phys_reads,Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc


根据句柄得到语句
select text from sys.dm_exec_sql_text(0x03000E00D4AB884E808214016B9A00000100000000000000)




查询可以确定按 CPU 使用率衡量的、开销最高的查询
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;




高开销的 CLR 查询
SELECT TOP 10
[Average CLR Time] = total_clr_time / execution_count
,[Total CLR Time] = total_clr_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;




最常执行的查询
SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;




受阻塞影响的查询
SELECT TOP 10
[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time - total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;




最低计划重用率
SELECT TOP 100
[Plan usage] = cp.usecounts
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX),
qt.text)) * 2 ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;


数据库连接情况
SELECT session_id,connect_time,endpoint_id,auth_scheme,num_reads,num_writes,client_net_address,connection_id from sys.dm_exec_connections order by client_net_address


查询优化器信息
select * from sys.dm_exec_query_optimizer_info


当前执行请求
select * from sys.dm_exec_requests


当前执行session
select * from sys.dm_exec_sessions




所有的调度器并产看等待运行的任务数量
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255




所有的调度器并产看等待运行的任务数量
select
* 
from
sys.dm_os_schedulers
where
scheduler_id < 255




整个CPU使用中最占用资源的查询
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc






所有的调度器并产看等待运行的任务数量
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255




所有的调度器并产看等待运行的任务数量
select
* 
from
sys.dm_os_schedulers
where
scheduler_id < 255




整个CPU使用中最占用资源的查询
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc




得到在给定的时间段内花费在查询优化的时间
select * from sys.dm_exec_query_optimizer_info




重编译次数最多的25个存储过程
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc




累计使用cpu最多的查询
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

上面一个是SQL语句，一个是存储过程，可以查询当前数据库阻塞的存储过程和SQL语句