Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

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