March 30, 2006 at 4:59 am
We are running 2000 with a RAID 5 disk configuration.
We are planning to do a DBReIndex and where also think ing about doing a defrag of the main data disk. However our server guy debates the benefit of this on RAID 5(together with all th caching etc.. on such a ssytem). Has anyone proven the benefiot of a defrag on RAID 5, and if it is wrthwhile doing should we do it before or after the dbreindex?
Thanks Jeet
March 30, 2006 at 7:37 am
Well, I've had substantial speed improvements on RAID1 disks, so I see no reason why RAID5 shouldn't benefit as well, even with caching. Granted, caching will reduce the effects of poor disk layout, but removing fragmentation will mean that the data can be read into disk cache more efficiently in the first place, which has to be a good thing.
I suggest the following sequence for defragging:
1. Backup databases
2. Shrink databases
3. Shutdown the dataserver
4. Run DiskDefragmenter to defragment the data files at the OS level. (repeat as many times as necessary)
5. Restart the dataserver
6. Extend the database(s) to ensure a suitable amount of free space exists to prevent early autogrowth.
7. Run DBCC INDEXDEFRAG to correct internal table fragmentation.
March 30, 2006 at 9:19 pm
Raid Configuration has nothing to do with defragmentation of data per say. If you understand how your index can become fragmented you can see that RAID level does not negates that. The less the ram the worse the prolem. If you have 64 gig or ram and a 2 gig database the problem is likley less severe than a 10 gig database and 1 gig of ram. anyhow, to defragment you really need to rebuild indexes. Especially clustered indexes, that IS your data. Lots up inserts or deletes can casue page breaks and forward chaining. Varchar fields make it even worse if those undergo updates once written to disk. SQL Server requests IO blocks and it may not get them in a contigous manner. That is a different kind of fragmentation. It si at the file level. So Index fragmentation can be dealt with by rebuilds and disk fragmentation can be dealt with defrag with SQL Server stopped.
Thre are some cases where defraging may not help or may hurt but generally rebuild indexes and defrag regardless of RAID. Infact, RAID 5 has to write paridy data so it writes more. I suspet fragmentation is worse on a RAID 5 given the formula for calculating Disk IO by RAID level. Ther eis no way that it can be anything but worse on a RAID 5 segment. As per my analysis, some may differ.
Oh, I am the server guy too. 36 of them. Defragment and rebuild indexes.
Good luck!
April 3, 2006 at 7:51 am
It's really a case of housekeeping. ntfs fragmentation is best avoided regardless of raid or memory. If you don't keep on top of fragmentation ( ntfs ) there's always the risk of messing up the mtf . ntfs fragmentation affects read ahead ( scans ) it has little imapct on random i/o but can seriously impact sequential i/o.
I work on the basis that what you did for 6.0 still works best !! W2003 can manage on-line defrag - assuming you don't have too much user activity on your databases.
I'd usually do the index rebuilds prior to ntfs fragmentation. Never shrink a mdf file after an index rebuild.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 4, 2006 at 3:40 am
Guys,
Thanks for the advice, and will prbably now do a defrag and dbreindex.
One final question, the concensus seems to be to Redbuild indexes first and then defrag. Is there any particular reason for this?
Jeet
Thanks Jeet
April 4, 2006 at 9:19 am
Actually, unless I've misread some of the other replies, I think the concensus is (just) to defrag first, then rebuild indexes.
I've never found any formal documentation to support this, but the reason I suggest this approach is that it makes little sense to me to get all your extents in nice, sequential order if the underlying file structure is fragmented. You're then going to re-arrange those file(s) and copy bits of them here, there and everywhere before finally ending up with a less-fragmented layout. This could mean that your extents are no longer contiguous.
I'd agree with the advice "never shrink a file after an index rebuild". Indeed, why shrink a file at all unless you're desperate for disk space? Once you've done all your ntfs defragging and index defragging, extend your database to be as big as you can afford, and then turn autoshrink OFF. Repeated autoshrink / autoextend is what causes the underlying files to fragment in the first place.
April 4, 2006 at 10:14 am
Many thanks to everyone for your help.
Thanks Jeet
April 4, 2006 at 10:54 am
Actually I would consider the Index rebuild first and then the file system defrag. The reason is you are going to creqate additional fragmentation in the rebuild process since the files will expand (if there isn't enough room) to do the rebuild (especially on the clustered indexes). I would then update stats (sp_update stats) and usage (DBCC UPDATEUSAGE). Do all internal housekeeping before external simple for (as I stated) the fact these will further fragment the physical files. The defrag in an of itself doesn't show a significant impact as long as you don't have a significant amount of fragmentation, and you have to keep in mind you cannot defrag open databases and may need to stop SQL server to get a complete defrag.
April 7, 2006 at 8:51 pm
I am curious if you did it - which did you do first? Did it seem to make any significant difference in performance?
April 10, 2006 at 4:17 am
I plan to do it in live soon, however in our test system we did the dereindex first and this dropped the nightly DBCCS by 20%, the defrag made no visible improvement
Thanks Jeet
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply