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;