September 29, 2008 at 9:13 am
I noticed that one of my database datafiles, containing one large table, consistently had 23 GB of wasted space - total space 46 Gb, used space 23 Gb. I wanted to retrieve the space so I shrank the datafile using DBCC Shrinkfile. Sure enough, I retrieved the space. However, when I rebuilt the indexes on the table (I dropped them prior to shrinking, knowing that they would get defragmented during the shrink) and just before the rebuild finished, the space usage jumped to 46 Gb again and the space used stayed at 23 Gb. I started again, shrinking the database and rebuilding the clustered index, this time with SORT IN TEMPDB = ON, thinking that the last time, SQL Server used the data file itself to sort the data, causing the datafile to grow larger. However, even with SORT IN TEMPDB = ON, the datafile again grew to 46 Gb. Does anyone have an idea why the datafile grows to this size (notice that it is double the size of the datafile right after the shrink) and what I can do to prevent it? Thanks - Willem
September 29, 2008 at 9:24 am
If I remember right the space usage grows by 1.1 times the size of the index when reindexing. I never shrank files that grew from reidenxing as they will need the space the next time you reindex anyhow.
September 29, 2008 at 9:37 am
SORT_IN_TEMPDB does what is says: Only sorts during rebuilding take place in TempDb, all other steps take place in your filegroup.
Wilfred
The best things in life are the simple things
September 29, 2008 at 9:38 am
I thought it was 2x, but in any case, you need space to rebuild the indexes. It doesn't drop them first, it creates a new one, then drops the old one so your data remains available. Depending on fillfactor, type of index, etc., you might need lots of space.
Also, updates/inserts need free space in the file. This isn't like a text file that should grow when you add data. For a SQL file, you want free space in there so the file doesn't fragment and doesn't have to grow often as this is an expensive operation.
My rule is to check space monthly, ensure I have space for 3 months operation at least.
Shrinking a file whacks your indexes and creates lots of fragmentation.
September 30, 2008 at 9:14 am
In general, then, would it be fair to say that whenever you build an index on a table that doesn't yet have any indexex, first of all, you'll be adding space in the filegroup to house the index and in addition, you will be using work space during the construction of the index that you won't get back? So, for example, if the data is 1 gb and you add an index that takes 500 Mb you may end up getting 1.75 Gb total for the data and the index, the additional 250 Mb being the work space that was used during the construction of the 500 Mb index. That 250 Mb becomes unused space in the datafile that you cannot get back.
It just surprises me that constructing a clustered index on a 23Gb table takes 23Gb of work space. I wouldn't have thought it took that much, especially if I'm sorting in TEMPDB.
September 30, 2008 at 10:30 am
If by "work space you don't get back", you mean "space that is used temporarily, marked as free in the data file once completed, to be used in the future by any number of other activities", then - yes. Data files, just like log files, need extra room in order to perform their jobs so to speak, so starving them (or - taking away their free space all of the time) is usually a bad idea.
That is why you will often see a recommendation to make your data and log files big. The last thing you need during a busy operation is to have one of the types of files run out of usable space and have to grow the file.
As to the clustered index - rebuilding a clustered index = rewriting an entire table, so it's not out of the question that the file would be just about double after a rebuild.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 10:39 am
Thank you all for your time and expertise. You've given me some things to think about and experiment with. Does anyone have any suggestions for additional reading that explores the inner workings of SQL Server regarding indexes and rebuilding them?
September 30, 2008 at 2:20 pm
Matt Miller (9/30/2008)
As to the clustered index - rebuilding a clustered index = rewriting an entire table, so it's not out of the question that the file would be just about double after a rebuild.
a clustered index is stored with the table so essentially the index is the table that is undoubtedly why you see the space usage jump
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 30, 2008 at 2:29 pm
Perry Whittle (9/30/2008)
Matt Miller (9/30/2008)
As to the clustered index - rebuilding a clustered index = rewriting an entire table, so it's not out of the question that the file would be just about double after a rebuild.a clustered index is stored with the table so essentially the index is the table that is undoubtedly why you see the space usage jump
That's what I was trying to say. Of course - you're saying it better than I am....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 2:31 pm
great minds think alike they say 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 30, 2008 at 3:25 pm
Does anyone have any suggestions for additional reading that explores the inner workings of SQL Server regarding indexes and rebuilding them?
Here are a few to start you off:
Heap Structures
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72f63db6-7306-4c43-a73d-7eaa4ffe1f82.htm
Table and Index Organization
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8936789a-e803-4814-83f2-77e717f81736.htm
Managing Space Used by Objects
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5b6751a2-e67e-432c-8999-eb6382d960c4.htm
Add to the above a search of this site for many many forum questions in reference to indexes.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply