Index fragmentation report in SQL Server 2005 and 2008

problem
While indexes can speed up Execution of queries several fold as they can make the querying process faster, there are overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or a ppended in a table. Also when do perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may OCCU R 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 Quer Y 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 is first built, little or no fragmentation should exist. Over time, as data was inserted, updated, and deleted, fragmentation levels on the underlying indexes could begin to rise. So let's see how it happens.

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

There is basically the types of fragmentation:

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

• Internal fragmentation  -Internal fragmentation occurs when there was too much free space in t He index pages. Typically, some free space was desirable, especially when the index was created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage x pages is when created. If the index pages are too fragmented, it'll cause queries to take longer (because of the extra reads required to find T He dataset) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) would cause page splits as discussed ABO ve, 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, the question is what to identify the fragmentation. For, purpose SQL Server 2005 and provide a dynamic management function (DMF) to determine index fragmentation Lev El. This new DMF (sys.dm_db_index_physical_stats) function accepts parameters such as the database, database table, a nd index for which your want to find fragmentation. There is several options that allow you to specify the level of detail so you want to see in regards to index Fragmenta tion, we'll 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 was 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 SPE Cific database. If you specify NULL fordatabase_id, you must also specify NULL for object_id, index_id, AND partition_number.
object_id	the default was 0 (NULL, 0, and default is EQ Uivalent 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 is equivalent values in this context) which specify to return information for AL l indexes for a base table or view. If you specify NULL for index_id, you must also specify NULL forpartition_number.
Partition_number	The default is 0 (NULL, 0, and default was 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's used to obtain statistics. Valid inputs is 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 a index, only the parent-level pages of the B-tree (which is, the pages above the leaf level) are scanned. In SQL Server: only the associated PFS and IAM pages of a heap is examined; The data pages of the heap is not scanned. In SQL Server 2005, all pages of a heap is scanned in LIMITED mode. SAMPLED -It returns statistics based on a 1 percent sample of all the pages in the the index or heap. If the index or heap has fewer than 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 intent-shared (was) table lock in comparison to DBCC Showcontig which required a Shared lock, also the Algorithm for calculating fragmentation are more precise than DBCC showcontig and hence it gives a more accurate result.

• For a index, one row is returned for each level of the b-tree in each partition (thisis the reason Mage below, for some indexes there is, or more than, and records for a single index; You can refer to the Index_depth column which tellsthe number of Index levels). For a heap, one row are returned for the In_row_data allocation unit of each partition. For large object (LOB) data, one row was 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 DA Tabase 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 their meaning returned by thesys.dm_db_index_physical_stats is given in the Belo W table.

Script:index Fragmentation Report Script

--to Find out fragmentation level of a given database--this query would give detailed information--caution:it may take ve Ry long time, depending on the number of tables in the DB UseAdventureWorksGOSELECT 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_per cent, Ips.record_count, Ips.ghost_record_count, Ips.fragment_count, Ips.avg_fragment_size_in_pages fromSys.dm_db_index_physical_stats (db_id(N'AdventureWorks'),NULL,NULL,NULL,'detailed') IPSJOINSys.tables ST with(NOLOCK) onIps.object_id =St.object_id JOINSys.indexes SI with(NOLOCK) onIps.object_id =SI.object_id andips.index_id=si.index_idWHEREst.is_ms_shipped= 0ORDER by 1,5GO --to Find out fragmentation level of a given database and table--this query would 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 PRINTN'Invalid Object';ENDELSEBEGIN SELECTIPS. Index_type_desc, Ips.avg_fragmentation_in_percent, Ips.avg_fragment_size_in_pages, IPS.avg_page_space_us Ed_in_percent, Ips.record_count, Ips.ghost_record_count, Ips.fragment_count, Ips.avg_fragment_size_ In_pages fromSys.dm_db_index_physical_stats (@db_id,@object_id,NULL,NULL,'detailed') asIPS;ENDGO

Returned Column	Description
avg_fragmentation_in _percent	it Indicates the amount of  external Fragmentation  you has for the given objects. the lower the number the better  -as This number approaches 100% the "more pages" and the "given" index that is 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 was, i.e. on average how all page in the index is ( internal Frag Mentation ). the higher the number the better   Speaking in terms of fragmentation and read-performance. To achieve optimal disk space use, this value should is close to 100% for a index that would not have many random inserts. However, an index, the has many random inserts and had very full pages would have a increased number of page splits. This causes more fragmentation. Therefore, in order to reduce page splits, the value should is less than percent.
Fragment_count	A fragment is made up of physically consecutive leaf pages and the same file for an allocation unit. An index have at least one fragment. The maximum fragments an index can has is equal to the number of pages in the leaf level of the index. The fragments the more data is stored consecutively.
Avg_fragment_size_in_pages	Larger fragments mean this 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

• Review Identify Database fragmentation in SQL Server vs SQL Server 2005 tip.

• Review sys.dm_db_index_physical_stats on MSDN.

• Once you has identified the high fragmentation level, the next step are to fix it, in my next tip I am going to write more About the different ways to fix high fragmentation levels.

http://www.mssqltips.com/sqlservertip/1708/index-fragmentation-report-in-sql-server-2005-and-2008/

Index fragmentation report in SQL Server 2005 and 2008