Extended event - slow queries and waits

Question

Post reply

Extended event - slow queries and waits

Peter Kruis

Mr or Mrs. 500

Points: 578

May 30, 2022 at 6:20 pm

Go to Answer

#4041682

I have an extended event which filters on my slow queries. I have created the following script for it.

CREATE EVENT SESSION [SlowQueriesAndStatementsLargerThan3Seconds] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([duration]>=(3000000))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)
    WHERE ([duration]>=(3000000))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([duration]>=(3000000)))
ADD TARGET package0.event_file(SET filename=N'SlowQueriesAndStatementsLargerThan3Seconds'),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

I would like to include where my query is waiting for when it is being slow. I would like to include the wait_info for it, however when I add this, my trace fills up with unrelated waits for other queries.

Is it possible to have the waits included for only the queries which ran for in this case 3 seconds or longer?

Grant Fritchey

SSC Guru

Points: 398882
More actions
May 31, 2022 at 2:34 pm
Answer
#4042114

You would need some different filtering on the waits. You can't simply correlate them through causality tracking and then only capture the related waits. Instead, you also need to filter there so you're not overwhelmed with additional info. Minimum would be to go for the database, that would at least make it so you're only looking at one set of data. You'd want to think about other ways to filter here too. Query_hash, stuff like that might be helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

As a bit of a sidebar, your slowest queries are frequently NOT your worst queries. Rather it's usually some fairly small stuff that gets called thousands of times per hour. Another example is a query that we were running a whole lot that was "only" taking a 100ms... people were complaining about long wait times on the front end. It turned out that 100ms query was not properly parameterized and was taking anywhere from 2 to 22 seconds to recompile with an average of about 20 seconds and, because of the lack of proper paramterization, was recompiling EVERY time it ran.

We fixed the parameterization issue, which made the recompiles simply disappear and tweaked to code to come in at about 10ms which also seriously reduced contention on the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Peter Kruis Mr or Mrs. 500 Points: 578 More actions · Answer 2

Thanks Grant, I will look into different filters. Good to know it is not possible for all slow queries, however when I have one, I should be able to focus on that specific query.

Thanks Jeff for the additional information!