Submit a ticketCall us

Systems Monitoring for Dummies
Our new eBook will teach you the fundamentals and help you create monitors and alerts that are effective, meaningful, and actionable. Monitoring is more than a checkbox on your to-do list. This free eBook will give you practical advice to help you succeed in all aspects of monitoring – discovery, alerting, remediation, and troubleshooting. Don’t miss out on this indispensable resource for newbies, experienced IT pros, and everyone in between. Register Now.

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

Tags

Classifications

Public