June 2, 2011 at 12:36 pm
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 12, Create Alter Drop
June 21, 2011 at 10:04 am
Thank you so much for this series.
I have a question about when an index is finished building. For this question, let's say we're talking about a table with no indexes that has 20 million rows at 300KB per row and there is no activity involving the table other than the index build. This is an actual use case, so please let me know if I need to add any details.
When an index is created on that table (clustered or non-clustered), the command returns to the client very quickly to say "Completed successfully". It can't possibly have built the index already, right? So, if that assertion is correct, how can I tell if a particular index has been completely built? Is there a DMV for that? Something in profiler?
I've tried searching for this, but I primarily get discussions on when to rebuild indexes. While that is an important topic, it's not what I'm looking for.
Phil Helmer
Database Engineer
February 17, 2012 at 2:36 am
phelmer (6/21/2011)
When an index is created on that table (clustered or non-clustered), the command returns to the client very quickly to say "Completed successfully". It can't possibly have built the index already, right? So, if that assertion is correct, how can I tell if a particular index has been completely built? Is there a DMV for that? Something in profiler?
When you get the 'command completed successfully', the index is completely built. It's not a process that can happen in the background
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2012 at 5:58 am
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.
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)?
February 17, 2012 at 7:03 am
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
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2012 at 10:01 am
Heh regarding the last point about losing the index if the rebuild fails having disabled first...
That did seem like the gist of the disabled explanation, to save disk space with a neat command.
But it's good to know the downside too! Thanks for pointing it out and explaining more fully the growth reasons in the files.
February 17, 2012 at 10:51 am
The other disadvantage (for nonclustered indexes) is that the index can't be used while the rebuild is in progress. Normally rebuilding a nonclustered index makes the table read-only, but the index is still seekable while its been rebuilt (because the old one isn't dropped until the new is complete). Disabling it first means that the index is gone and not usable until the rebuild finished.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 1:47 pm
Gail, thank you for the reply.
GilaMonster (2/17/2012)
Disabling it first means that the index is gone and not usable until the rebuild finished.
It also seems likely that you'd end up with query plans that were compiled during the "no index" time frame which would need to be re-compiled once the index rebuild went into effect.
Phil Helmer
Database Engineer
February 21, 2012 at 1:50 pm
phelmer (2/21/2012)
Gail, thank you for the reply.GilaMonster (2/17/2012)
Disabling it first means that the index is gone and not usable until the rebuild finished.It also seems likely that you'd end up with query plans that were compiled during the "no index" time frame which would need to be re-compiled once the index rebuild went into effect.
Rebuild would invalidate the plans anyway
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 3:06 pm
Ah, so it would be extra recompilation, but no manual cache management. Thank you. This is what happens when I don't run a Google search before posting. :blush:
Phil Helmer
Database Engineer
February 23, 2012 at 8:53 am
This is a great series. I've enjoy'ed it a great deal. But I'm having a problem getting my head around the paragraph on disabling an index:
The primary reason for disabling an index is to save disk space when rebuilding the index. If the index has not been disabled, the rebuild process maintains the original version of the index until the new version has been created; resulting in both versions consuming disk space at the same time. By deleting the index first; an entire index’s worth of disk space is saved during the rebuild process. Rebuilding an index that has been deleted typically requires about one fifth the disk space that rebuilding an undeleted index requires.
How can you rebuild something that has been deleted?
February 23, 2012 at 9:13 am
If the index is disabled, the metadata is still there. That's all that's needed to rebuild an index (rebuild actually creates an new index structure). If the index is actually deleted, then it can't be rebuild and would have to be completely recreated
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply