SQL in the Wild

Or “Order the pages, shuffle the pages.”

Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are on independent drives, there’s little difference between space free on the drive or space free in the data file. There is also a more insidious reason for not shrinking a database.

Let’s take a very simple database (The creation code is at the end of the post). I have two tables, both with a tens of thousands of rows. Both tables have a clustered index on a uniqueidentifier and are heavily fragmented (>99%).

DBCC SHOWCONTIG(LargeTable1) -- 99.30%
BCC SHOWCONTIG(LargeTable2) -- 99.21%

To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.

DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)

So, shrink the database to release the wasted space back to the OS

DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free

That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.

DBCC SHOWCONTIG(LargeTable1)
- Logical Scan Fragmentation ..................: 99.99%

DBCC SHOWCONTIG(LargeTable2)
- Logical Scan Fragmentation ..................: 7.08%

Oops. Not exactly a desired outcome.

When SQL shrinks a data file, it takes extents that are towards the end of the file and moves them to empty places further forward. It does this with no concern over logical order of pages or indexes. Net result, after shrinking a database, many of the indexes in that database will be badly fragmented.

For this reason mainly I always recommend that, especially for production databases, the data files get grown as necessary and not shrunk. The space that can be reclaimed from the data file is not worth what the shrink does to page ordering. Especially since, as production databases tend to do, the file will simply be growing again sometime in the future.

All too often I hear of maintenance plans that first rebuild all the indexes, then shrink the data files. That kind of maintenance is worse than useless. The index rebuild uses cpu and time to arrange indexes in logical order and in the process often grows the data file. The shrink then uses more time and cpu and often will leave the indexes more fragmented than they were before the rebuild.

Basically, if you’re going to rebuild indexes, don’t shrink the data files. If you’re going to shrink data files, either don’t waste time rebuilding indexes, or do them after the shrink.

Paul Randal wrote a very nice post on the downsides of shrink, entitled “Turn Auto Shrink Off!” Pretty much says it all.

Caveat: There are cases where shrinking data files does make sense. When a process created lots of tables for processing then dropped them again, after a massive archiving job, after changing data types in a table to release a large amount of wasted space (more on that another time). Just be aware of the effect of a shrink on the fragmentation of indexes.

Edit: Some more thoughts from Paul Randal on shrinking databases: Autoshrink. Turn it OFF!

Sample Code:
SET NOCOUNT ON
GO

CREATE DATABASE TestingShrink
GO

ALTER DATABASE TestingShrink SET RECOVERY SIMPLE
GO

USE TestingShrink
GO

Create Table LargeTable1 ( — row size of ~700 (10 rows per page)
ID BIGINT,
SomeString CHAR(600),
Row_ID UNIQUEIDENTIFIER,
AValue NUMERIC(30,8),
RandomDate DATETIME
)

Create Table LargeTable2 ( — row size of ~700 (10 rows per page)
ID BIGINT,
SomeString CHAR(600),
Row_ID UNIQUEIDENTIFIER,
AValue NUMERIC(30,8),
RandomDate DATETIME
)
GO

– ensuring high fragmentation
CREATE CLUSTERED INDEX idx_Large1 on LargeTable1 (Row_ID)
CREATE CLUSTERED INDEX idx_Large2 on LargeTable2 (Row_ID)
GO

DECLARE @i SMALLINT
SET @i = 0
WHILE (@i<8)
BEGIN
;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS (
SELECT 1 AS RowNo, ‘abc’ as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),’1753/01/01′)
UNION ALL
SELECT rowNo+1, ‘abc’ as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),’1753/01/01′)
FROM DataPopulate WHERE RowNo<10000
)
INSERT INTO LargeTable1
SELECT * FROM DataPopulate
OPTION (MAXRECURSION 10000)

;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS (
SELECT 1 AS RowNo, ‘abc’ as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),’1753/01/01′)
UNION ALL
SELECT rowNo+1, ‘abc’ as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),’1753/01/01′)
FROM DataPopulate WHERE RowNo<10000
)
INSERT INTO LargeTable2
SELECT * FROM DataPopulate
OPTION (MAXRECURSION 10000)
SET @i = @i+1
END
GO

DBCC SHOWCONTIG(LargeTable1) — 99.30%
DBCC SHOWCONTIG(LargeTable2) — 99.21%
DBCC showfilestats — 2467 extents total, 2463 used (157 MB total, 256kb free)
GO
– Rebuild the indexes. This should grow the database quite a bit.
Alter Index idx_Large1 on LargeTable1 rebuild
Alter Index idx_Large2 on LargeTable2 rebuild
go

DBCC SHOWCONTIG(LargeTable1) — 0%
DBCC SHOWCONTIG(LargeTable2) — 1%
DBCC ShowFileStats — 3363 extents total, 1697 used (215 MB total, 106 MB free)
GO

USE Master
go
DBCC SHRINKDATABASE (TestingShrink, 10) — Shrink to 10% free
go
use TestingShrink
GO

DBCC ShowFileStats — 1885 extents total, 1695 used (120 MB total, 12 MB free)
DBCC SHOWCONTIG(LargeTable1) — 99.99%
DBCC SHOWCONTIG(LargeTable2) –7.08%
GO

USE master
GO

DROP DATABASE TestingShrink
GO