Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Index Fragmentation Report in SQL Server 2005 and 2008

MSSQLTips author Arshad Ali By:   |   Read Comments (20)   |   Related Tips: More > Fragmentation and Index Maintenance
Problem
While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor. In this article I am going to write about fragmentation and different queries to determine the level of fragmentation.

Solution
When indexes are first built, little or no fragmentation should exist. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes may begin to rise. So let's see how it happens.

When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new incoming data, SQL Server moves half of the data from the full page to a new page. The new page that is created is created after all the pages in the database. Therefore, instead of going right from one page to the next when looking for data, SQL Server has to go from one page to another page somewhere else in the database looking for the next page it needs. This is called index fragmentation.

There are basically two types of fragmentation:

  • External fragmentation - External, a.k.a logical,  fragmentation occurs when an index leaf page is not in logical order, in other words it occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server to perform extra work to return ordered results. For the most part, external fragmentation isn't too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.

  • Internal fragmentation - Internal fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find the dataset) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits as discussed above, which also require additional system resources to perform.

As we learned, heavily fragmented indexes can degrade query performance significantly and cause the application accessing it to respond slowly. So now the question is how to identify the fragmentation. For that purpose SQL Server 2005 and 2008 provide a dynamic management function (DMF) to determine index fragmentation level. This new DMF (sys.dm_db_index_physical_stats) function accepts parameters such as the database, database table, and index for which you want to find fragmentation. There are several options that allow you to specify the level of detail that you want to see in regards to index fragmentation, we will see some of these options in the examples below.

The sys.dm_db_index_physical_stats function returns tabular data regarding one particular table or index.

Input Parameter Description
database_id The default is 0 (NULL, 0, and DEFAULT are equivalent values in this context) which specify to return information for all databases in the instance of SQL Server else specify the databaseID from sys.databases if you want information about a specific database. If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.
object_id The default is 0 (NULL, 0, and DEFAULT are equivalent values in this context) which specify to return information for all tables and views in the specified database or else you can specify object_id for a particular object. If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.
index_id The default is -1 (NULL, -1, and DEFAULT are equivalent values in this context) which specify to return information for all indexes for a base table or view. If you specify NULL for index_id, you must also specify NULL for partition_number.
partition_number The default is 0 (NULL, 0, and DEFAULT are equivalent values in this context) which specify to return information for all partitions of the owning object. partition_number is 1-based. A nonpartitioned index or heap has partition_number set to 1.
mode mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.
  • LIMITED - It is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. In SQL Server 2008, only the associated PFS and IAM pages of a heap are examined; the data pages of the heap are not scanned. In SQL Server 2005, all pages of a heap are scanned in LIMITED mode.
  • SAMPLED - It returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
  • DETAILED - It scans all pages and returns all statistics.

Note

  • The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement. It requires only an Intent-Shared (IS) table lock in comparison to DBCC SHOWCONTIG which required a Shared Lock, also the algorithm for calculating fragmentation is more precise than DBCC SHOWCONTIG and hence it gives a more accurate result.

  • For an index, one row is returned for each level of the B-tree in each partition (this is the reason, if you look at image below, for some indexes there are two or more than two records for a single index; you can refer to the Index_depth column which tells the number of index levels). For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.

Example

Let's see an example. The first script provided below gives the fragmentation level of a given database including all tables and views in the database and all indexes on these objects. The second script gives the fragmentation level of a particular object in the given database. The details about the columns and its meaning returned by the sys.dm_db_index_physical_stats are given in the below table.

Script : Index Fragmentation Report Script

--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
--To Find out fragmentation level of a given database and table
--This query will give DETAILED information
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'Production.BillOfMaterials');
IF @object_id IS NULL 
BEGIN
   PRINT N'Invalid object';
END
ELSE
BEGIN
   SELECT IPS.Index_type_desc, 
      IPS.avg_fragmentation_in_percent, 
      IPS.avg_fragment_size_in_pages, 
      IPS.avg_page_space_used_in_percent, 
      IPS.record_count, 
      IPS.ghost_record_count,
      IPS.fragment_count, 
      IPS.avg_fragment_size_in_pages
   FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS;
