Post reply

Extended event - slow queries and waits

  • 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?

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 31, 2022 at 2:55 pm

    #4042118

    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)
    Intro to Tally Tables and Functions

  • June 1, 2022 at 5:48 am

    #4042448

    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!

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

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