Sql-server – Need to setup a sql alert on “waiting tasks” – similar to “waiting tasks” in Activity Monitor

alertsperfmonsql serversql-server-agentt-sql

My manager has asked me to work upon a request. I am not sure if it is reasonable or weird but he wants me to setup an alert based on the "waiting task" that we see in the activity monitor. Basically, if the value in the "waiting task" goes above 10 then we need to be alerted upon. I am not sure as to how I would replicate the same information that we see in Activity monitor as this seems to be live data. Closest DMV and the column I could think of is "waiting_tasks_count" in sys.dm_os_wait_stats. However, this does not seem to be in line with what he is looking for and the column consists of cumulative values over a period of time since last time SQL was restarted. Can someone shed some light on where I can find a query or a counter that I can alert upon?

I have thought about a query at this time but I think I can't calculate the delta correctly it always shows me 1. Can someone help me out exactly where I am going wrong here.

select  so1.wait_type,
        so1.waiting_tasks_count as no_of_waits,
        so1.waiting_tasks_count - so2.waiting_tasks_count as delta_wait_count,
        so1.signal_wait_time_ms,
        so1.wait_time_ms - so2.signal_wait_time_ms as resource_wait_time,
        getdate() as sample_time
from  sys.dm_os_wait_stats as so1
inner join sys.dm_os_wait_stats as so2
on so2.waiting_tasks_count = so1.waiting_tasks_count - 1

Best Answer

you can trace out the exact query in profiler..below is the query you are referring to

WITH profiled_sessions as (
    SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT 
   [Session ID]    = s.session_id, 
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [Login]         = s.login_name,   
   [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end, 
   [Task State]    = ISNULL(t.task_state, N''), 
   [Command]       = ISNULL(r.command, N''), 
   [Application]   = ISNULL(s.program_name, N''), 
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''), 
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  = 
        CASE 
            -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
            WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' 
            -- session is either not blocking someone, or is blocking someone but is blocked by another party
            ELSE ''
        END, 
   [Total CPU (ms)] = s.cpu_time, 
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024, 
   [Memory Use (KB)]  = s.memory_usage * (8192 / 1024), 
   [Open Transactions] = ISNULL(r.open_transaction_count,0), 
   [Login Time]    = s.login_time, 
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''), 
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = ISNULL(g.name, N''),
   [Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN 
(
    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as 
    -- waiting for several different threads.  This will cause that thread to show up in multiple rows 
    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread, 
    -- and use it as representative of the other wait relationships this thread is involved in. 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks 
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;

Related Solutions

Sql-server – Permanent count of waiting tasks (2) in Activity monitor

Apart from Resources waits tab i believe you should expand the tab above which says processes.The Processes pane shows the information about the currently running processes on the SQL databases, who runs them, and from which application.

Also, you can simultaneously use below script to find the info on whats running and causing waits thereby:

SELECT 
      [spid] = session_Id
    , ecid
    , [blockedBy] = blocking_session_id 
    , [database] = DB_NAME(sp.dbid)
    , [user] = nt_username
    , [status] = er.status
    , [wait] = wait_type
    , [current stmt] = 
        SUBSTRING (
            qt.text, 
            er.statement_start_offset/2,
            (CASE 
                WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)  
                ELSE er.statement_end_offset 
            END - er.statement_start_offset)/2)
    ,[current batch] = qt.text
    , reads
    , logical_reads
    , cpu
    , [time elapsed (ms)] = DATEDIFF(mi, start_time,getdate())
    , program = program_name
    , hostname
    --, nt_domain
    , start_time
    , qt.objectid
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              -- Ignore system spids.
AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
ORDER BY 1, 2
GO

SQL Server – How to Set Up Data Deletion Alerts

You can use SQL Server Change Data Capture or Change Tracking features to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

To get the changed records for a table and information about these changes, use the CHANGETABLE(CHANGES…) function.

Drawback: is that you have to query the tables periodically and then send the notification email

Create triggers on a table that needs auditing. Triggers will be fired when a record is inserted, deleted or updated and msdb.dbo.sp_send_dbmail should be used to send an email alert to a specified email recipient, immediately:

e.g.

CREATE TRIGGER t_Pers ON Person.Person 
AFTER INSERT, UPDATE, DELETE 
AS EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'Profie name', 
@recipients = 'abc.xy.com' , 
@body = 'Data in AdventureWorks2012 is changed', 
@subject = 'Your records have been changed' 
GO

As triggers are fired per batch, not per row, it should be ensured that all batch transactions are captured, and no data is lost.

Therefore as mentioned above both the above solutions are very much helpful, but depends upon how you want to use them and get benefited.

In addition, please read Tracking Data Changes from MSDN and Performance Tuning SQL Server Change Tracking

The method which i have been using for such scenarios is analyzing the changes from default trace file which tracks object level changes using the below query as explained in this great article:The default trace in SQL Server - the power of performance and security auditing

    SELECT  TE.name ,
    v.subclass_name ,
    DB_NAME(t.DatabaseId) AS DBName ,
    T.NTDomainName ,
    t.NTUserName ,
    t.HostName ,
    t.ApplicationName ,
    t.LoginName ,
    t.Duration ,
    t.StartTime ,
    t.ObjectName ,
    CASE t.ObjectType
      WHEN 8259 THEN 'Check Constraint'
      WHEN 8260 THEN 'Default (constraint or standalone)'
      WHEN 8262 THEN 'Foreign-key Constraint'
      WHEN 8272 THEN 'Stored Procedure'
      WHEN 8274 THEN 'Rule'
      WHEN 8275 THEN 'System Table'
      WHEN 8276 THEN 'Trigger on Server'
      WHEN 8277 THEN '(User-defined) Table'
      WHEN 8278 THEN 'View'
      WHEN 8280 THEN 'Extended Stored Procedure'
      WHEN 16724 THEN 'CLR Trigger'
      WHEN 16964 THEN 'Database'
      WHEN 16975 THEN 'Object'
      WHEN 17222 THEN 'FullText Catalog'
      WHEN 17232 THEN 'CLR Stored Procedure'
      WHEN 17235 THEN 'Schema'
      WHEN 17475 THEN 'Credential'
      WHEN 17491 THEN 'DDL Event'
      WHEN 17741 THEN 'Management Event'
      WHEN 17747 THEN 'Security Event'
      WHEN 17749 THEN 'User Event'
      WHEN 17985 THEN 'CLR Aggregate Function'
      WHEN 17993 THEN 'Inline Table-valued SQL Function'
      WHEN 18000 THEN 'Partition Function'
      WHEN 18002 THEN 'Replication Filter Procedure'
      WHEN 18004 THEN 'Table-valued SQL Function'
      WHEN 18259 THEN 'Server Role'
      WHEN 18263 THEN 'Microsoft Windows Group'
      WHEN 19265 THEN 'Asymmetric Key'
      WHEN 19277 THEN 'Master Key'
      WHEN 19280 THEN 'Primary Key'
      WHEN 19283 THEN 'ObfusKey'
      WHEN 19521 THEN 'Asymmetric Key Login'
      WHEN 19523 THEN 'Certificate Login'
      WHEN 19538 THEN 'Role'
      WHEN 19539 THEN 'SQL Login'
      WHEN 19543 THEN 'Windows Login'
      WHEN 20034 THEN 'Remote Service Binding'
      WHEN 20036 THEN 'Event Notification on Database'
      WHEN 20037 THEN 'Event Notification'
      WHEN 20038 THEN 'Scalar SQL Function'
      WHEN 20047 THEN 'Event Notification on Object'
      WHEN 20051 THEN 'Synonym'
      WHEN 20549 THEN 'End Point'
      WHEN 20801 THEN 'Adhoc Queries which may be cached'
      WHEN 20816 THEN 'Prepared Queries which may be cached'
      WHEN 20819 THEN 'Service Broker Service Queue'
      WHEN 20821 THEN 'Unique Constraint'
      WHEN 21057 THEN 'Application Role'
      WHEN 21059 THEN 'Certificate'
      WHEN 21075 THEN 'Server'
      WHEN 21076 THEN 'Transact-SQL Trigger'
      WHEN 21313 THEN 'Assembly'
      WHEN 21318 THEN 'CLR Scalar Function'
      WHEN 21321 THEN 'Inline scalar SQL Function'
      WHEN 21328 THEN 'Partition Scheme'
      WHEN 21333 THEN 'User'
      WHEN 21571 THEN 'Service Broker Service Contract'
      WHEN 21572 THEN 'Trigger on Database'
      WHEN 21574 THEN 'CLR Table-valued Function'
      WHEN 21577
      THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
      WHEN 21581 THEN 'Service Broker Message Type'
      WHEN 21586 THEN 'Service Broker Route'
      WHEN 21587 THEN 'Statistics'
      WHEN 21825 THEN 'User'
      WHEN 21827 THEN 'User'
      WHEN 21831 THEN 'User'
      WHEN 21843 THEN 'User'
      WHEN 21847 THEN 'User'
      WHEN 22099 THEN 'Service Broker Service'
      WHEN 22601 THEN 'Index'
      WHEN 22604 THEN 'Certificate Login'
      WHEN 22611 THEN 'XMLSchema'
      WHEN 22868 THEN 'Type'
      ELSE 'Hmmm???'
    END AS ObjectType
 FROM    [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                        value
                                                FROM    [fn_trace_getinfo](NULL)
                                                WHERE   [property] = 2
                                              )), DEFAULT) T
    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
    JOIN sys.trace_subclass_values v ON v.trace_event_id =   TE.trace_event_id
                                        AND v.subclass_value = t.EventSubClass
      WHERE   TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )
            -- filter statistics created by SQL server                                         
    AND t.ObjectType NOT IN ( 21587 )
            -- filter tempdb objects
    AND DatabaseID <> 2
            -- get only events in the past 24 hours
    AND StartTime > DATEADD(HH, -24, GETDATE())
   ORDERBY t.StartTime DESC ;

Best Answer

Related Solutions

Sql-server – Permanent count of waiting tasks (2) in Activity monitor

SQL Server – How to Set Up Data Deletion Alerts

Related Question