sql waits for sp/rpc/stmt completed and other events

Question

In SQL XE for sp/rpc/stmt completed events it will be great if we can include wait types like IO/Network waits etc. Just like we can see reads/cpu/duration, If we can also gets other resource waits, we can get a good idea why sql is slow during scenarios where the duration/CPU is high and reads are low.

Answer 1

You can actually track the wait statistics of a query but the other way around - by tracking the wait statistics themselves. Take a look at the following snippet and the result image:

CREATE EVENT SESSION [Wait Statistics] ON SERVER 
ADD EVENT sqlos.wait_info
(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
    WHERE (             
            opcode = N'End'
            AND duration > 0
         )
),
ADD EVENT sqlos.wait_info_external
(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
    WHERE ( 
            opcode = N'End'
            AND duration > 0
          )
) 

Here we capture the end of every wait (this is done because at this point SQL Server knows the duration of the statistic, so we can output it int he result) that has has duration greater than 0. In the ACTION part we retrieve the database name, the text of the query that caused the statistic and the session id of the query.

Beware though. Tracking wait statistics through Extended Events(and not through sys.dm_os_wait_stats that collects aggregate data) can generate a ton of data overwhelmingly fast. Should you choose this method, you should define very carefully which wait statistics you want to keep track of and what from what duration on the statistic causes you problem.