Catching slow queries with prepared statements

Question

I use extended events to identify slow queries, defined as queries taking more than 2 seconds to complete, like this:

CREATE EVENT SESSION SlowQueries ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
    (
    ACTION
        (
        sqlserver.client_hostname,
        sqlserver.sql_text,
        sqlserver.database_id,
        sqlserver.tsql_stack,
        sqlserver.username
        )
    WHERE 
        (
        duration > 2000000
        )
    ) 
ADD TARGET package0.asynchronous_file_target
    (
    SET filename='D:\SlowQueries.xel'
    )
WITH 
    (
    MAX_MEMORY=256 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    );

However, I noticed from the SQL Server Profiles that a PHP/Laravel application is not sending statements directly, but using RPC:Completed: they look like prepared statements, receiving the argument at run time. How do I expand my SlowQueries to include statements executed via RPC:Completed? I am 100% sure that slow queries are happening, but not reported by my event.

Answer 1

It's In There

Extended events has the rpc completed class:

SELECT * FROM sys.dm_xe_objects AS dxo WHERE dxo.name = 'rpc_completed';

If you need to show the parameters used, you may need to try one of these events:

CREATE EVENT SESSION what_do_i_get
    ON SERVER
    ADD EVENT sqlserver.module_end
    ( SET collect_statement = ( 1 )
WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.rpc_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sp_statement_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sql_batch_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sql_statement_completed
    ( WHERE ( sqlserver.session_id = ( 55 )))
    ADD TARGET package0.event_file
    ( SET filename = N'what_do_i_get' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = ON,
           STARTUP_STATE = OFF );
GO

The sql_text column won't help you though, you'll wanna use the statement column I believe, depending on how the statements are executed.

As a side note, you may want to be careful if you're using sp_prepare.

See my post here:

• Why sp_prepare Isn’t as “Good” as sp_executesql for Performance