Rotating Header Image

If an Index is Heavily Fragmented Will SQL Server Still Use It?

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)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. The camera used in the clone is a fairly generic device, and the flash appears to just be a little light bulb with almost no energy output. The flashlight mode is a joke.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.