Post reply

track tempdb allocations using Extended events

  • May 26, 2017 at 1:33 am

    #325936

    Hi SQL experts,

    How can we track queries/(which stored proc & sql stmt within proc) causing tempdb to grow using extended events?

    For example, I have a stored proc and sql stmt which createing a temp tbl/doing heavy sort operation which is taking up more than 100MB space in Tempb, so I want to track all such stored procs and sql stmts within those stored procs.

    How can we accomplish it?

    Thanks in advance.

    -Sam

  • May 26, 2017 at 12:41 pm

    #1944382

    You might want to take a look at this Event session definition on Brent Ozar's site - it does what you are looking for:
    Tracking tempdb growth using Extended Events

    Sue

  • May 27, 2017 at 5:54 am

    #1944408

    Thanks Sue. However, I dont want to track the autogrowths of tempdb database but I want to track the tempdb allocations per query so that I can try optimizing queries taking up most space inside tempdb (Temp tbl creations , huge sorts etc....)

  • May 27, 2017 at 10:59 am

    #1944427

    vsamantha35 - Saturday, May 27, 2017 5:54 AM

    Thanks Sue. However, I dont want to track the autogrowths of tempdb database but I want to track the tempdb allocations per query so that I can try optimizing queries taking up most space inside tempdb (Temp tbl creations , huge sorts etc....)

    Sorry about that - thought that's what you meant with what was causing tempdb to grow.
    I haven't tried to track tempdb usage per query with extended events - just growth, spills, sort warnings, etc. I've only done allocations through polling DMVs or query usage by searching the plans in cache. 

    Sue

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

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