查询占用cpu最高的20条sql语句,sql2008

SELECT TOP 10 a.creation_time,total_worker_time/execution_count AS avg_cpu_cost, plan_handle,execution_count,(SELECT SUBSTRING(text, statement_start_offset/2 + 1,(CASE WHEN statement_end_offset = -1THEN LEN(CONVERT(nvarchar(max), text)) * 2ELSE statement_end_offsetEND - statement_start_offset)/2)FROM sys.dm_exec_sql_text(sql_handle) b) AS query_textFROM sys.dm_exec_query_stats aORDER BY [avg_cpu_cost] DESC

查看死锁

use mastergodeclare @spid int,@bl intDECLARE s_cur CURSOR FOR select  0 ,blockedfrom (select * from sysprocesses where  blocked>0 ) a where not exists(select * from (select * from sysprocesses where  blocked>0 ) b where a.blocked=spid)union select spid,blocked from sysprocesses where  blocked>0OPEN s_curFETCH NEXT FROM s_cur INTO @spid,@blWHILE @@FETCH_STATUS = 0beginif @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,@blendCLOSE s_curDEALLOCATE s_curexec sp_who2