Post reply

help on extended event session

  • February 14, 2023 at 7:59 am

    Go to Answer

    #4149377

    Hi All,

    Is there a way to track page splits occuring at what time and what DML(insert/update/del) operation is causing the bad page split ?

    Want to know whether it is randam insert guid is causing the problem or variable length column update is causing the problem or AG 14 bytes tag etc...

    We are checking overall page splits filtering on database id and want to go more granular.

    CREATE EVENT SESSION [TrackPageSplits] ON SERVER

    ADD EVENT sqlserver.transaction_log(

    WHERE ([operation]=(11))) --LOP_DELETE

    ADD TARGET package0.event_file(SET filename=N'd:\xEvents\TrackPageSplits.xel')

    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=OFF,STARTUP_STATE=OFF)

    GO

    Any help or ideas will be of great help.

    Thanks,

    Sam

  • February 14, 2023 at 1:10 pm

    Answer

    #4149490

    I'd look at this article by Jonathan Kehayias. I think it covers what you're looking for. In general, you may not have a direct, "HERE IS THE ONE PIECE OF DATA CAUSING A SPLIT", but instead will have to identify that, yeah, lots of splits on this index, and that index is structured such and such, and the queries are doing so & so, which means our cluster keys (or heap I guess) lend themselves to splits. We have to.... whatever that is. Restructure, change the fill factor, something.

    "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

  • February 14, 2023 at 2:56 pm

    #4149546

    vsamantha35 wrote:

    Want to know whether it is randam insert guid is causing the problem

    If it is and you're concerned about the page splits (I usually am because they can cause some serious log file usage and blockin) and page density issues, please watch the following hour and 20 minute video to SOLVE that problem.  I'll give you a hint... if you're doing ROWSTORE Index Maintenance that uses REORGANIZE, you have created and perpetuated the page-split problem with Random GUIDs and other types of indexes.

    https://www.youtube.com/watch?v=rvZwMNJxqVo

    What I want to point out is that the video is NOT just about Random GUIDs... I just use those because they're the "Poster Child" for fragmentation.  There's a huge amount of information about other things especially about how following supposed "Best Practice" Index Maintenance is not a "Best Practice", was never meant to be taken as a "Best Practice", and is actually a WORST Practice for most of your indexes.

    Beware the sudden an loud advertisements they inserted at timestamps 15:00, 30:15, and 45:25.  They will make you throw your headset across the room.

    --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)

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

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