By: Andy Novick | Comments (4) | Related: > 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.
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
