Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Indexes: Step 12, Create Alter Drop


Stairway to SQL Server Indexes: Step 12, Create Alter Drop

Author
Message
David Durant
David Durant
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 12, Create Alter Drop
phelmer
phelmer
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 531
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47408 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Charles Tsang
Charles Tsang
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 285
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)?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47408 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Charles Tsang
Charles Tsang
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 285
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47408 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


phelmer
phelmer
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 531
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47408 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


phelmer
phelmer
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 531
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search