mssql 查看语句运行时间异常的原因（SQLServer）

概述经常有开发同事反映如下情况：我有一条语句或者一个JOB昨天跑半个小时就完成了，今天怎么跑了两个小时还没有完成？ 是不是数据库出现问题了？     数据库语句运行时间异常，其实是一个比较复杂的情况，因为数据是不断变动的，今天好好的一条语句，有可能明天运行就 不在预计的时间内了，这个场景是没办法完全重溯的，即便有当时的备份数据，但是当时的服务器压力是没有办法知道和营造 的；但是好在现在不是要调查昨天语 经常有开发同事反映如下情况：我有一条语句或者一个JOB昨天跑半个小时就完成了，今天怎么跑了两个小时还没有完成？

是不是数据库出现问题了？

    数据库语句运行时间异常，其实是一个比较复杂的情况，因为数据是不断变动的，今天好好的一条语句，有可能明天运行就

不在预计的时间内了，这个场景是没办法完全重溯的，即便有当时的备份数据，但是当时的服务器压力是没有办法知道和营造

的；但是好在现在不是要调查昨天语句跑时间异常的原因，而是要找到现在语句运行异常的原因，现在的情况还正在进行着呢，

所以我们可以根据语句目前的情况，初步来排查一下；

    其实要考虑的问题比较多：

    1. 索引是否正常（索引是否损坏、有没有人删除索引等）；

    2. 统计信息是否过时；

    3. 语句执行计划是否发生偏移（和索引、统计信息以及数据量都有关系）；

    4. 语句是否有BUG；

    5. 是否发生的阻塞；

    6. 系统资源是否遇到瓶颈；

    .........

    这么多的情况都考虑的话我们很难下手，一般解决这个问题我们都需要采用比较快的方式来做排查，以下方法主要针对5和6两

个方面进行，因为这两个方面是最常见的情况。

我们来简单模拟一下排查过程:

1. 创建测试表和数据

USE [master]GO/****** Object:  table [dbo].[a]    Script Date: 01/17/2012 16:46:34 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_padding ONGOCREATE table [dbo].[a](    [ID] [int] IDENTITY(1,1) NOT NulL,[name] [varchar](100) NulL) ON [PRIMARY]GOSET ANSI_padding OFFGOinsert into a values('aa'),('bb'),('cc')

2. 制造阻塞：开两个session，分别运行下面的语句

--Session 1use mastergobegin tranupdate A set name='abc' where ID=2--rollback

--Session 2select * from a 

因为Session1 的Update语句没有能够提交，所以此时Session2 过程会被阻塞

3. 分析排查：

  我们首先需要查询下此时数据库中是否存在阻塞：

--Blockedselect * from sys.sysprocesses with(nolock) where blocked<>0

 我们看到了阻塞的记录，53阻塞了56，被阻塞的资源是：dbID 1 file 1 page 307；

 接下来我们需要知道阻塞和被阻塞的是什么语句，有两种方式：

 a. dbcc inputbuffer

 b. sys.dm_exec_sql_text

 方法一与方法二相比：

   优点：方法一能显示非活动session的语句，方法二只能查活动的session（通过sp_who2 active 能显示是否活动）；

   缺点：方法一只能一个一个查询，方法二可以多个一起查询；

方法一：

--No1:dbcc inputbuffer(53)godbcc inputbuffer(56)

方法二：

--No2:SELECT    S.session_ID,R.blocking_session_ID,S.host_name,S.login_name,databasename=DB_name(R.database_ID),R.command,R.status,current_execute_sql = SUBSTRING(T.text,R.statement_start_offset / 2 + 1,CASE                    WHEN statement_end_offset = -1 THEN LEN(T.text)                    ELSE (R.statement_end_offset - statement_start_offset) / 2+1                END),S.program_name,S.status,S.cpu_time,memory_usage_kb = S.memory_usage * 8,S.reads,S.writes,S.transaction_isolation_level,C.connect_time,C.last_read,C.last_write,C.net_transport,C.clIEnt_net_address,C.clIEnt_tcp_port,C.local_tcp_port,R.start_time,R.wait_time,R.wait_type,R.last_wait_type,R.wait_resource,R.open_transaction_count,R.transaction_ID    FROM sys.dm_exec_sessions S    left JOIN sys.dm_exec_connections C        ON S.session_ID = C.session_ID    left JOIN sys.dm_exec_requests R        ON S.session_ID = R.session_ID            AND C.connection_ID = R.connection_ID    OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) TWHERE  S.is_user_process = 1  -- 如果不限制此条件，则查询所有进程（系统和用户进程）and s.session_ID in(53,56)

我们看到方法一两条语句都能查出来，而方法二只能查出一个语句；

到这里，我们已经能判断语句运行慢的原因是被阻塞了，我们再来查查阻塞的原因是什么，可以通过以下语句查看：

select request_session_ID,resource_type,db_name(resource_database_ID) as DBname,resource_description,request_mode,request_type,request_status from sys.dm_tran_locks where request_session_ID in(56,53)order by request_session_ID

可以看到，56处于WAIT状态，它在等待获取1：307：1 上的一个共享锁，但是1：307：1上被53的一个排他锁占据了（GRANT代表

已获得资源，正在运行），因此56必须等待53上的排他锁释放后才能继续运行；于是我们转而调查53排他锁没有释放的原因；可能是

53需要的其他资源被其他进程占有了，在等待其他进程释放锁；也可能是因为Update语句更新的数据量过多，需要的时间比较长，不

能够及时的释放锁；还有就是我们现在的情况，没有提交事物了（语句中可以直接看到）；阻塞的排查方法都是类似的。

 

如果语句并没有被其他语句blocked呢？ 那我们需要再进一步查找的原因就是Wait了，前面已经有wait的相关查询，下面我们来查下

更具体的信息：

-- wait & lockselect lo.request_session_ID as [Session],DB_name(lo.resource_database_ID) as Dbname,lo.resource_type as [Type],lo.resource_description,lo.request_mode,lo.request_owner_type,lo.request_status,case when lo.resource_type='OBJECT' then OBJECT_name(lo.resource_associated_entity_ID)     when lo.resource_associated_entity_ID IS NulL OR lo.resource_associated_entity_ID=0     then NulL     else OBJECT_name(p.object_ID)      end as Associated_Entity,wt.blocking_session_ID,wt.resource_descriptionfrom sys.dm_tran_locks lo with(nolock)left join sys.partitions p with(nolock)on lo.resource_associated_entity_ID=p.partition_IDleft join sys.dm_os_waiting_tasks wt with(nolock)on lo.lock_owner_address=wt.resource_addresswhere lo.request_session_ID>50and lo.request_session_ID=56 order by [Session],[TYPE]

上面可以看到，56在获取共享资源1：307：1时，遇到了等待，当然这里的等待还是被53阻塞了，但是等待会有多种原因的等待，我们查

一下当前的等待信息：

--current wait infoselect wait_type,COUNT(0) as num_waiting_tasks,SUM(wait_duration_ms) as total_wait_time_ms from sys.dm_os_waiting_tasks with(nolock)where session_ID>50group by wait_typeorder by wait_type

这里可以看到是锁等待（Wait_Type），还有很多资源类型的等待，值的重点关注的有：
  Memory:cmeMTHREAD,RESOURCE_SEMAPHORE
     cmeMTHREAD:
       说明和原因：计划缓存出现问题的标志（大量计划加入或者移出）；
       解决：     使用参数化的查询或者设置数据库强制参数化（forced parameterization）

     RESOURCE_SEMAPHORE：
       说明和原因：内存密集型查询无法获得请求的内存；其他进程消耗了太多的内存；
       解决：     为数据库添加合适的索引或者增加内存

  IO：IO_COMPLETION,ASYNC_IO_COMPLETION,WRITELOG,PAGEIolATCH_*

  cpu: CXPACKET,SOS_SCHEDulER_YIELD
       CXPACKET:
          说明和原因：并行处理等待类型，并行同步等待；
          解决：     可以通过修改并行度的值（或者禁用）解决；
       SOS_SCHEDulER_YIELD：
          说明和原因：任务执行到时间片尾，让出调度器给其他任务运行；
          解决：     需要处理能力更好的cpu
 
  Network：ASYNC_NETWORK_IO,DBMIRROR_SEND
       ASYNC_NETWORK_IO: 网卡带宽饱和或者客户端不能及时把结果取走；
       DBMIRROR_SEND：  网络带宽不足以支持镜像事务量或者镜像数据库超出限额；

  锁阻塞：LCK_*    

我们可以统计下，我们数据库最多的20种等待类型：

--total wait infoselect top 20 wait_type,SUM(waiting_tasks_count) waiting_tasks_count,SUM(wait_time_ms)as total_wait_time_ms,SUM(signal_wait_time_ms) as total_signal_wait_time_msfrom sys.dm_os_wait_stats with(nolock)where wait_type not in --system wait type('LAZYWRITER_SLEEP','REQUEST_FOR_DEADLOCK_SEARCH','sqlTRACE_BUFFER_FLUSH','XE_TIMER_EVENT','FT_IFTS_SCHEDulER_IDLE_WAIT','LOGMGR_QUEUE','CHECKPOINT_QUEUE','SLEEP_TASK','broKER_IO_FLUSH','broKER_TASK_Stop','broKER_TO_FLUSH','broKER_EVENTHANDLER')group by wait_typeorder by total_wait_time_ms desc

通过这个我们可以从中看出DB等待主要集中在哪些方面，如果是在cpu、IO、Memory、Lock等上面等待时间很长，说明我们

的数据库需要做某些方面的优化了。

总结

以上是内存溢出为你收集整理的mssql 查看语句运行时间异常的原因（SQLServer）全部内容，希望文章能够帮你解决mssql 查看语句运行时间异常的原因（SQLServer）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。