Good article! Really liked the fact that it clearly indicated problems and solutions (to include pros and cons). Thank you!
We recently migrated from SQL 7 to SQL 2000. In SQL 7 whenever a clustered index was rebuilt the NC would get rebuilt, but in SQL 2000 i do not see the NC being rebuilt. Atleast not in last 2 weeks of what i have seen. So is this an enhancement in SQL 2000 or does that mean the keys havn't changed to affect rebuild of NC?
Very good article and the dicussion is also providing lot of information.
We have our SQL databases on RAID sets on a SAN. I assume, therefore, that the disk defragmentation discussion is moot, since files on this hardware are fragmented by design. I wouldn't know where to begin to figure out which disks and how contiguous the files were.
Given that, one shouldn't spend too much time trying to optimize table or index storage either, other than having fillfactors that are consistent with transaction load, right?
Thanks
For file defragmentation, I heard indeed that Diskeep does a good job been able to defrag files in use and base on a schedule, never use it though.
Regarding
"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."
I use it on one of our production servers and during the process it made the tempdb inaccessible. Even if I try to stop the process the SQL Server was inaccessible. I had to restart it.
Diskeeper monitors the IO queue and throttles back when this rises so the hit to the machine is near nothing.
You could, essentially, run the defragger every hour. Why though...
