Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to SQL Server Indexes: Step 12, Create Alter Drop Expand / Collapse
Author
Message
Posted Thursday, June 2, 2011 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 12, Create Alter Drop
Post #1118979
Posted Tuesday, June 21, 2011 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 3, 2014 11:15 AM
Points: 283, Visits: 473
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.
Post #1129107
Posted Friday, February 17, 2012 2:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 42,422, Visits: 35,483
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 2008, MVP
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

Post #1253686
Posted Friday, February 17, 2012 5:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 6:00 AM
Points: 6, Visits: 271
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)?
Post #1253793
Posted Friday, February 17, 2012 7:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 42,422, Visits: 35,483
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 2008, MVP
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

Post #1253833
Posted Friday, February 17, 2012 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 6:00 AM
Points: 6, Visits: 271
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.
Post #1254012
Posted Friday, February 17, 2012 10:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 42,422, Visits: 35,483
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 2008, MVP
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

Post #1254039
Posted Tuesday, February 21, 2012 1:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 3, 2014 11:15 AM
Points: 283, Visits: 473
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.
Post #1255558
Posted Tuesday, February 21, 2012 1:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 42,422, Visits: 35,483
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 2008, MVP
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

Post #1255563
Posted Tuesday, February 21, 2012 3:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 3, 2014 11:15 AM
Points: 283, Visits: 473
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.
Post #1255628
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse