SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强

什么是等待

简单说明一下什么是等待:

当应用程序对SQL Server发起一个Session请求的时候,这个Session请求在数据库中执行的过程中会申请其所需要的资源,

比如可能会申请内存资源,表上的锁资源,物理IO资源,网络资源等等,

如果当前Session运行过程中需要申请的某些资源无法立即得到满足,就会产生等待。

SQL Server会以不用的方式来展现这个等待信息,比活动Session的等待信息,实例级的等待信息等等。

SQL Server中,等待事件是作为DBA进行TroubleShooting的重要参考信息之一,SQL Server中可以通过多中方式来获取等待信息。

但是对于SQL Server 2016之前的版本来说,不管是活动Session级别的等待和实例级的等待,参考意义都有限,

更多的时候是想要更加详细的且可以事后分析的等待,这就需要收集那些曾经已执行过的Session产生的等待信息,也就是特定Session等待信息的历史记录

本文重点介绍使用扩展事件来捕获等待信息,但不介绍扩展事件本身的使用,重点放在如何使用扩展事件来获取想要的等待事件信息。

需要对扩展事件有一定的了解。

等待信息的获取途径

在SQL Server中有一个系统视图sys.dm_os_wait_stats记录了自数据库服务启动以来累积产生的等待信息,

如下图,这个结果是实例级的,也就是记录的整个数据库服务器所有的等待事件的累积。

多数时候参考意义不是很大,比如某一天的网络延时很高,sys.dm_os_wait_stats中累计记录了大量的ASYNC_NETWORK_IO等待信息。

但是到了第二天或者什么时候,网络变好了,但是sys.dm_os_wait_stats中记录的ASYNC_NETWORK_IO等待信息是不变的

也就是说sys.dm_os_wait_stats无法反映实时等待情况。

当然要获取实时的等待信息也简单,记录两个时间点之间sys.dm_os_wait_stats中等待时间的差值,可以间接地反映出来某一段时间的数据的等待信息。

但是这个信息仍然比较粗略,依旧是实例级的,某些时候依旧是不足够作为参考的。

另外一个是通过sys.dm_exec_requests这个系统视图的wait_type,wait_time等获取活动Session的等待信息

如截图,但是这个是活动Session的信息,当Session完成之后,它的等待信息就看不到曾经都产生了那些等待,分别是多久。

也就说,你无法追溯历史上某一个Session或者某一个SQL(存储过程)执行过程中的都产生了什么类型的等待,等待了多长时间。

实话说,不管是sys.dm_os_wait_stats还是sys.dm_exec_requests,在正常情况下,获取到的等待信息实用价值都是不高或者是适用场景有限。

更多的时候我们是想要更细一级的等待,比如某一个Login、某一部分Session、甚至某些特定的SQL(存储过程)的执行过程中产生的等待信息。

举个实际例子,数据库又10个Login给10个不同的应用程序访问,其中只有1个应用程序端反馈说访问数据库慢,或者有性能问题,其他Login都反馈正常

那么很有可能是这个Login请求自己的问题,此时就需要针对这一个Login的情况进行针对性分析,而不是在实例级分析诊断。

如果能够拿到这个Login执行的Session的等待情况,或者这个Login某些特定的数据库对象的执行过程中的等待信息,对定位问题的针对性的就比较强了。

本文就以此为切入点,针对如何获取Session级别的等待信息展开说明和演示。

SQL Server 2016中获取Session级别的等待信息

在SQL Server 2016中,获取Session级别的等待信息是比较方便的,有直接的系统视图sys.dm_exec_session_wait_stats可以使用

当前情况下,想要知道某一个Session的等待信息就很简单了,

在sql语句开始的时候把当前Session的等待信息记录下来

在sql语句结束的时候把当前Session的等待信息再次记录出来

计算两次等待信息的差值,就可以知道当前Session运行的过程中有哪些等待,分别是多少。

  sqlserver开发团队可能也意识到了对于等待信息,更多的时候,需要的是较为具体的等待,而不是一个笼统的实例级的等待

  因此在SQL Server 2016中增加了sys.dm_exec_session_wait_stats这个支持统计Session级别的等待的视图

  很不幸的SQL Server2016之前的版本中是没有这个系统视图可以很方便地记录Session级别的等待。

  但是可以借助扩展事件来实现类似的功能。

使用扩展事件来捕获Session级别的等待信息

因为这里是是用扩展事件来实现的,这里要求读者要对扩展事件有一个基本的认识,扩展事件本身就不多说了。

上代码,启动一个扩展事件,来记录执行时间超过三秒的SQL语句,其执行过程中等待时间大于0的等待事件信息。

当然这个捕获的信息可以加上各种过滤条件。具体参考代码备注。

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='CollectionSessionWaitStats')
 DROP EVENT session CollectionSessionWaitStats ON SERVER;
GO
-- 创建事件会话
CREATE EVENT SESSION CollectionSessionWaitStats ON SERVER
ADD EVENT sqlserver.rpc_completed
(
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.username,
    sqlserver.sql_text,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE [duration]>=3000000
),
ADD EVENT sqlserver.sql_batch_completed
(
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.username,
    sqlserver.sql_text,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE [duration]>=3000000
),
ADD EVENT sqlos.wait_info
(
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.sql_text,
    sqlserver.username,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE session_id>50
      and opcode=1
      and duration>0
      and sql_text not like '%sp_MScdc_capture_job%' --排除某些信息
      --and username = '' --限定只记录某些信息
),
ADD EVENT sqlos.wait_info_external
(
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.username,
    sqlserver.sql_text,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE  session_id>50
      and opcode=1
      and duration>0
      and sql_text not like '%sp_MScdc_capture_job%'
      --and username = ''
)
ADD TARGET package0.event_file
(
  SET filename=N'D:\XEventFiles\CollectionSessionWaitStats',
  max_file_size=(1024),
  max_rollover_files=(10)
)
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
  )
GO
-- 启用(停止)事件会话(START / STOP)
ALTER EVENT SESSION CollectionSessionWaitStats ON SERVER STATE=START
GO

是用扩展事件收集的Session级别的等待信息有以下几个特点

1,SessionId是可以重复的

  举例说明就是:比如一个SessionId = 80的Session,可以第一次执行一个SQLA,第二次执行一个SQLB.

  同时这两个SQL的执行与其执行过程中产生的等待信息都可以被捕获出来

2,同一个时间段内,一个同一个SessionId不可能同时执行,

  比如16:46:36秒到16:46:46秒到这个时间内,SessionId = 80的Session正在执行,扩展事件捕获到了其产生的等待信息

  但是在16:46:36秒到16:46:46这个时间段内,不可能有另外一个SessionId = 80也在执行,这个逻辑不难理解

为什么要特意说明这个问题?

因为扩展事件收集到的事件信息中SQL语句完成事件(rpc_completed或者sql_batch_completed)与产生的等待之间,没有一个直接的对应关系。

怎么理解?动起手来才能发现问题,我是这个纠结了大半天,截图示例。

如下截图,扩展事件捕获到的两个目标事件的SessionId都是58,但是执行的SQL是不一样的,

如下截图是不区分事件类型,捕获到的所有的事件信息。

也就是上面说的,对于58号Session:

第一次运行了SELECT COUNT(1) FROM TestCollectionSessionWaitStats

第二次运行了SELECT COUNT(1) FROM TestCollectionSessionWaitStats WHERE Id>10

两次运行的SQL一致或者不一致问题不大,关键是捕获到的等待事件信心的SessionId也是58,怎么区分产生的事件是归属于哪一次的运行?

比如58号Session运行两次sql,产生了10条等待信息,怎么区分这10条等待信息哪些归属于第一次运行生成的,哪些归属于第二次运行生成的?

这个就依靠上面说的第二点“同一个时间段内,一个同一个SessionId不可能同时执行”

对于同一个SessionId,在sql_batch_completed事件中,从时间的维度来看

小于第一次sql_batch_completed事件完成时间的必然是58号Session第一次执行生成的

大于第一次sql_batch_completed事件完成事件的且小于第二次sql_batch_completed的必然是58号Session第二次执行生成的

有了这个理论基础,我们统计Session级别的等待就比较容易了,相信这个逻辑的实现并不难。

