SqlServer 查询死锁，杀死死锁进程

 

-- 查询死锁
select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT' 

--kill 354 

--DBCC INPUTBUFFER (53)

转发自：https://www.cnblogs.com/fuyuanming/p/5783421.html

--查询CPU执行过高的语句
 SELECT TOP 100 TEXT AS 'SQL语句'
    ,last_execution_time AS '上次执行时间'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [平均 IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [平均CPU时间（秒）]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [平均运行时间（秒）]
    ,execution_count AS "执行计数",qs.total_physical_reads,qs.total_logical_writes
    ,qp.query_plan AS "查询计划"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time / execution_count DESC

 

--查询连接数据库的客户端IP最后执行语句
SELECT st.text,qs.client_net_address,qs.* FROM  sys.dm_exec_connections qs
CROSS APPLY sys.dm_exec_sql_text(qs.most_recent_sql_handle) st
--CROSS APPLY sys.dm_exec_query_plan(qs.most_recent_sql_handle) qp
WHERE st.text LIKE '%news%'

 

--在SQL Server 中查询执行计划可以使用下面的脚本：
--DBCC FreeProccache

select total_elapsed_time / execution_count 平均时间,total_logical_reads/execution_count 逻辑读,
usecounts 重用次数,SUBSTRING(d.text, (statement_start_offset/2) + 1,
         ((CASE statement_end_offset 
          WHEN -1 THEN DATALENGTH(text)
          ELSE statement_end_offset END 
            - statement_start_offset)/2) + 1) 语句执行 from sys.dm_exec_cached_plans a
cross apply sys.dm_exec_query_plan(a.plan_handle) c
,sys.dm_exec_query_stats b
cross apply sys.dm_exec_sql_text(b.sql_handle) d
--where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000
ORDER BY total_elapsed_time / execution_count DESC;

 

--查询最近执行的语句
SELECT TOP 100
    deqs.last_execution_time AS [Execution Time],
    dest.TEXT AS [Query]
FROM 
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE 
    dest.TEXT LIKE '%your_search_string%' -- 在这里替换为你要查询的语句
ORDER BY 
    deqs.last_execution_time DESC

 

--查询执行计划
SELECT usecounts,text,plan_handle,* FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle)
where text LIKE
'%sql%'


--清理执行计划
DBCC FREEPROCCACHE(0x05000B000F7D747A808F797B2800000001000000000000000000000000000000000000000000000000000000)