sqlserver数据库死锁查询，删除，语句耗时查询

查询死锁

?

1 2 3 4

SELECT request_session_id spid,OBJECT_NAME (resource_associated_entity_id)tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT';

杀进程

?

1	exec ('Kill 1223')

当前语句耗时查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

/*当前耗时查询*/ SELECT TOP 20     total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],     qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],     last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],     SUBSTRING(qt.text,qs.statement_start_offset/2+1,         (CASE WHEN qs.statement_end_offset = -1         THEN DATALENGTH(qt.text)         ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)     AS [使用CPU的语法], qt.text [完整语法],     dbname=db_name(qt.dbid),     object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY  total_worker_time DESC

选取前10个最耗CPU时间的语句，具体到表和语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT TOP 10 cpu_time, [session_id], [request_id], [start_time] AS '开始时间', [status] AS '状态', [command] AS '命令', dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名', [blocking_session_id] AS '正在阻塞其他会话的会话ID', [wait_type] AS '等待资源类型', [wait_time] AS '等待时间', [wait_resource] AS '等待的资源', [reads] AS '物理读次数', [writes] AS '写次数', [logical_reads] AS '逻辑读次数', [row_count] AS '返回结果行数' FROM sys.[dm_exec_requests] AS der CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest WHERE [session_id]>50 ORDER BY [cpu_time] DESC

　　

sqlserver死锁检查工具 （存储过程）if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

<em id="__mceDel"><em id="__mceDel">OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_who_lock] GO    use master go create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int    create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint)    IF @@ERROR<>0 RETURN @@ERROR    insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (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>0    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 from #tmp_lock_who where Id = @intCounter 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 ) end    -- 循环指针下移 set @intCounter = @intCounter + 1 end<br> drop table #tmp_lock_who return 0 end </em></em>

?

1

执行存储过程

?

1	exec sp_who_lock

 

根据表名查询在哪个数据库

?

1	exec sp_MSforeachdb @command1='USE ? if exists(SELECT 1 from sysobjects where id=object_id(''填表名'')) PRINT ''?'''

 

数据库索引

?

1 2	DBCC showcontig('表名')--分析表的索引建立情况 DBCC DBREINDEX('表名') --重建索引的语句