如下图是58号Session执行某SQL语句产生的等待详细信息,可以看到多次产生了CXPACKET和 PAGEIOLATCH_SH等待

鉴于截图问题,下面还有另外一个同样是58号Session的执行另外一个SQL捕获到的等待信息

这个统计办法就是上面提到的,在两次sql_batch_completed事件中,虽然等待事件的SessionId一样,

但是其发生的时候是处于当前事件的sql_batch_completed之前,上一次sql_batch_completed之后,

这样就可以完美地匹配到sql_batch_completed事件与其对应的wait_info事件。

在这种情况下,统计得到类似于SQL Server 2016中的sys.dm_os_wait_stats的结果也就不难了。

与sys.dm_os_wait_stats 等待信息的结果相比,上述通过扩展事件获取的等待信息,是不是更加详细和具体?

比如对于CXPACKET等待时间,

不难发现,如果计算计算其产生的次数(count),就类似于sys.dm_os_wait_stats 中的waiting_tasks_count,计算其产生的总时间(sum),就类似于wait_time_ms

但是上述时间的信息已经细化到Session级别了,比sys.dm_os_wait_stats 中的等待信息更有参考价值。
对于问题的诊断和分析,也会是更加有效。

上述统计结果的SQL语句

-- Parse the XML to show rpc_completed,sql_batch_completed details
if object_id('tempdb..#t1') is not null
  drop table #t1
SELECT
  event_xml.value('(./action[@name="session_id"]/value)[1]', 'INT') as session_id,
  event_xml.value('(./@timestamp)', 'varchar(1000)') as timestamp,
  event_xml.value('(./data[@name="statement"]/value)[1]', 'varchar(max)') as statement,
  event_xml.value('(./data[@name="batch_text"]/value)[1]', 'varchar(max)') as batch_text,
  event_xml.value('(./@name)', 'varchar(1000)') as Event_Name,
  event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as Duration,
  event_xml.value('(./data[@name="cpu_time"]/value)[1]', 'bigint') as cpu_time,
  event_xml.value('(./data[@name="physical_reads"]/value)[1]', 'bigint') as physical_reads,
  event_xml.value('(./data[@name="logical_reads"]/value)[1]', 'bigint') as logical_reads,
  event_xml.value('(./action[@name="username"]/value)[1]', 'varchar(max)') as username
INTO #t1
FROM (
    SELECT CAST(event_data AS XML) xml_event_data
    FROM sys.fn_xe_file_target_read_file(N'D:\XEventFiles\CollectionSessionWaitStats*', NULL, NULL, NULL)
   ) AS event_table
CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE event_xml.value('(./@name)', 'varchar(1000)') in ('rpc_completed','sql_batch_completed')
order by Event_Name
-- Parse the XML to show wait_info,wait_info_external details
if object_id('tempdb..#t2') is not null
  drop table #t2
SELECT
  cast(event_xml.value('(./@timestamp)', 'varchar(1000)') as DATETIME2) as timestamp,
  event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as duration,
  event_xml.value('(./action[@name="session_id"]/value)[1]', 'INT') as session_id,
  event_xml.value('(./data[@name="wait_type"]/text)[1]', 'VARCHAR(200)') as wait_type
INTO #t2
FROM (
    SELECT CAST(event_data AS XML) xml_event_data
    FROM sys.fn_xe_file_target_read_file(N'D:\XEventFiles\CollectionSessionWaitStats*', NULL, NULL, NULL)
   ) AS event_table
   CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE event_xml.value('(./@name)', 'varchar(1000)') in ('wait_info','wait_info_external')
if object_id('tempdb..#t3') is not null
  drop table #t3
SELECT
  a.session_id          AS SessionId,
  isnull(statement,batch_text)  AS SQLTEXT,
  a.Duration            AS TotalExecuteTime,
  CAST(a.timestamp AS DATETIME2)  AS CompletedTime,
  CAST(b.timestamp AS DATETIME2)  AS WaitTypeStartTime,
  b.wait_type            AS WaitType,
  b.duration            AS WaitDuration
INTO #t3
FROM #t1 a INNER JOIN #t2 b on a.session_id = b.session_id
  and b.timestamp < a.timestamp
  and b.timestamp>(
             select top 1 timestamp from #t1 c
             where a.session_id = a.session_id and a.timestamp > b.timestamp
             order by a.timestamp
          )
select
  case when rn = 1 then SessionId else NULL end as SessionId,
  case when rn = 1 then SQLTEXT else NULL end as SQLTEXT,
  case when rn = 1 then TotalExecuteTime else NULL end as TotalExecuteTime,
  CompletedTime as CompletedTime,
  WaitType,
  WaitTypeStartTime as WaitTypeStartTime,
  WaitDuration
from
(
  select ROW_NUMBER()over(partition by SessionId,SQLTEXT,TotalExecuteTime,CompletedTime order by CompletedTime,WaitTypeStartTime) as rn,
  * FROM #t3
)

当然该语句仅供参考,目的是为了收集Session级的统计信息,当收集到Session级别的统计信息之后,具体的统计方式也不难。

总结

   等待事件可以帮助我们诊断SQL Server上的一些资源瓶颈,对于问题的处理和解决有着比较重要的参考意义,如果能够细化地收集等待事件,对于解决问题的参考意义会更大。

   本文通过一个简单的示例,使用扩展事件来收集SQL Server中一些特定场景下的等待信息,来更加有针对性地进行问题的诊断和识别,使得问题的分析更加高效和具有针对性。

以上所述是小编给大家介绍的SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • php中使用session_set_save_handler()函数把session保存到MySQL数据库实例

    PHP保存session默认的是采用的文件的方式来保存的,这仅仅在文件的空间开销很小的windows上是可以采用的,但是如果我们采用uinx或者是liux上的文件系统的时候,这样的文件系统的文件空间开销是很大的,然而session是要时时刻刻的使用的,大量的用户就要创建很多的session文件,这样对整个的服务器带来性能问题. 另一方面,如果服务器起采用群集的方式的话就不能保持session的一致性,所以我们就绪要采用数据库的方式来保存session,这样,不管有几台服务器同时使用,只要把他们的

  • 解析php session_set_save_handler 函数的用法(mysql)

    复制代码 代码如下: <?php /*============================文件说明======================================== @filename:     session.class.php @description:  数据库保存在线用户session,实现在线用户功能! @notice:       session过期时间一个小时,因为我们的站点是使用cookie(有效时间是1小时)登录.                 因此我们只记

  • ThinkPHP实现将SESSION存入MYSQL的方法

    本文以实例讲解了ThinkPHP实现将SESSION存入MYSQL的方法,所采用的运行环境是ThinkPHP3.1.2版 首先index.php中设置为: <?php define('APP_DEBUG', true);//设置为调试模式 require '../ThinkPHP/ThinkPHP.php';//设置入口文件 ini_set("session.save_handler", "user");//设置PHP的SESSION由用户定义 在config

  • PHP实现利用MySQL保存session的方法

    session是PHP程序设计中服务器端用来保存用户信息的一个变量,具有非常广泛的应用价值.本文实例讲述了PHP实现利用MySQL保存session的方法.分享给大家供大家参考之用.具体步骤如下: 本文实例的实现环境为: PHP 5.4.24 MySQL 5.6.19 OS X 10.9.4/Apache 2.2.26 一.代码部分 1.SQL语句: CREATE TABLE `session` ( `skey` char(32) CHARACTER SET ascii NOT NULL, `d

  • SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强

    什么是等待 简单说明一下什么是等待: 当应用程序对SQL Server发起一个Session请求的时候,这个Session请求在数据库中执行的过程中会申请其所需要的资源, 比如可能会申请内存资源,表上的锁资源,物理IO资源,网络资源等等, 如果当前Session运行过程中需要申请的某些资源无法立即得到满足,就会产生等待. SQL Server会以不用的方式来展现这个等待信息,比活动Session的等待信息,实例级的等待信息等等. SQL Server中,等待事件是作为DBA进行TroubleSh

  • 深入分析React源码中的合成事件

    目录 热身准备 明确几个概念 事件系统角色划分 事件注册 registerSimpleEvents registerEvents$2 registerEvents$1 registerEvents$3 registerEvents 事件监听 事件派发 合成事件 捕获和冒泡 总结 热身准备 明确几个概念 在React@17.0.3版本中: 所有事件都是委托在id = root的DOM元素中(网上很多说是在document中,17版本不是了): 在应用中所有节点的事件监听其实都是在id = root

  • iOS开发中Subview的事件响应以及获取subview的方法

    Subview的事件响应 在view的层级里面,默认情况下subview是可以显示到其父view的frame区域以外的,通过设置clipToBounds属性为YES,可以限制subview的显示区域.但是touch在各个UIView中传递的时候,区域时限制在view的frame内,此处包含两个信息:1.在当前view的frame以外所做的操作是不会传递到该view中的,这一点很容易理解.2.如果touch事件是发生在当前view的frame以外,该view所有的subview将也不会再收到该消息

  • VUE中v-on:click事件中获取当前dom元素的代码

    在开发中总是忘记,特意在此记录 关键字:   $event <div class="bed" v-on:click="updateBed(index,$event)">{{item.BedID}}<br>{{item.CriminalName}}</div> updateBed: function(index, e) { var selectedBedDom = $(e.srcElement)//dom元素 } PS:下面看下v-

  • Android Manifest中meta-data扩展元素数据的配置与获取方式

    在AndroidManifest.xml清单文件中 我们有时会看到如下类似的<meta-data ... >元素开始的配置内容: <meta-data android:name="com.google.android.maps.v2.API_KEY" android:value="AIzaSyBhBFOgVQclaa8p1JJeqaZHiCo2nfiyBBo" /> <meta-data android:name="com.g

  • C#中的委托和事件学习(续)

    引言 如果你看过了 C#中的委托和事件 一文,我想你对委托和事件已经有了一个基本的认识.但那些远不是委托和事件的全部内容,还有很多的地方没有涉及.本文将讨论委托和事件一些更为细节的问题,包括一些大家常问到的问题,以及事件访问器.异常处理.超时处理和异步方法调用等内容. 为什么要使用事件而不是委托变量? 在 C#中的委托和事件 中,我提出了两个为什么在类型中使用事件向外部提供方法注册,而不是直接使用委托变量的原因.主要是从封装性和易用性上去考虑,但是还漏掉了一点,事件应该由事件发布者触发,而不应该

  • 深入解析PHP的Yii框架中的event事件机制

    事件 事件可以将自定义代码"注入"到现有代码中的特定执行点.附加自定义代码到某个事件,当这个事件被触发时,这些代码就会自动执行.例如,邮件程序对象成功发出消息时可触发 messageSent 事件.如想追踪成功发送的消息,可以附加相应追踪代码到messageSent 事件. Yii 引入了名为 yii\base\Component 的基类以支持事件.如果一个类需要触发事件就应该继承 yii\base\Component 或其子类. Yii的event机制 YII的事件机制,是其比较独特

  • JQuery中的常用事件、对象属性与使用方法分析

    本文实例讲述了JQuery中的常用事件.对象属性与使用方法.分享给大家供大家参考,具体如下: JQuery中的常用事件 .click() 鼠标单击触发事件,参数可选(data,function) .dblclick() 双击触发,同上 .mousedown()/up() 鼠标按下/弹起触发事件 .mousemove() 鼠标移动事件 .mouseover()/out() 鼠标移入/移出触发事件 .mouseenter()/leave() 鼠标进入/离开触发事件* .hover(func1,fun

  • 使用SQL Mail收发和自动处理邮件中的扩展存储过程简介

    使用SQL Mail收发和自动处理邮件中的扩展存储过程简介 SQL SERVER提供了通过EXCHANGE或OUTLOOK收发邮件的扩展存储过程,下面将这几个过程简单的介绍一下. 一.启动SQL Mail xp_startmail @user,@password @user和@password都是可选的 也可打开Enterprise Manager中的Support Services,在SQL Mail上单击右键打开右键菜单,然后按Start来启动 二.停止SQL Mail xp_stopmai

  • JS中dom0级事件和dom2级事件的区别介绍

    dom0级事件 <a href="#" id="hash" onclick="fn();fn();"> <button type="button">返回上面进行开通</button> </a> var btn=$('#hash').get(); btn.onclick=function(){ alert(''); }; btn.onclick=function(){ alert(

随机推荐