DBCC DBREINDEX / DBCC SHRINKFILE

Question

DBCC DBREINDEX / DBCC SHRINKFILE

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
September 5, 2008 at 9:31 am

#77085

I have a large table 1bil rows + that I have partitioned by year. I have 4 separate tables (each with ~350 mil rows). Each table is in its own file/filegroup. Each is 70%+ fragmented. When I use DBCC DBREINDEX on one of the tables it of course doubles the size of the file. Each file is approx 22gig and I don't have the space for them to exist at double that size. Because of this I then use DBCC SHRINKFILE to shrink it back to its smallest size.
As a test I ran DBCC SHOWCONTIG right after the DBREINDEX and got less than 1% fragmentation. Then I ran my SHRINKFILE, then the SHOWCONTIG again. I'm now back to 70+% fragmentation.
So I now have 2 questions. 1) How do I fix my problem. Each year as it ends is read only so once I have them defraged I'm done (except for the current year of course). 2) Does anyone have any idea why this is happening to begin with?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

This might be causing your problem

FROM BOL (DBCC SHRINKFILE):
If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained.

Try tagging SHRINKFILE with the argument TRUNCATEONLY. That supposedly won't move data around.

_____________________________________________________________________
- Nate

@nate_hughes

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Kenneth Fisher (9/5/2008)
As a test I ran DBCC SHOWCONTIG right after the DBREINDEX and got less than 1% fragmentation. Then I ran my SHRINKFILE, then the SHOWCONTIG again. I'm now back to 70+% fragmentation.
So I now have 2 questions. 1) How do I fix my problem. Each year as it ends is read only so once I have them defraged I'm done (except for the current year of course). 2) Does anyone have any idea why this is happening to begin with?

That's expected. What shrink does is to take pages at the end of the file and move them as early in the file as possible. If you have your index nicely defragmented, the pages will be in order. The shrink comes along and taks the last page in the index and moves it earlier in the file, then takes the next page and moves it even earlier, etc, etc.

Worst case, a shrink can completely reverse the order of an index, leaving it at 99%+ fragmentation.

Do you have space to put a second copy of this table temporarily?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

RP_DBA,

Thanks for the suggestion but I've tried TRUNCATE_ONLY. Unfortunately the defrag moves the data to the end of the file so TRUNCATE_ONLY doesn't do much.

Gila,

I have around 80gig free at the moment so I have plenty of space to move things around. What are you suggesting?

Thanks

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I'm assuming this table has a clustered index?

You can drop and recreate the clustered index and specify the new location on another filegroup. Then go and shrink the file where you want the table to be kept to the size required (plus maybe 10% for overheads), then drop and recreate the clustered index again and specify that filegroup as the location.

This assumes that there's nothing (including nonclustered indexes) in that filegroup.

So, would look something like this (if the cluster is not the PK)

CREATE CLUSTERED INDEX idx_Whatever ON SomeTable (SomeColumn)

WITH DROP_EXISTING

ON Some_Other_Filegroup

GO

CREATE CLUSTERED INDEX idx_Whatever ON SomeTable (SomeColumn)

WITH DROP_EXISTING

ON Desired_Filegroup

I'm not going to promise, but it should do what you want. If you want to be explicit (or if the cluster is the PK) then you can do the drop and the create separately.

p.s. Make sure you don't have autoshrink on anywhere.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass