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级别等待信息的增强,大家如有疑问可以留言,或联系站长。感谢亲们支持!!!
文章来源:丸子建站
文章标题:SQLServer中使用扩大事件获得Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强
https://www.wanzijz.com/view/62390.html