SQL锁表及CPU使用高查找

use HR
GO
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

----
----

declare @spid int,@bl int
DECLARE s_cur CURSOR FOR   select  0 ,blocked   from (select * from sys.sysprocesses where  blocked>0 ) a
where not exists(select * from (select * from sys.sysprocesses where  blocked>0 ) b   where a.blocked=spid)
union
select spid,blocked from sys.sysprocesses where  blocked>0   OPEN s_cur   FETCH NEXT FROM s_cur INTO @spid,@bl   WHILE @@FETCH_STATUS = 0
begin   if @spid =0
select ' 引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + ' 进程号, 其执行的SQL 语法如下'   else
select ' 进程号SPID ：'+ CAST(@spid AS VARCHAR(10))+ ' 被' + ' 进程号SPID ：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞, 其当前进程执行的SQL 语法如下'
DBCC INPUTBUFFER (@bl )   FETCH NEXT FROM s_cur INTO @spid,@bl   end   CLOSE s_cur
DEALLOCATE s_cur

----
----

use master
go
--检索死锁进程
select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name
from sysprocesses
where spid in
( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0)

-----
-----
-----

use master
go
select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name
from sysprocesses
where spid in
( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0)


kill 134


-- 查CPU使用情况
SELECT TOP 10 (a.total_worker_time / a.execution_count) AS  "平均每次cpu消耗"
,Convert(VARCHAR, Last_Execution_Time) AS  "最后执行时间"
,Total_Physical_Reads AS "物理读"
,execution_count AS "执行次数"
,SUBSTRING(b.TEXT, a.statement_start_offset / 2, (
CASE
WHEN a.statement_end_offset = - 1
THEN len(convert(NVARCHAR(max), b.TEXT)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset
) / 2) AS [Query_Text]
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_SQL_text(a.SQL_handle) AS b
ORDER BY 1 DESC