<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by David Durant
»
Stairway to SQL Server Indexes: Step 12,...
12 posts, Page 1 of 2
1
2
»»
Stairway to SQL Server Indexes: Step 12, Create Alter Drop
Rate Topic
Display Mode
Topic Options
Author
Message
David Durant
David Durant
Posted Thursday, June 02, 2011 12:36 PM
Forum 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
phelmer
phelmer
Posted Tuesday, June 21, 2011 10:04 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 278,
Visits: 431
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
GilaMonster
GilaMonster
Posted Friday, February 17, 2012 2:36 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 37,682,
Visits: 29,937
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
Charles Tsang
Charles Tsang
Posted Friday, February 17, 2012 5:58 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, January 25, 2013 4:49 AM
Points: 6,
Visits: 264
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
GilaMonster
GilaMonster
Posted Friday, February 17, 2012 7:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 37,682,
Visits: 29,937
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
Charles Tsang
Charles Tsang
Posted Friday, February 17, 2012 10:01 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, January 25, 2013 4:49 AM
Points: 6,
Visits: 264
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
GilaMonster
GilaMonster
Posted Friday, February 17, 2012 10:51 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 37,682,
Visits: 29,937
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
phelmer
phelmer
Posted Tuesday, February 21, 2012 1:47 PM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 278,
Visits: 431
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
GilaMonster
GilaMonster
Posted Tuesday, February 21, 2012 1:50 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 37,682,
Visits: 29,937
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
phelmer
phelmer
Posted Tuesday, February 21, 2012 3:06 PM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 278,
Visits: 431
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 »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
