November 14, 2008 at 8:49 am
Looking at a customers production database - 50gb, 8+ years old, currently running on SQL 2005. Never seen a SQL DBA or maintenance plan!!! As you can imagine, our remit is to "speed it up"! Main areas:
win2k3 enterprise 64 sp2.
D and E drives nearly full (couple of gb on each). Both heavily fragmented (along with C drive), RAID5 with 3 partitions
SQL 2005 enterprise 64 still RTM
16bg ram, same swap file.
quad processors, often 90%+ utilised
DB is set to 2000 compatability (yet to verify if for a reason)
Over 400 index's, majority 95% fragmented (40+mins to reindex one on a test box)
Huge amount of data archiving needs to be done (eg 15mill rows in one table, i can delete 5mill asap, but worried about it hitting the log with little space)
Where would you start?? (btw, its required 7 days a week...)
November 14, 2008 at 9:51 am
First - is everything backed up properly & frequently ?
November 14, 2008 at 9:56 am
😀 yep! Nightly, and logs shipped to another server hourly - about the only thing they have done!
November 14, 2008 at 10:06 am
I'd be tempted to start with correcting the compatibility level, then re-indexing, one index at a time. It has the potential for immediate payback.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 17, 2008 at 4:28 am
Alvin Ramard (11/14/2008)
I'd be tempted to start with correcting the compatibility level, then re-indexing, one index at a time. It has the potential for immediate payback.
My problem is there could be some legacy app that uses this. Someone in their IT "thinks" this but isnt sure...
Testing some of the reindex statements atm - with over 500 its a slow job (On a test server, its done 12 in an hour!) :ermm:
November 19, 2008 at 7:34 am
I'm not sure if there are any problems with disk defragmentation and SQL Server, but if there aren't, then I might consider tackling the disk fragmentation before the index defrag. Unless you can GET contiguous space for the index, rebuilding the indexes might not result in any worthwhile improvements, as the physical file might be so fragmented that index fragementation will just be replaced by file fragmentation as a source for a problem. Can there be signficant down time? If not, you might have no viable alternative but to seriously consider a hardware swap, via creating a brand new server and migrating data, then decommissioning the old server. One alternative might be to introduce significant additional data storage, and thus take the opportunity to design the storage subsystem with more consideration for the realistic needs. This would only require a detach and reattach of the database, and the moving of the physical files in between.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply