Charles Tsang (2/17/2012)
Something worth keeping in mind is that Index reorganization uses the database's transaction log file as a working space.
Rebuilding it allows the option of using the TempDB as working space for the sorts instead.
Not really. The transaction log is not working space, it's just where the changes made get written. Index reorganise is fully logged in all recovery models, Index rebuilds are minimally logged in bulk-logged and simple recovery. Index rebuild requires free space = size of index (+20% if not using Sort In TempDB), reorganise needs a single 8kb page free iirc.
I'm intrigued by the disable option on non-clustered indexes as to it's effect on working space required when doing a follow on reorganization or rebuild. Will it therefore lessen the disk requirements (whether in the TempDB or Transaction log file for a rebuild and reorganization respectively)?
What, disable and then rebuild? It won't reduce the working space for the sort (in TempDB if Sort In TempDB is set, in the user database otherwise), it won't reduce the logging impact.
It'll reduce the space needed in the data file because normally rebuild keeps the old index around until the end of the rebuild. I wouldn't recommend it as a general practice though, if the rebuild fails for whatever reason you're left without an index
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass