November 27, 2013 at 4:21 am
While had a conversation over fragmentation and execution plan, we ended up differenent opinion. My thought is fragmentation will not impact Exe plan. But my colleague has different idea.
I request all experts to reply with your opinion.
To test, we have created two codes blocks, having little difference like number of tables, fill factors (to force fragmentation). I do not think these minor changes will be a important factor here for execution plan difference.
-- -----------------------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------------------
-- Query block to prove fragmentation will not impact Execution Plan
Create Table BigTable1 ( id int identity primary key, SomeColumn char(4), Filler char(100) )
-- First table
INSERT INTO BigTable1 (SomeColumn)
SELECT top 250000 char(65+FLOOR(RAND(a.column_id *5645 + b.object_id)*10)) +
char(65+FLOOR(RAND(b.column_id *3784 + b.object_id)*12)) +
char(65+FLOOR(RAND(b.column_id *6841 + a.object_id)*12)) +
char(65+FLOOR(RAND(a.column_id *7544 + b.object_id)*8))
from master.sys.columns a cross join master.sys.columns b -- taken from Gila monster's blog. Thanks Gila.
CREATE NONCLUSTERED INDEX [idx_BigTable_SomeColumn] ON [dbo].[BigTable1] ([SomeColumn] ASC)
with fillfactor=100
-- Second table
Select * into bigtable2 from bigtable1
CREATE NONCLUSTERED INDEX [idx_BigTable_SomeColumn2] ON [dbo].[BigTable2] ([SomeColumn] ASC)
with fillfactor=50
Select SomeColumn from BigTable1 where SomeColumn like 'aa%' -- Seek
GO
Select SomeColumn from BigTable2 where SomeColumn like 'aa%' Option(recompile)-- seek
Select SomeColumn from BigTable1 where SomeColumn between 'adb' and 'df'Option(recompile)-- seek
GO
Select SomeColumn from BigTable2 where SomeColumn between 'adb' and 'df'Option(recompile)-- seek
-- -----------------------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------------------
-- Query block to prove fragmentation will impact Execution Plan
Create Table BigTable1 ( id int identity , c2 char(100) default replicate('a', 100))
INSERT INTO BigTable1 default values
go 1500
--Now I’m creating one NCI with heavy fragmentation
CREATE NONCLUSTERED INDEX [idx_BigTable_SomeColumn1] ON [dbo].[BigTable1] (c2 ASC)
with fillfactor=2
Select c2 from BigTable1 where c2 like 'aa%' option(recompile)
--optimizer uses the table scan.
--Now I’m going to rebuild the index
ALTER INDEX [idx_BigTable_SomeColumn1] ON [dbo].[BigTable1] REBUILD
with (fillfactor = 100)
Select c2 from BigTable1 where c2 like 'aa%' option(recompile)
--Now its using NCI seek
-- -----------------------------------------------------------------------------------------------
November 27, 2013 at 4:24 am
The optimiser has no knowledge of fragmentation, therefore logical fragmentation cannot directly affect an execution plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2013 at 4:28 am
SQL Show (11/27/2013)
--Now I’m creating one NCI with heavy fragmentationCREATE NONCLUSTERED INDEX [idx_BigTable_SomeColumn1] ON [dbo].[BigTable1] (c2 ASC)
with fillfactor=2
That creates an index with very low fragmentation, not heavy fragmentation.
It has very low page density meaning far more pages than are necessary, but the fragmentation will be very low since it's a freshly created index.
To create a heavily fragmented index, you need to create it first, then insert values into the table that don't match the index key order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2013 at 4:36 am
GilaMonster (11/27/2013)
The optimiser has no knowledge of fragmentation, therefore logical fragmentation cannot directly affect an execution plan.
Can you please explain Internal Fragmentation also cant affect an exeuction plan?
November 27, 2013 at 4:41 am
Define 'internal fragmentation' please? It's not a standard term, it doesn't have a defined meaning, different people use it for different things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2013 at 4:50 am
I meant to say empty space inside pages. If pages have lots of empty space inside them, these unused spaces will affect exeution plan?
as per your reply, am going to try with large inserts and then fragmenting them.
November 27, 2013 at 4:54 am
That's low page density, not fragmentation. When you say 'fragmentation', the general assumption will be that you mean 'logical fragmentation'
The optimiser has no knowledge of logical fragmentation. It has no direct knowledge of page density, however a low page density will increase the number of pages in the table which could, depending on the operation, affect the optimiser's costing of potential plans.
You can have low page density without high logical fragmentation and you can have high logical fragmentation without low page density
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2013 at 5:04 am
A Perfect Explanation. Thanks Gila.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply