Submit a ticketCall us

Quickly Address Software Vulnerabilities
Patch Manager is an intuitive patch management software which extends the capabilities of WSUS and SCCM to not only patch Windows® servers and workstations, and Microsoft® applications, but also other 3rd-party applications which are commonly exploited by hackers. Learn more about our patch management solution.

 

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