Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

 

 

 

Home > Success Center > Database Performance Analyzer (DPA) > Blocked DPA SQL server sessions

Blocked DPA SQL server sessions

Table of contents

Updated March 9, 2017

Overview

There is a blocked sessions metric in the Resources > Sessions tab for SQL server. This metric is not filtering some sessions that might be blocking while the quick poll query is filtering. This can lead to confusions as we see blocked sessions in the metric and then no blocking on the Blockers tab.

Environment

  • DPA versions 10.1.317 and earlier
  • Monitoring SQL server 

Detail

 

Here is the Queries for these two items. 

<metricsQuery className="com.confio.idc.database.metrics.domain.queries.SingleValueMetricsQuery">
    <sql>
    <![CDATA[
        SELECT COUNT(1) FROM master..sysprocesses AS s
        WHERE s.status<>'background'
        AND s.cmd<>'AWAITING COMMAND'
        AND s.blocked>0
        ]]></sql>
    <frequency>60</frequency>
    <queryTimeout>20</queryTimeout>
    <name>Blocked Sessions</name>
    <supportedDatabaseList>
      <supportedDatabase>
        <databaseType>SQL Server</databaseType>
      </supportedDatabase>
    </supportedDatabaseList>
</metricsQuery>



Here is the quick poll query 

SELECT 
s.loginame
,db_name(s.dbid) name
, s.hostname
, s.program_name
, s.sql_handle
, s.stmt_start
, s.stmt_end
, s.spid, CONVERT(smallint, s.waittype) waittype
, s.lastwaittype
, s.ecid
, s.waittime
, CONVERT(varchar(64), s.context_info) context_info
, RTRIM(r.wait_resource) waitresource
, s.blocked 
, r.plan_handle
, r.statement_start_offset
, r.statement_end_offset
, r.start_time
, q.plan_generation_num 
FROM master..sysprocesses AS s WITH(NOLOCK) 
LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) 
ON r.session_id = s.spid LEFT OUTER JOIN 
sys.dm_exec_query_stats q WITH(NOLOCK) ON q.plan_handle = r.plan_handle 
AND q.statement_start_offset = r.statement_start_offset 
AND q.statement_end_offset = r.statement_end_offset 
WHERE (s.dbid<>0 AND s.cmd<>'AWAITING COMMAND' 
AND s.cmd NOT LIKE '%BACKUP%' 
AND s.cmd NOT LIKE '%RESTORE%' 
AND s.cmd NOT LIKE 'FG MONITOR%' 
AND s.hostprocess > '' AND s.spid>50 
AND s.spid<>@@SPID)  
AND lastwaittype 
NOT IN ('BROKER_RECEIVE_WAITFOR', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'TRACEWRITE', 'WAITFOR')

 

You can see that the Quick poll query that is what is feeding the data for the blockers tab is filtering items in the where clauses that are not filtered by the metric. 

 

 

Last modified
23:20, 8 Mar 2017

Tags

Classifications

Public