END
GO

Returned Column Description
avg_fragmentation_in_percent It indicates the amount of external fragmentation you have for the given objects.

The lower the number the better - as this number approaches 100% the more pages you have in the given index that are not properly ordered.

For heaps, this value is actually the percentage of extent fragmentation and not external fragmentation.

avg_page_space_used_in_percent It indicates how dense the pages in your index are, i.e. on average how full each page in the index is (internal fragmentation).

The higher the number the better speaking in terms of fragmentation and read-performance. To achieve optimal disk space use, this value should be close to 100% for an index that will not have many random inserts. However, an index that has many random inserts and has very full pages will have an increased number of page splits. This causes more fragmentation. Therefore, in order to reduce page splits, the value should be less than 100 percent.

fragment_count A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. So the less fragments the more data is stored consecutively.
avg_fragment_size_in_pages Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance.

Next Steps



Last Update: 3/16/2009


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, November 17, 2014 - 7:51:19 PM - Ignacio Jose Read The Tip

Hi Arshad, thank you for the index fragmentation script, I have been using it lately. 

it was confusing to run the report again to see how much fragmentation there was after I rebuilt the indexes because it was not showing the data in the right order so that it will show the most relevant data first where there is a large number of records or extents Eg. order by 1, 7 instead of order by 1, 5

I also found the sys.dm_db_index_physical_stats function report column name Fragment_Count confusing as it's referring to chunks of data (pages or extents) rather than fragmentation, since we are talking about fragmentation why use the word Fragment in this terms?

Never the less the report is really handy at it made a lot so sense once I got the order by correct.

Regards, Ignacio


Tuesday, September 23, 2014 - 4:13:54 AM - mohit rathod Read The Tip

Fragmentation process how to implement in real time system

 


Thursday, September 18, 2014 - 7:15:37 AM - yahlaa Read The Tip

 

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'Database');
SET @object_id = OBJECT_ID(N'dbo.TableName');
IF @object_id IS NULL
BEGIN
   PRINT N'Invalid object';
END
ELSE
BEGIN
   SELECT IPS.Index_type_desc,
      IPS.avg_fragmentation_in_percent,
      IPS.avg_fragment_size_in_pages,
      IPS.avg_page_space_used_in_percent,
      IPS.record_count,
      IPS.ghost_record_count,
      IPS.fragment_count,
      IPS.avg_fragment_size_in_pages
   FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS;
END
GO

 

When I run the script its shows:

Msg 297, Level 16, State 12, Line 11
The user does not have permission to perform this action.

 

what does level 16 state 12 and line 11 means

 

Mgs 297 means I have no access.

 

Pls help. Im using sql server 2008


Friday, May 30, 2014 - 7:29:28 AM - Alex_B Read The Tip

According to avg_page_space_used_in_percent definition, I have a question about index random inserts. In order to evaluate avg_page_space_used_in_percent correctly, how can I retrieve and decrease the level of randomness of a separate index inserts?
Thank you 


Monday, April 22, 2013 - 1:45:13 AM - Luiz Mercante Read The Tip

Hello Guys. It is a really nice article. Here is the script adapted to server with a lot of databases:

DECLARE @db_id int;
DECLARE db_cursor CURSOR FOR
SELECT dbid
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_id

WHILE @@FETCH_STATUS = 0
BEGIN
select DB_NAME(@db_id) as [Database]
,object_name(IPS.[object_id]) as [TableName]
,SI.index_id as [SI.index_id]
,SI.name AS [IndexName]
,IPS.index_type_desc
,IPS.index_id
,IPS.avg_fragmentation_in_percent
,IPS.avg_page_space_used_in_percent
,IPS.record_count
,IPS.fragment_count
from sys.dm_db_index_physical_stats(@db_id, null, null, null, 'detailed') as IPS
inner join sys.indexes as SI with (nolock) on IPS.[object_id] = SI.[object_id] and IPS.index_id = SI.index_id
inner join sys.tables as ST with (nolock) on IPS.[object_id] = ST.[object_id]
where ST.is_ms_shipped = 0
and IPS.avg_fragmentation_in_percent>=10 
and IPS.page_count>25 
and IPS.index_type_desc<>'heap' 
and IPS.index_level=0 
order by IPS.avg_fragmentation_in_percent desc

FETCH NEXT FROM db_cursor INTO @db_id
END
CLOSE db_cursor

DEALLOCATE db_cursor

 

Regards!!! 


Monday, September 24, 2012 - 3:07:10 AM - Anandan Read The Tip

Arshad,

Thanks for the useful article.

I am using this script to generate and send Index Fragmentation of few production databases in our company. I understand that the Row count here is approximate.

For a requirement, we need to get the row count along with Index Fragmentation details. Kindly help us how can we get the estimated row count here.

Thanks in advance.


Wednesday, August 29, 2012 - 6:28:59 AM - Dhanapal Selvaraj Read The Tip

What is the best approach/ method to get high avg_fragment_size_in_pages any partiitoned table. ?

My table design is : I have a partitoned table with weekly boundary from 2000 to 2012 year (676 partitions), each partition holds almost 110 milion rows.

I am getting very low fragment numbers, which means,

fragment_count avg_fragment_size_in_pages page_count
150675 7.999953542 1205393

Please help me on this

 

 


Monday, August 13, 2012 - 3:02:32 AM - Arshad Read The Tip

It happes when you have only few records in your table and its absolutely fine. When your table grows in size you will start seeing difference.

Now if you are wodering why its happening, refer this article for your reference:

http://sqlserver2005tutorial.blogspot.com/2011/03/what-is-mixed-extent.html

http://msdn.microsoft.com/en-us/library/ms190969(v=sql.90).aspx


Thursday, July 26, 2012 - 7:15:09 AM - Darshan Read The Tip

After Rebuild indexes still i am getting fragmentation on some table. what to do now? Do i drop and recreate index or any other way ?


Tuesday, June 26, 2012 - 6:24:39 AM - Fabio Read The Tip

Very good article

Thanks

Fabio

 

www.names4fun.com


Wednesday, June 13, 2012 - 9:00:43 AM - William Read The Tip

I saw some duplicate index names. You might want to include leaf-levels only:

select object_name(IPS.[object_id]) as [TableName]
    ,SI.index_id as [SI.index_id]
    ,SI.name AS [IndexName]
    ,IPS.index_type_desc
    ,IPS.index_id
    ,IPS.avg_fragmentation_in_percent
    ,IPS.index_level
    ,IPS.avg_fragment_size_in_pages
    ,IPS.avg_page_space_used_in_percent
    ,IPS.record_count
    ,IPS.ghost_record_count
    ,IPS.fragment_count
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') as IPS
inner join sys.indexes as SI with (nolock) on IPS.[object_id] = SI.[object_id] and IPS.index_id = SI.index_id
inner join sys.tables as ST with (nolock) on IPS.[object_id] = ST.[object_id]
where ST.is_ms_shipped = 0
and IPS.avg_fragmentation_in_percent>=10 -- allow limited fragmentation
and IPS.page_count>25 -- ignore small tables
and IPS.index_type_desc<>'heap' -- ignore heaps
and IPS.index_level=0 -- leaf levels only
order by IPS.avg_fragmentation_in_percent desc


Saturday, May 05, 2012 - 3:22:20 AM - alpesh Read The Tip

PLEASE HELP ME HOW I SEE VIEW SYMTEX IN SQL SERVER 2000. FIRST I MAKE VIEW QUERY IN SQL SERVER 2000. NOW I WANT SEE WHICH TYPE OF TABLE I USED IN THIS QUERY. THAT TIME I AM VERY CONFUESED. I DO NOT KNOW HOW I SEE MAIN QUERY CODE.

