September 22, 2009 at 2:20 am
Hi all,
I have an issue with the amount of work tempdb is doing on one of my servers (based on the transactions/sec counter. The value of which equates to about 95% of my total transactions/sec.
I understand why this is happening (tempdb usage), and there is a lot of neglected and poorly written SQL being used in this environment and I was wondering if anyone had any tips that I could use to help determine the worst offending procedures or SQL statements. I've had a look around profiler looking for something that I could use to point me in the right direction, rather than having to investigate the procedures one by one.
Any idea's?
Your help would be most appreciated.
Thanks
September 22, 2009 at 2:23 am
You can set filters up on profiler and search for long running queries that way, or you can use some of the performance dashboard reports like performance top queries by Total IO for example.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 2:37 am
Thanks for the response.
I'm not sure if that will pick them up, for example I imagine that my issue is a large number of inline and small procedures using temp tables etc when they dont need to.
So I kind of want to say, profile anything where a temporary object is created or tempdb is used, but when I run a profile monitoring the db I'm not getting anything of value.
September 22, 2009 at 2:41 am
You could always go the other way, and look at it from a developer ppint of view. You could search the syscomments table and look for stored procedures that use cursors, or reference tempdb.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 2:44 am
infact scrub that.
I've managed to monitor the created objects on the tempdb and pull back some object id's.
Hopefully this will give me a little something to go on.
Thanks again.
September 22, 2009 at 2:47 am
np, glad to know it helped. let us know if you need any more help
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply