Brent Ozar ( @BrentO , blog ) recently wrote a blog post about how you should Stop Worrying About SQL Server Fragmentation which prompted this twitter convo:
So I thought I would write up a quick test that does the following:
- Create a Test DB
- Create a test table (Customers) that has a CreateDateTime that defaults to a random date greater than (2000-01-01)
- Create an index on CreateDateTime (idx_CreateDateTime)
- Loop through and insert 1 million rows of data
- Check the fragmentation of idx_CreateDateTime (should be around 95-99% fragmented)
- Query the table using a WHERE clause that will use the idx_CreateDateTime index
USE [master] GO IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestIndexUsage') DROP DATABASE TestIndexUsage GO CREATE DATABASE TestIndexUsage ON PRIMARY ( NAME = N'Test', FILENAME = N'C:\Test.mdf' , SIZE = 1GB , FILEGROWTH = 100MB ) LOG ON ( NAME = N'Test_log', FILENAME = N'C:\Test_log.ldf' , SIZE = 100MB , FILEGROWTH = 100MB) GO ALTER DATABASE TestIndexUsage SET RECOVERY SIMPLE GO SET NOCOUNT ON GO USE TestIndexUsage GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U')) DROP TABLE [dbo].[Customers] GO CREATE TABLE [dbo].[Customers]( [CustomerID] [int] PRIMARY KEY IDENTITY(1,1), [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [CreateDateTime] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Customers] ADD DEFAULT (DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01') ) FOR [CreateDateTime] GO CREATE INDEX idx_CreateDateTime ON Customers(CreateDateTime) GO DECLARE @LoopCounter int = 1000000 WHILE @LoopCounter != 0 BEGIN INSERT INTO Customers(FirstName, LastName) SELECT CONVERT(varchar(50), NEWID()), CONVERT(varchar(50), NEWID()) SELECT @LoopCounter = @LoopCounter - 1 END SELECT OBJECT_NAME(A.object_id) as 'TableName', i.name as 'IndexName', A.index_type_desc, A.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.Customers'), NULL, NULL , 'LIMITED') as A LEFT OUTER JOIN sys.indexes AS i ON A.object_id = i.object_id AND A.index_id = i.index_id -- NOW TURN ON INCLUDE ACTUAL EXECUTION PLANS /* SELECT FirstName, LastName, CreateDateTime FROM dbo.Customers WHERE CreateDateTime = '2007-01-01' */
As you can see even though the index idx_CreateDateTime is HEAVILY fragmented the optimizer will still use the index to return the data requested.
A fun little quicky to hopefully help clear up any doubts out there about this SQL Server myth.
Enjoy!! (Follow me on Twitter: @ColinStasiuk)
