May 3, 2011 at 2:39 am
Hi All,
Thanks for sharing your valuable knowledge and thoughts. After agreeing to bhuvnesh in post I have few observations as below.
I am using the syntax to rebuild the index as below
Alter index[ind_name]on[tbl_name]Rebuild with (sort_in_tempdb=on)
Before executing the command below are the observation on the drive size on which the db files exist and database size (Which i took by right clicking the DB in SSMS)
Drive free space: 201GB
Db size : 133283.31 MB
Space available : 34094.38 MB
Drive free space (For tempdb) :601 GB
After the execution below are the observations
Drive free space: 187GB
Db size : 153693.81 MB
Space available : 54504.84 MB
Drive free space (For tempdb) :601 GB
Now even using the sort_in_tempdb option the drive on which the DB exist has lesser free space than before rebuilding the index why it so?
Please guide.
May 3, 2011 at 2:48 am
SQL needs space for the new 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
May 3, 2011 at 3:10 am
Hi Gail,
What is the use of sort in tempdb. I also cheched for the fragmented index using follwing script all the index i rebuid came to as fragmented why it is so?
SELECT
SI.[name] AS index_name,
OBJECT_SCHEMA_NAME(SDDIPS.[object_id]) + '.' + OBJECT_NAME(SDDIPS.[object_id]) AS [object_name],
SDDIPS.[index_type_desc], SDDIPS.[avg_fragmentation_in_percent]
FROM sys.[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'Detailed') SDDIPS
INNER JOIN sys.[indexes] SI ON SDDIPS.[object_id] = SI.[object_id]
AND SDDIPS.[index_id] = SI.[index_id]
WHERE SDDIPS.[avg_fragmentation_in_percent] > 15
AND SDDIPS.[page_count] > 2
AND SDDIPS.[index_id] > 0
ORDER BY OBJECT_SCHEMA_NAME(SDDIPS.[object_id]), OBJECT_NAME(SDDIPS.[object_id])
May 3, 2011 at 11:32 am
vinod.saraswat (5/3/2011)
Hi All,Drive free space: 201GB
Db size : 133283.31 MB
Space available : 34094.38 MB
Drive free space (For tempdb) :601 GB
After the execution below are the observations
Drive free space: 187GB
Db size : 153693.81 MB
Space available : 54504.84 MB
Drive free space (For tempdb) :601 GB
Now even using the sort_in_tempdb option the drive on which the DB exist has lesser free space than before rebuilding the index why it so?
Note that free space hasn't really changed, merely what was assigned to be available to the database.
Your first set of values is around 133 gigs of assigned space - 34 gigs of free space, so about 99 gigs for the database's actual usage.
Sorting in tempdb meant it didn't have to rebuild the entirety in the local db, only some of it. Most of that space is to allow for defragging, pulling pages out of any shared extents, etc, and to get everything reorganized. So you're going to have some 'spaces' in your file so things go cleaner. Notice your last set of numbers is 153 gigs of assigned - 54 gigs of free... still you have 99 gigs assigned to data. It's just more spread out and the DB needed some room to work in the middle.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 4, 2011 at 8:32 am
Would this be a case when it would make sense to DISABLE an index before rebuilding it?
http://msdn.microsoft.com/en-us/library/ms177406.aspx
I've used this one before in some development testing but was curious it had real application in the field.
May 4, 2011 at 11:20 am
Incindium (5/4/2011)
Would this be a case when it would make sense to DISABLE an index before rebuilding it?http://msdn.microsoft.com/en-us/library/ms177406.aspx
I've used this one before in some development testing but was curious it had real application in the field.
It can help on the non-clustered indexes, but if you disable the clustered you turn the table off. I would have to research for when it's a good or bad idea on the NC indexes though, I don't remember the general pros and cons offhand.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply