A cautionary tale
-
Hi folks,
not so much a question as a bit of a cautionary tale about database maintenance.
One of my users rang me today to say that his project finance report was freezing and producing no results. His colleague on a different project was apparently having no problems with the same report.
The report is based on very complex SQL statement which uses a view-based main select, and separate in-line sub-selects to generate the data for each of the 20 or so columns, and writes the results to a temporary table which contains half a dozen additional computed columns.
I captured his SQL using profiler and ran it in query analyzer, it took ten minutes to execute and fetch the data. Not good!
I removed a couple of the WHERE clause conditions on the main SELECT and managed to get it down to about 3 minutes. Still not good!
The code hadn't been changed since September, so my thoughts turned to the data. Some time ago I had a problem which I posted on this site where I 'lost' some data on the database. It was there, but not being returned in queries. To solve the problem I had written some maintenance scripts, which I had run successfully.
I dug out my scripts and ran a succession of
sp_updatestats,
sp_refreshview
and a final
EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''
Hurrah! The users' report now runs in less than 5 seconds, and I will be making sure that I run these maintenance commands every month before our financial period reports are due.
DLQ
If it ain't broke, don't fix it...
-
Thanks for the info - is often hard to explain to customers why such things can go wrong with seemingly no change at all... At least it wasn't database corruption!
I fixed a similar problem where everyone using the DB was timing out (coincidentally the morning after an SP4 upgrade) by reindexing every index in the database... Took 5 minute query back down the <1second it usually takes to run. 
-
If I have the window I rebuild indexes every night. I always update stats every night regardless ( even with auto update stats enabled ).
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
It sounds like there are a few items to add to your database build checklist under maintenance JOBs to create ... we call them site standards ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply