Submit a ticketCall us

WebinarUpcoming Webinar: Know What’s Changed – with NEW Server Configuration Monitor

Change management in IT is critical. But, even with a good change management process, changes are too often not correctly tracked, if at all. The configuration of your servers and applications is a key factor in their performance, availability, and security. Many incidents can be tracked back to an authorized (and sometimes unauthorized) configuration change, whether to a system file, configuration file, or Windows® Registry entry. Join SolarWinds VP of product management Brandon Shopp to discover how the new SolarWinds® Server Configuration Monitor is designed to help you.

Register now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > 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 all versions.
  • 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