日期:2014-05-18 浏览次数:20504 次
SELECT s2.dbid , DB_NAME(s2.dbid) AS [数据库名] , --s1.sql_handle , ( SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2 + 1, ( ( CASE WHEN statement_end_offset = -1 THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2 ) ELSE statement_end_offset END ) - statement_start_offset ) / 2 + 1) ) AS [语句] , execution_count AS [执行次数] , last_execution_time AS [上次开始执行计划的时间] , total_worker_time AS [自编译以来执行所用的 CPU 时间总量(微秒)] , last_worker_time AS [上次执行计划所用的 CPU 时间(微秒)] , min_worker_time AS [单次执行期间曾占用的最小 CPU 时间(微秒)] , max_worker_time AS [单次执行期间曾占用的最大 CPU 时间(微秒)] , total_logical_reads AS [总逻辑读] , last_logical_reads AS [上次逻辑读] , min_logical_reads AS [最少逻辑读] , max_logical_reads AS [最大逻辑读] , total_logical_writes AS [总逻辑写] , last_logical_writes AS [上次逻辑写] , min_logical_writes AS [最小逻辑写] , max_logical_writes AS [最大逻辑写] FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid IS NULL ORDER BY last_worker_time DESC
------解决方案--------------------
--计算SQL语句执行时间: declare @date1 datetime set @date1=getdate() 你的SQL语句 declare @date2 datetime set @date2=getdate() select datediff(ms,date1,date2)
------解决方案--------------------
--检查阻塞:
EXEC SP_WHO——BLK列中不为0的就是阻塞的SPID
--检查全部活动进程:
EXEC SP_WHO ‘ACTIVE’
--检查某用户当前进程:
EXEC SP_WHO ‘SA’
--检查阻塞的锁信息:
EXEC SP_LOCK SPID的值
--获得阻塞的T-SQL语句:
DBCC INPUTBUFFER (SPIN的值)
--查看锁住表的session_id:
select request_session_id,*
from sys.dm_tran_locks where
resource_associated_entity_id=object_id('[TableName]')