December 14, 2003 at 5:06 pm
Hi,
I have inherited a SQL Server 2000 system that reorgs all indexes on the weekend using SQLMAINT.EXE -D dbname -RebldIdx. After the reorg there is a DBCC UPDATEUSAGE and then a DBCC SHRINKDATABASE NOTRUNCATE. I've noticed that at the beginning of each week the Scan Density and the Logical Fragmentation for a lot of the critical indexes is not very good. This weekend I decided to put in a DBCC SHOWCONTIG both after the reorg and then again after the SHRINKDATABASE statements. The figures are showing that after the reorg there is 100% scandensity and 0% logical fragmentation. However after the SHRINKDATABASE the scandensity on a number of indexes has dropped from 99% to 55% and the logical fragmentation has gone from 0% to 100%. SHRINKDATABASE seems to undo a lot of what the reorg has done. Has anybody else noticed this? Should I be using SHRINKDATABASE first and then reorging the indexes? All of the tables in question are clustered. Any advice would be appreciated.
regards
John Matear
December 14, 2003 at 7:28 pm
First things first - do you have a need to shrink the database.
If the database is going to grow during the week anyway, everytime it needs to grow, your users will be suffering a performance hit.
A better strategy would be to review the free space within each table and index and set it to a value that reduces the amount a fragmentation in the table/index. This is done by setting the FILLFACTOR and can be adjusted with a reindex.
Once this is done, setup your database size to accomodate a reasonable amount of growth. Personally, I normally set a fixed number of bytes as the increment rather that a percentage.
Monitor the amount of free space in your database and, ideally, increase "proactivity" so that your users do not suffer when this is done.
December 17, 2003 at 1:14 am
I will certainly investigate if the database does in fact require a SHRINKDATABASE. Being new to SQL Server I was assuming that whoever set this process up knew what they were doing. Probably not a good assumption. However my question still stands and that is does SHRINKDATABASE undo a lot of what the DBREINDEX does. If this is true then this would imply that you would never use SHRINKDATABASE without running DBREINDEX (or equivalent) after.
December 22, 2003 at 10:41 am
You are correct John.
DBCC shrinkfile NOTRUNCATE does indeed undo the reindex, as you have seen from your investigations.
It moves allocated pages from the end of the file to the beginning of the file, with no regard to he table/index data placement.
The database has not shrunk at all however, it leaves the unallocated pages intact.
So you will not get a performance hit other than allocating a new extent or a page split.
The SHRINKDATABASE TRUNCATEONLY option however does NOT move any pages, it simply chops from the end of the file up to, but not including, the last allocated extent.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply