We have an exceptionally busy server with many databases used by many applic
ations. One or more processes is causing tempdb to grow very rapidly and eat
up the disk.
I need suggestions on how to figure out which of the hundreds of processes m
ight be doing this. It's like trying to find a needle in a haystack. Is ther
e some way to filter a Profiler trace which might help me identify who the c
ulprit might be?
Thanks.Good question,
I don't think there is a direct way to do that in Profiler, but... if you
add some of hte AutoGrow events in database event class... and you're
tracking stmt:completed events... and looking at writes... you should be
able to see some type of correlation. Haven't messed around to look for the
particular model you need... but it's interesting. I'll take a look at see
if I find a nice way to do it...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Marie Ramos" <RamosMar@.saccounty.net> wrote in message
news:D4061B10-528B-42BE-A58F-BA6331F4D5F4@.microsoft.com...
quote:
> Help!
> We have an exceptionally busy server with many databases used by many
applications. One or more processes is causing tempdb to grow very rapidly
and eat up the disk.
quote:
> I need suggestions on how to figure out which of the hundreds of processes
might be doing this. It's like trying to find a needle in a haystack. Is
there some way to filter a Profiler trace which might help me identify who
the culprit might be?
quote:|||also... I'm not sure where the waittype would be... but I'm sure the spid's
> Thanks.
>
waiting on the autogrow to complete must be showing some sort of
distinguishing waittype in master..sysprocesses...
a little bit of testing should be able to help you figure out the waittype
while the spid is waiting for an autogrow to complete.... and that would
give you another way to narrow it down...
Brian
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:...
quote:
> Good question,
> I don't think there is a direct way to do that in Profiler, but... if you
> add some of hte AutoGrow events in database event class... and you're
> tracking stmt:completed events... and looking at writes... you should be
> able to see some type of correlation. Haven't messed around to look for
the
quote:
> particular model you need... but it's interesting. I'll take a look at
see
quote:|||Hi Marie.
> if I find a nice way to do it...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Marie Ramos" <RamosMar@.saccounty.net> wrote in message
> news:D4061B10-528B-42BE-A58F-BA6331F4D5F4@.microsoft.com...
> applications. One or more processes is causing tempdb to grow very rapidly
> and eat up the disk.
processes[QUOTE]
> might be doing this. It's like trying to find a needle in a haystack. Is
> there some way to filter a Profiler trace which might help me identify who
> the culprit might be?
>
I don't actually know of a conclusive set of profiler filters, perfmon
settings or sysprocesses columns that would give you what you're after per
se (hopefully someone does), but have you tried looking at the output of
sp_who2's DiskIO column?
This isn't going to give you precisely the answer you're after but if you
identify which processes are consuming large amounts of disk io & perhaps
relatively less cpu, you may be able to quickly eliminate many of the other
processes & concentrate on those with these crude characteristics.
Hopefully someone else can produce a more scientific method but this might
help you get started.
Microsoft runs a program called sqlwish for sql server which allows you to
submit requests for features. Your request for a feature to track which
process is causing tempdb to grow sounds a good one imo, as many would
benefit from such a feature - sqlwish@.microsoft.com.
Regards,
Greg Linwood
"Marie Ramos" <RamosMar@.saccounty.net> wrote in message
news:D4061B10-528B-42BE-A58F-BA6331F4D5F4@.microsoft.com...
quote:
> Help!
> We have an exceptionally busy server with many databases used by many
applications. One or more processes is causing tempdb to grow very rapidly
and eat up the disk.
quote:
> I need suggestions on how to figure out which of the hundreds of processes
might be doing this. It's like trying to find a needle in a haystack. Is
there some way to filter a Profiler trace which might help me identify who
the culprit might be?
quote:|||Thanks for the sp_who2 tip. I think this may be helpful.
> Thanks.
>
Do you know what the number in the Diskio column represents? If it's
zero, does that mean no disk io?
Marie
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi Marie.
Yep - eg select statements that are satisfied entirely by rows cached in
memory, therefore not needing to access the disk.
Regards,
Greg Linwood
"Marie Ramos" <ramosmar@.saccounty.net> wrote in message
news:O4MBKHc5DHA.2740@.TK2MSFTNGP09.phx.gbl...
quote:
> Thanks for the sp_who2 tip. I think this may be helpful.
> Do you know what the number in the Diskio column represents? If it's
> zero, does that mean no disk io?
> Marie
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
No comments:
Post a Comment