March 12, 2009 at 12:00 pm
Hi,
I am looking for a script which runs a trace(scheduled script) which will track the queries that are causing tempdb to grow. Pls advice..
March 12, 2009 at 3:14 pm
I can't think of any good way to do this. You could profile all statements over a period and parse them for temp table/variable usage, and you could profile the execution plans and parse them for tempdb-utilising operators like hash joins etc, but neither method is going to tell you which statements force the most tempdb usage.
Ultimately it may be growing for other reasons - triggers, optimistic locking etc. I would probably start by defining the period when most growth occurs, and then isolating the statements that run in that period.
March 13, 2009 at 6:45 am
In theory, you could just setup a trace that includes all sql statement starting and completed events making sure you include the EventSequence column and then merge that trace data with the trace data from the Default Trace (includes Auto Grow events) ordered by EventSequence and that should show the sql statements surrounding the growth events. I'm sure you would have to do some digging to find out exactly which SQL Statements are likely to cause it, but it would give you an idea. If I were to do this I'd probably do a single database at a time to reduce the load on the server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply