Index Fragmentation in SQL Server 2008

You must have seen so many articles and scripts and even technet msdn provides suggestions
to reorganize and rebuild indexes just on the basis of the counter avg Fragmentation in percent.The counter
avg page space used in percent is not mentioned at all.For me it should be used in deciding when to rebuild or reorganize the
indexes. I will show you why this is much more improtant, personally for me it is even more improtant than avg fragmentation as the avg fragmentation impcats the large scan when physical IO’s are involved whereas avg page space used impacts physical Io. logical Io,CPU and memory and thus should be given much more attention.

Conclusion:

Suppose you have avg page space used in percent is say 50% and total page count is 1000.I could see following issues with this.

1. You need to read twice the pages to read the same data. If this table had fully packed pages it would have clocked 500 logical IO’s whereas now it will need 1000 IO’s.
2. More logical IO’s means you will do more physical IO’s. This could be huge impact if the table size were quite big.

3. This in my point is the main reason why I would like to include this counter in your rebuild and reorganize strategy
When you bring the data into memory it comes in pages. Thus you will have 1000 pages in memory whereas
you could have stored the same data in 500 pages. Thus you are wasting memory size of 500 pages.Memory is very precious in SQL server.Now Imagine that you have 30GB total memory and your table’s poages are 70% full. Then you are actually using 30GB whereas the same data could have been stored in 21GB. Thus 9GB would be wasted. This 9GB could have been used to keep for other table’s in memory or this memory could have been used by sort or hash opertaions.This in turn would have reduced physical IO’s and thus improved overall server performance.

Now what if your script were just checking the avg Fragmentation in percent and say rebuild the index only your fragmentation is more than 30%.This index were not picked up for rebuild and thus could have caused the performance issue.
Thus I would suggest that while deciding the strategy for index rebuild and reorg.Use the avg page space used in percent as well along with avg fragmentation.

Thus I would say that the avg page space used in percent is as important as avg fragmentation.I will reasearch into finding the exact impcats of high avg fragmenations and will post it.

Rest of the details are there in the script.

/*
You must have seen so many articles and scripts and even technet msdn provides suggestions
to reorganize and rebuild indexes just on the basis of the counter avg Fragmentation in percent.The counter
avg page space used in percent is not mentioned at all.For me it should be used in deciding when to rebuild or reorganize the
indexes. I will show you why this is much more improtant and what should be done in below script.
*/
–Create a database
if exists ( select 1 from sys.databases where name = ‘MyFragMentation’)
begin
drop database MyFragMentation
end
go
create database MyFragMentation
ON Primary
(
Name = ‘Mydata’,Filename = ‘C:\BOOK\DB\MyData.mdf’,size = 1024MB
)
LOG ON
(
Name = ‘MyLog’,Filename = ‘C:\BOOK\DB\MyLog.ndf’,size = 512MB
)
go

–Now create a table called mysoh which will have the same data as is the sales.salesorderheader table..
use MyFragMentation
go
If OBJECT_ID(‘mysoh’,’U’) is not null
begin
drop table mysoh
end
go
select * into mysoh from AdventureWorks.Sales.SalesOrderHeader
go
–Now I will create the primary key with clustered index
alter table mysoh add constraint pk_mysoh_SalesOrderId primary key (SalesOrderId)
go

–Now check the fragmentation
select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go

/*
Page count 799
avg fragmentation in percent 37.54%. This is a new table and primary key is just now created
and still I see the fragmentation.This is quite a bit odd..
avg page space used in percent is 98.5%.This is quite good. I personaly look at this counter more than the
avg fragmentation in percent.
*/

–anyway let us rebuild the clustered index
alter index all on mysoh rebuild
go

select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go

/*
Page count 798 it is reduced,
avg fragmentation in percent 37.59%. This is odd why this is increasing even though I am creating the clustered index.
avg page space used in percent is 98.61%.This is quite good again.
*/
–Now i will truncate the table and then inserts the data
truncate table mysoh
go
set identity_insert mysoh on
go
insert into mysoh with (tablockx)
( SalesOrderID
,[RevisionNumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
,[Status]
,[OnlineOrderFlag]
,[SalesOrderNumber]
,[PurchaseOrderNumber]
,[AccountNumber]
,[CustomerID]
,[ContactID]
,[SalesPersonID]
,[TerritoryID]
,[BillToAddressID]
,[ShipToAddressID]
,[ShipMethodID]
,[CreditCardID]
,[CreditCardApprovalCode]
,[CurrencyRateID]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[Comment]
,[rowguid]
,[ModifiedDate])
select
SalesOrderID
,[RevisionNumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
,[Status]
,[OnlineOrderFlag]
,[SalesOrderNumber]
,[PurchaseOrderNumber]
,[AccountNumber]
,[CustomerID]
,[ContactID]
,[SalesPersonID]
,[TerritoryID]
,[BillToAddressID]
,[ShipToAddressID]
,[ShipMethodID]
,[CreditCardID]
,[CreditCardApprovalCode]
,[CurrencyRateID]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[Comment]
,[rowguid]
,[ModifiedDate]
from AdventureWorks.Sales.SalesOrderHeader
option( querytraceon 610)
go

–check the fragmentation again

select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go

/*
Now I got what i wanted
Page count 798 it is reduced,
avg fragmentation in percent 0%. This is what everyone is looking at.
avg page space used in percent is 98.61%.This is quite good again.

Select into folowed by alter table primary key had very high fragmentation and even rebuilding index did not improve it.
I am not sure much about this behaviour. I will post it about some day when I will have much more information.But this is
not my point today so you can skip it.
*/

–Now let us delete some data. I will delete all rows which have an even salesorderid

delete from mysoh where SalesOrderID%2= 0
go

–Check the fragmentation

select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go

/*
Now I got what i wanted
Page count 798 it is reduced,
avg fragmentation in percent 0%. I have deleted half of my data but no change in this parameter and it is expected because
it represents the logical fragmenetation of pages in file.e.g. 0 fragmenetation means that every page in clustered index ‘s next page pointer
contains the page which is alos physically next to this page.
avg page space used in percent is 49.294%.This is seriouslly bad. We should have this value as close as to 100%.
As we deleted half of the rows our data should be fitted within approx 798/2 which is 399 pages but it is actually stored on 799 pages.
This is sheer watse of space.This itself is not an issue. Floowoing are its main impcats
1. You need to read twice the pages to read the same data. If this table had fully packed pages it would have clocked 399 logical
IO’s whereas now it will need 798 IO’s.
2. More logical IO’s means you will do more physical IO’s. This could be huge impact if the table size were quite big.This could have been serious issue.
3. This in my point is the main reason why I would like to include this counter in your rebuild and reorganize strategy
When you bring the data into memory it comes in pages. Thus you will have 798 pages in memory whereas
you could have stored the same data in 399 pages. Thus you are wasting memory size of 399 pages which is around 3.1MB.Memory is very precious in SQL server.
This is not huge in terms of memory but now visualize that you have 30GB total memory and your table’s poages are 70% full. Then you are actually using 30GB whereas
the same data could have been stored in 21GB. Thus 9GB would be wasted. This 9GB could have been used to store more
table’s data , thus avoiding the physical IO’s and this memory could have been used by sort and hash operation within memory.

This is like you are getting your data comressed by 30% without actually compressing the data.

Now what if your script were just checking the avg Fragmentation in percent and say rebuild the index only your fragmentation is more than 30%.
This index were not picked up for rebuild and thus could have caused the performance issue.
Thus I would suggest that while deciding the strategy for index rebuild and reorg.Use the avg page space used in percent as well along with avg fragmentation.

*/

–You saw previously that delete did not caused a chaneg in average fragmentation in percent. That is not always the case. When there will be large inserts and deletes
–Then the avg fragmentation will be changed accordingly. But before that I will show you that for the indexes
–where the avg fragmenetation is quite less but the avg page space used in percent is quite low. You do not need to do rebuild the index
–Just reorganize the index and it is lighter opertaion as compared to rebuild.

alter index all on mysoh reorganize
go
/*
CAUTION : If you do not have any fragmentation but your avg page space used is less you might tempt to use the reorganize but
this could cause the counter avg fragmentation to increase. Thus you might need to rebuild the index. Thus I would suggest that
you should try to rebuild the index,if you have window,space and resources.
*/
select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go
/*
Now you will see that the number of pages reduced to 400 and avg page space used is around 98.5%.
But our avg fragmentation has been increased.Can you guess why?
Because now the data is reorganized on the pages and some pages are freed up and now some of the index’s next logical page
is not the next physical page.
*/
–I will rebuild the index so that I have minimu fragmentation and then I will do some inserts and deletes where the avg page space used will
–be reduced but at the same time the fragmentation will be increased.

alter index all on mysoh rebuild with(online = on)
go

select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go

/*
now all counters seems fine to me..
page count 399 as expected
avg frag is 0
avg page space used is 98.61%
*/

–I will insert the data into the table which form sales.salesorderheader table with even salesorderid. Thus I will cause lots of fragmentation
–and page splits and so on.

set identity_insert mysoh on
go
insert into mysoh with (tablockx)
( SalesOrderID
,[RevisionNumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
,[Status]
,[OnlineOrderFlag]
,[SalesOrderNumber]
,[PurchaseOrderNumber]
,[AccountNumber]
,[CustomerID]
,[ContactID]
,[SalesPersonID]
,[TerritoryID]
,[BillToAddressID]
,[ShipToAddressID]
,[ShipMethodID]
,[CreditCardID]
,[CreditCardApprovalCode]
,[CurrencyRateID]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[Comment]
,[rowguid]
,[ModifiedDate])
select
SalesOrderID
,[RevisionNumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
,[Status]
,[OnlineOrderFlag]
,[SalesOrderNumber]
,[PurchaseOrderNumber]
,[AccountNumber]
,[CustomerID]
,[ContactID]
,[SalesPersonID]
,[TerritoryID]
,[BillToAddressID]
,[ShipToAddressID]
,[ShipMethodID]
,[CreditCardID]
,[CreditCardApprovalCode]
,[CurrencyRateID]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[Comment]
,[rowguid]
,[ModifiedDate]
from AdventureWorks.Sales.SalesOrderHeader
where SalesOrderID%2= 0
option( querytraceon 610)
go

select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go
/*
Now there are a number of issues with the data
1. number of pages are 1004 instead of 799.
2.Avg page space used is 78.37% which is around 20% less
3.But good thing is hat avg fragmentation is 78.68% which means the script you created will rebuild this index.
*/

–I will rebuild the index

alter index all on mysoh rebuild with(online = on)
go

select * from sys.dm_db_index_physical_stats(db_id(),object_id(‘mysoh’),null,null,’DETAILED’)
go

/*
Much better now. There are around 802 pages
avg fragmentation is 1.24%
and avg space used is 98.24%
*/
/*
Conclusion: DO NOT JUST RELY ON THE COUNTER AVG FRAGMENTATION ALONE.BUT RATHER USE THIS COUNTER WITH AVG PAGE SPACE USED.

*/

Advertisements