Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jake Shelton
Yak Posting Veteran
74 Posts |
 Posted - 2012-06-22 : 20:29:23
|
| Hi I know this is a well-established procedure in SQL, but are there any drawbacks to doing it? Any Gotcha!'s ??Thanks,Jake |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-22 : 21:53:26
|
| Yes, lots of drawbacks for very little improvement, if any. Biggest drawback is the pain that you cause while performing the work, second is the transaction log. There is only a specific case where it would help things, and it's rare to hit that situation. The case is an ordered scan.You should do a test to see if you even need to defragment, there's a very high chance that it'll provide no benefit and yet cause lots of pain.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-06-23 : 05:21:25
|
| I should have added that this is for an SS2K system. At first I thought you were being facetious throughout the entire post. :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-24 : 18:18:48
|
Your other post about the enormous tlog and out of sync log shipping is an example of the pain I was referring.Same answer for SQL 2000. Defragment/rebuilding/reorganizing rarely provides any benefit, yet it causes so much pain. My advice is to not do it and instead ensure you have an update stats job in place. And if later you determine you need to do it, do it manually rather than through a scheduled job.No facetious here, just got out of a week of training with a SQL Server expert and that was one of the topics we discussed at great length.  Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
Defragmenting Indexes