Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Issues with running DBCC SHRINKFILE on your SQL Server data files

MSSQLTips author Andy Novick By:   |   Read Comments (4)   |   Related Tips: More > Performance Tuning
Problem

Why would database performance remain low and fragmentation high event though the entire database has been defragmented every night?  In this tip we cover how SHRINKFILE works and some of the issues it might create.

Solution

This is a real problem faced by a client.  They defragmented every table every evening using DBCC DBREBUILD, but database performance remained poor. Fragmentation isn't the only reason for an unresponsive database, but it can be a major contributor.  In their case the culprit was the DBCC SHRINKFILE command that they were issuing after the reindex operation.  This reversed the defragmentation step and gave them a database with nearly 100% fragmentation.

DBCC SHRINKFILE does what the name implies.  It shrinks the size of a database file, either to a specific size or as small as it can.  It does this by moving pages from the back of the file to the first free page in the front of the file with the consequence that pages end up in reverse order.  This is about as bad as it gets, as far as fragmentation goes.

To illustrate the process the script below creates a database and a single table, consume_space, that is highly fragmented.  The fragmentation comes it two flavors.  First is the file fragmentation.  Because the default 1MB growth size is used, the file must grow frequently.  While file fragmentation isn't directly related to the fragmentation within a SQL table, it should be avoided for the similar reason, it causes extra IO operations.   I recommend creating a file at the size that it will eventually grow to and limiting file growth operations to a minimum, but the small file growth size is kept for this demonstration so that we can see the file size change in detail. 

The second form of fragmentation is the topic of this article, index fragmentation. Index fragmentation occurs when SQL Server must split a page to allow for the insertion of a new row.  It's a normal part of the handling of pages. Here's the script to create a database and populate it with a fragmented table.  The last query shows the database size.

SET NOCOUNT ON
GO 
IF DB_ID('ns_shrink_demo') IS NOT NULL DROP DATABASE ns_shrink_demo 
GO 
CREATE DATABASE ns_shrink_demo 
GO 
USE ns_shrink_demo 
GO 
IF OBJECT_ID ('consume_space') IS NOT NULL DROP  TABLE consume_space 
GO 
CREATE TABLE consume_space ( id decimal(28,18) NOT NULL  
                           , padding CHAR(1000) DEFAULT (REPLICATE('abcdefghij', 100)) 
                           , CONSTRAINT pk_consume_space PRIMARY KEY CLUSTERED  (id) 
                           ) 
GO 
-- Produce a fragmented table                            
INSERT INTO consume_space (id) VALUES (100000), (200000), (300000), (4000000), (5000000) 
                                     ,(600000), (700000), (800000), (900000), (1000000) 
DECLARE @i INT = 1 
WHILE @i < 4 BEGIN 
   INSERT INTO consume_space(id)  
   SELECT DISTINCT (id - (id * RAND() / a.x)) 
      FROM consume_space 
      CROSS JOIN (VALUES (CAST (1 AS numeric(28,18))), (2), (3), (4), (5), (6), (7), (8), (9)) a (x) 
       WHERE (id - (id * RAND() / a.x)) NOT IN (SELECT id FROM consume_space); 
   SET @i = @i + 1 
END  
GO 
SELECT FILE_ID, LEFT(name, 24) name, CONVERT(INT, size /128.0, 0) size_mb  
   FROM sys.database_files WHERE FILE_ID = 1 
GO 

FILE_ID     name                     size_mb 
----------- ------------------------ ----------- 
1           ns_shrink_demo           18 

Fragmentation of an index can be measured with the system function sys.dm_db_index_physical_stats.  This function must read from the table to produce its result, so use it cautiously.  It can take a long time to gather the necessary statistics on large tables.  This query shows the key information from  the consume_space table:

SELECT index_level 
      , avg_fragmentation_in_percent 
      , fragment_count 
      , avg_fragment_size_in_pages  
  FROM sys.dm_db_index_physical_stats ( 
             DB_ID('ns_shrink_demo') -- database_id 
           , OBJECT_ID('consume_space') -- object_id 
          , 1, 1, 'LIMITED') 
GO  

index_level avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages
----------- ---------------------------- -------------------- --------------------------
0           98.9078822412156             2104                 1.00095057034221

The key number is the avg_fragmenataion_in_percent, which stands at 98.9 percent.  After the high INSERT activity of the loop that populated the table almost every page is out of order.

Fragmentation can be removed by rebuilding the index either through DBCC DBREINDEX, in SQL 2000 and beyond, or ALTER TABLE with the REBUILD option, in SQL 2005 and beyond.  Here's a script that rebuilds PK_Consume_Space to remove fragmentation and then measures both the fragmentation and space used by the database:

ALTER INDEX pk_consume_space ON consume_space 
   rebuild WITH (FILLFACTOR=80) 
GO 
SELECT FILE_ID, LEFT(name, 24), CONVERT(INT, size /128.0, 0) size_mb  
   FROM sys.database_files WHERE FILE_ID = 1 
GO 
    
SELECT index_level 
      , avg_fragmentation_in_percent 
      , fragment_count 
      , avg_fragment_size_in_pages  
  FROM sys.dm_db_index_physical_stats ( 
             DB_ID('ns_shrink_demo') -- database_id 
           , OBJECT_ID('consume_space') -- object_id 
          , 1, 1, 'LIMITED') 
GO

    file_id                              size_mb
----------- ------------------------ -----------
          1 ns_shrink_demo                    42

index_level avg_fragmentation_in_percent
----------- ----------------------------
          0           0.0699790062981106 

fragment_count avg_fragment_size_in_pages
-------------- --------------------------
             8                    178.625

As you can see the size of the database has grown from 18 to 42 megabytes.  That's because the data has to be copied to new space in the file and there's a time when there are two copies of the data on disk.  In addition, by using fillfactor=80 extra space has been added to the defragmented index.  This space allows for insertion of new rows without splitting and can be a boost to performance during inserts. 

Fragmentation in the consume_space table has been nearly eliminated.  It's down to 0.069 percent, which means that the physical order rows of the table within the file nearly matches the logical order of the rows.  This ordering makes it very efficient to read the data.

Now we come to the problem.  To remove the extra space on disk it might be tempting to run DBCC SHRINKFILE.  The file will shrink, but at the cost of fragmentation.  This query runs the SHRINKFILE and measures the size on disk and the fragmentation of the consume_space table:

DBCC SHRINKFILE ('ns_shrink_demo', 1) 
GO
 
SELECT FILE_ID, LEFT(name, 24), CONVERT(INT, size /128.0, 0) size_mb  
   FROM sys.database_files WHERE FILE_ID = 1 
GO 
SELECT index_level 
      , avg_fragmentation_in_percent 
      , fragment_count 
      , avg_fragment_size_in_pages  
  FROM sys.dm_db_index_physical_stats ( 
             DB_ID('ns_shrink_demo') -- database_id 
           , OBJECT_ID('consume_space') -- object_id 
          , 1, 1, 'LIMITED') 
GO
  
  DbId      FileId CurrentSize MinimumSize   UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
    33           1        1616         160        1592           1592

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    file_id                              size_mb
----------- ------------------------ -----------
          1 ns_shrink_demo                    12

index_level avg_fragmentation_in_percent       fragment_count avg_fragment_size_in_pages
----------- ---------------------------- -------------------- --------------------------
          0             99.9300209937019                 1429                          1

Sure, the file shrank back to 12 megabytes as seen in the second result set.  DBCC SHRINKFILE did it's job.  Unfortunately fragmentation went from less than 1% back up to 99.9%.  This is going to lead to excessive out of order reads when we attempt to use the table.  The impact will be felt both during SELECTs and insert activity or any update activity that expands a row and forces a split. 


