I noticed that the a.text/Query column contains: insert [dbo].[Table] select * from [dbo].[Table]
Why is that? Does the index creation literally re-insert every row in the table into itself?
The answer to your question is, yes... for anything over 128 extents (which is only 1,024 pages or 8MB), the original object is preserved as is and a brand new copy of the object is created with the new index in place. That means that it copies every bloody row and, in the FULL Recovery Model, it's a fully logged evolution. It's minimally logged in the BULK LOGGED and SIMPLE recovery models.
The problem with creating a new index (Clustered or NOT) or REBUILDing one is the fact that the original object does remain until the new object is committed. For really large Clustered Indexes and depending on how much freespace the MDF/NDF file has, that can cause a huge expansion of the MDF/NDF file so that it can accommodate the size of both the original an new objects. Of course, the space occupied by the original object is returned as freespace to the file but that can be a whopping amount that's just not needed especially in the short term.
Yes, it is a clustered index.
The way I handle such large CREATEs or REBUILDs is to create a new file group and create the index there. From there you can either do another CREATE (WITH DROP_EXISTING=ON) to move it back to the PRIMARY file group (and then drop the temporary file group you made) of leave it in the new file group and do the bit of work necessary to shrink the PRIMARY file group and rebuild the indexes that were fragmented by the shrink. The latter has the advantage for future index maintenance (REBUILDs) on the large table by creating another file group and doing the CREATE (WITH DROP_EXISTING=ON) and then drop the original (not the PRIMARY). You can go back and forth like that on a regular basis to "rebuild" the large CLUSTERED index without ending up with a shedload of wasted freespace.
Of course, it's much better to build a Clustered Index that will only fragment 1 or 2% every half decade or so.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)