April 22, 2008 at 7:43 am
My tempdb size has exceeded 60 GB and is causing a SQL job to fail after 4 hrs.
I want to run a trace to identify statements with poor execution plans, high no. of reads, that could be causing this, so I can tune them.
What are the best events/columns I should be choosing in Profiler?
Also, should I be restarting the SQL instance to refresh the tempdb space after the failure?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 22, 2008 at 1:08 pm
you'll find it's not easy to profile against tempdb. Most likely it's index rebuilds and checkdb commands. Or you have lots of processes creating and populating tempdb. One extreme way to find out is to limit tempdb growth to 2gb and find out what breaks!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 22, 2008 at 1:54 pm
Thanks for the reply.
We actually found the culprit.
It was a monster execution plan that at the point of failure registered over a billion logical reads in Profiler.
We were able to isolate and fix the problem by a small re-arrangement of the query.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 23, 2008 at 12:41 pm
only a billion? I have a plan to post to my blog that actually overflowed an int!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 23, 2008 at 12:44 pm
colin Leversuch-Roberts (4/23/2008)
only a billion? I have a plan to post to my blog that actually overflowed an int!
...and I thought I had seen it all! :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 23, 2008 at 1:05 pm
yeah me too but here I am 16 years on and to be honest nothing surprises me now. What I do find surpising is that millions of io seem to mean nothing to many. I recorded a set of reports on our prod system today, approx 200 reports over a 8 hour period, together they generated 5,400 million page reads - I can't seem to be able to get over the fact that 75 iterations of a report at nearly 750k reads each is not good. Mind until yesterday I'd not actually seen a billion io query before.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply