我知道SQL Server有很多视图和函数让我来了解SQL Server的运行状态.我还想知道SQL Server上关于来自用户或者应用的活动请求信息.怎么查询这些信息呢?
SQL Server的动态管理视图DMV sys.dm_exec_requests可以实现.但是它不仅仅显示了来自连接用户或应用的请求.比如,它还显示了SQL Server有非常多的后台任务.比如下面的简单查询:
select session_id,start_time,command
from sys.dm_exec_requests
where status='background';
这是一个很简单的例子,在我的测试机上返回了20多个不同的会话.
不过,通常我们是使用DMV来对活动会话进行故障排除.最先我们需要做的就是看哪些会话在经理等待.
select session_id,blocking_session_id,start_time,wait_type
from sys.dm_exec_requests
where blocking_session_id >0;
我们可以使用下面的2中方法确定查询是什么,以及是什么导致了阻塞:
1.如果有活动请求,我们可以使用sys.dm_exec_requests 和sys_dm_exec_sql_text(),然后把sql_handle作为参数传进去.
2.如果没有活动的请求,我们可以连接sys.dm_exec_commections 然后传递most_recent_sql_handle到sys.dm_exec_sql_text().
在这个例子中,我知道spid=53会话没有活动的请求,因为我查了sys.dm_exec_requests.我们再回过头来看看第二种方法.
select distinct des.session_id,dst.text as 'SQL'
from sys.dm_exec_requests as DER
join sys.dm_exec_connections as DEC
on DER.blocking_session_id=DEC.session_id
cross apply sys.dm_exec_sql_text(DEC.most_recent_sql_handle) as DST;
然后我们就发现下面的请求返回了
这看起来是一个没有问题的查询,只是简单的插入,所有我们还应该更深入的看看.这时我们应该看看是否有开启的事务,如果它有活动的请求,我们可以在sys.dm_exec_requests的open_transaction_count列看到.我们这里没有看到活动请求,我们可以看看sys.dm_exec_sessions:
select session_id,open_transaction_scount
from sys_dm_exec_sessions
where open_transaction_count >0;
我们看到了下面打开的事务,可能是随忘了提交事务.
获取活动的查询计划
如果有查询运行时间非常长,我们就需要看看查询计划了解为什么它会花这么长时间.有可能这个查询计划有问题. 下面的查询可以返回任何活动查询的查询计划:
select DER.session_id,DEQP.query_plan
from sys.dm_exec_requests as DER
cross apply sys.dm_exec_query_plan(DER.plan_handle) as DEQP
where not DER.status in ('background','sleeping');
注:sys.dm_exec_query_plan是一个表值函数,它接收cross apply左侧的表传递的参数,每行记录计算一次,生成一个新表,然后与左表内连接. 下面链接解释的比较详细.
https://www.cnblogs.com/xbf321/archive/2011/08/14/apply-in-sql-server.html
cross apply更详细的解释,3种使用情况:
http://sqlhints.com/tag/examples-of-cross-apply/
我们查到有下面的2条活动请求的查询计划:
这里我在52号session中执行我们的查询,因此我们看看53号session. 如果使用SQL Server management studio的话,我们只需要点击查询计划的XML就可以可视化的查看查询计划.
获取活动查询的完成百分比
我们能从sys.dm_exec_requests中找到的非常有用一列信息是"完成百分比".比如,我想知道DBCC check现在执行到哪里了,我们基于它执行一个简单的查询获取所需的信息. 我们知道它是它是DBCC TABLE CHECK,下面是我的查询子句:
select session_id,start_time,status,database_id,percent_complete
from sys.dm_exec_requests
where command='DBCC TABLE CHECK';
我们看到现在完成了11%
很显然,这可以用来检查长查询的执行情况.
对指定的数据库获取所有活动请求
很多时候我们希望获取某一数据库上执行的所有操作.我们也可以是使用sys.dm_exec_requests来查询.这里我们连接sys.database使用数据库名来过滤.如果你已经知道数据库ID,你就不需要做这个join.你也可以使用DB_ID()这个函数,用来把数据库名翻译成数据库ID.然后,我还想知道谁连接了数据库,它是怎么连接的(使用什么应用连接的),我还需要连接sys.dm_exec_session.下面是我的查询,使用数据库名Test作为过滤条件.
select DER.session_id,DES.login_name,DES.program_name
from sys.dm_exec_requests as DER
join sys.databases as DB
on DER.database_id=DB.database_id
join sys.dm_exec_sessions as DES
on DER.session_id=DES.session_id
where DB.name='Test';
当我们执行这个查询的时候,我们可以获得下面2条活动会话:
因为这是针对sys.dm_exec_requests DMV的,我们知道这是针对Test数据库的.如果我们尝试针对特定数据库进行性能故障排除,这是一个好的突破方向.很显然,我们可以结合这个查询和上个查询获取实际的查询计划.
查看所有活动等待事件计数信息
有些时候我们诊断一个问题是,我们需要查询所有等待类型情况.我们也可以使用sys.dm_exec_requests,因为这个视图也显示了当前等待类型. 因此我们过滤掉后台任务或者sleeping任务时,我们可以了解到这些活动请求的等待情况,看看是否有什么问题.下面是查询:
select coalesce(wait_type,'None') as wait_type,count(*) as Total
from sys.dm_exec_requests
where not status in('Background','Sleeping')
group by wait_type
order by Total DESC;
下面是查询结果:
我们可以看到我们有2个LCK_M_S这种等待类型.这种等待类型是当我们等待获取共享锁时发生的等待.然后我们可以继续查询sys.dm_tran_locks来确定具体这个请求尝试获取的锁是什么.
select L.request_session_id,L.resource_type,
L.resource_subtype,L.request_mode,L.request_type
from sys.dm_tran_locks as L
join sys.dm_exec_requests as DER
on L.request_session_id=DER.session_id
where DER.wait_type='LCK_M_S';
然后我们获取到了这2个会话的全部信息列表:
故障排除方面我们还可以做更多,但是到此为止我们已经了解到了sys.dm_exec_requests的强大.