There are three workable solutions to the problem. The first and usually the best option is to live with the extra space in the file.  If you're database is growing or very dynamic, the extra space is necessary anyway to avoid file growth during production hours.  The second option is a complete file rebuild. This requires using a CREATE INDEX with DROP_EXISTING statement for all the indexes in the file and can be complex.  The simpler solution is to use ALTER INDEX REORGANIZE, or DBCC INDEXDEFRAG.   The latter command is deprecated and may not be included in future releases of SQL Server, but it works in SQL Server 2000.  Here's how to run it:

ALTER INDEX pk_consume_space ON consume_space reorganize  
-- in SQL 2000 use DBCC INDEXDEFRAG (0, 'consume_space', 1) 
GO
 
SELECT FILE_ID, LEFT(name, 24), CONVERT(INT, size /128.0, 0) size_mb  
   FROM sys.database_files WHERE FILE_ID = 1 
GO 
   
SELECT index_level 
      , avg_fragmentation_in_percent 
      , fragment_count 
      , avg_fragment_size_in_pages  
  FROM sys.dm_db_index_physical_stats ( 
             DB_ID('ns_shrink_demo') -- database_id 
           , OBJECT_ID('consume_space') -- object_id 
          , 1, 1, 'LIMITED') 
GO
    
SELECT FILE_ID, LEFT(name, 24), CONVERT(INT, size /128.0, 0) size_mb  
   FROM sys.database_files WHERE FILE_ID = 1 
GO 

    file_id                              size_mb
----------- ------------------------ -----------
          1 ns_shrink_demo                    12

index_level avg_fragmentation_in_percent       fragment_count avg_fragment_size_in_pages
----------- ---------------------------- -------------------- --------------------------
          0            0.699790062981106                   12           119.083333333333  

Now fragmentation is reduced to less than one percent without the data file growing.  The disadvantage of REORGANIZE over REBUILD is that the fillfactor wasn't applied and there may be additional page splits during insert activity.

Next Steps
  • Avoid using DBCC SHRINKFILE on your data files unless absolutely necessary
  • Remove DBCC SHRINKFILE or the shrink database option in a Database Maintenance Plan
  • Use ALTER TABLE REORGANIZE to remove fragmentation without increasing data file size.
  • Note that this example was done on a small table, so do some testing on your systems to see what options work best.


Last Update: 7/7/2010


About the author
MSSQLTips author Andy Novick
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Tuesday, July 27, 2010 - 9:31:17 AM - Gshaw18 Read The Tip

Thank you for the well written article.  While others have touched on this subject, few have been able to lay it out as well as you have.

There is another form of fragmentation that most people do not think about and that is fragmentation at the file system level.   Much like the hard drive in your pc, your database server is server is silently suffering from data and log file fragmentation.  Every time a data or log file is expanded, automatically or manually, it has to claim space at the OS level and grab space where itís available.  This is another argument for following Microsoftís best practices and making sure that your auto growth settings are set to fixed MB sizes and set to a large enough size to minimize the number of growths but not so large as to waste space.  Having perfectly defragged databases is a good step but a fragmented file system will cause you just as much of a headache and is much harder to get rid of.

As we are a 24X7 shop, we donít have the ability to take the SQL instance down for the night and run a disk defrag or backup all the databases and restore them one by one.  Our only solution is to bring up new SQL environments every time we migrate to a new version.


Tuesday, July 27, 2010 - 10:19:05 AM - Tom Garth Read The Tip

What a great explanation. Very detailed and easy to understand. Thanks for the enlightenment!

 Tom Garth


Wednesday, October 24, 2012 - 2:52:12 PM - Peter Juhl Read The Tip

Nice article, but the SQL produces errors on SQL 2005.


Sunday, February 09, 2014 - 10:46:36 PM - GiangCMH Read The Tip

tks so much!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.