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.