IF YOU HAVE ANY IDEA, SO PLEASE HELP ME.

 

 

 

 

 


Saturday, May 05, 2012 - 3:18:16 AM - alpesh Read The Tip

My SQL 2000 query is how to open wiews symtex main query.

 


Friday, March 02, 2012 - 8:14:04 AM - Golam Kabir Read The Tip

Very good article - very helpful


Friday, November 11, 2011 - 7:37:48 PM - RajeshV Read The Tip

Nice article. Thanks, Arshad. I was going to write about the missing index_level column, and I noticed it in the comments. IMHO, it may help future visitors of this page if the query is updated to reflect the additional useful columns (alloc_unit_type_desc, index_depth, index_level). Also, an interesting point is that only the leaf level (level=0) gets defrag'ed after doing an ALTER INDEX REORGANIZE - BOL makes this clear too, but it may catch an unaware person by surprise. Thanks again for the post.


Monday, August 29, 2011 - 5:04:11 PM - Golam Kabir Read The Tip

Well explained - pls keep writing


Friday, June 24, 2011 - 10:36:18 PM - Arshad Read The Tip

Please refer to the note section of the article, it has details:

Snippets from the article:

For an index, one row is returned for each level of the B-tree in each partition (this is the reason, if you look at image below, for some indexes there are two or more than two records for a single index; you can refer to the Index_depth column which tells the number of index levels). For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.

You can include alloc_unit_type_desc, index_depth, index_level columns as well.

Sample results:

TableNameIndexNameIndex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percent
SalesOrderDetailAK_SalesOrderDetail_rowguidNONCLUSTERED INDEXIN_ROW_DATA300.737100737
SalesOrderDetailAK_SalesOrderDetail_rowguidNONCLUSTERED INDEXIN_ROW_DATA310
SalesOrderDetailAK_SalesOrderDetail_rowguidNONCLUSTERED INDEXIN_ROW_DATA320
SalesOrderDetailIX_SalesOrderDetail_ProductIDNONCLUSTERED INDEXIN_ROW_DATA201.327433628
SalesOrderDetailIX_SalesOrderDetail_ProductIDNONCLUSTERED INDEXIN_ROW_DATA210
SalesOrderDetailPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailIDCLUSTERED INDEXIN_ROW_DATA300.32388664
SalesOrderDetailPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailIDCLUSTERED INDEXIN_ROW_DATA3175
SalesOrderDetailPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailIDCLUSTERED INDEXIN_ROW_DATA320

Friday, June 24, 2011 - 5:02:49 PM - BewareOf Script Read The Tip

NOTEs on the scripts. 

On the full database script I got a syntax error on db_id (

Second after plugging in the correct dbid litteral and running, my output from this query in detailed mode returns multiple lines per index... and to make it more complicated they all have different number with regard to fragmentation levels...

Sorry just want to let folks know to debug this before using or you may be rebuilding more than you need too...

for example:

AWB_DIM PK__AWB_DIM__34C8D9D1 CLUSTERED INDEX 70.37037037 1.096446701 35.77410427 26078
AWB_DIM PK__AWB_DIM__34C8D9D1 CLUSTERED INDEX 2.063041644 8.739276139 97.36968125 4195419
AWB_DIM PK__AWB_DIM__34C8D9D1 CLUSTERED INDEX 0 1 64.02273289 216

 


Sunday, March 22, 2009 - 3:27:49 AM - arshad0384 Read The Tip

Thanks for your appreciation.

Once you have identified the high fragmentation level, the next step is to fix it, in my next tip I am going to write more about the different ways to fix high fragmentation levels. I will update the thread soon with URL of upcoming tip on fixing fragmentation.


Saturday, March 21, 2009 - 3:50:05 PM - prabhupr Read The Tip

Excellent Article.

Can you please add information on HOW to read the output of this SQL, I mean what action should somebody take based on the result, what are the counters people should look for.  Dont have to explain everything, but may be pointing to URLs that speak the continuity can be a value add to this URL




 
Sponsor Information