Check Database Fragmentation

Just thought it would be useful if we have a query to check the overall fragmentation percentage on our database. Just like DBCC showcontig gives fragmentation info at table level,it would be of some help to

check the same at database level. So here we go..

SELECT Sum(Round ((avg_page_space_used_in_percent / 100)

* page_count,0)) AS [Total Pages Used],

Sum(page_count) AS [Available Pages],

(Sum(Round((avg_page_space_used_in_percent / 100) * page_count, 0)) /

Sum(page_count)) * 100 AS [Percentage Used ],

Max(CASE WHEN index_type_desc IN ('HEAP')

AND record_count > 1000 THEN avg_fragmentation_in_percent ELSE 0 END)

AS [Maximum Extent Fragmentation],

Sum(CASE WHEN index_type_desc IN ('HEAP')

AND record_count > 1000 AND avg_fragmentation_in_percent > 10

THEN 1 ELSE 0 END) AS [No_of_Extent_Fragmented_Tables],

Sum(CASE WHEN index_type_desc NOT IN ('HEAP')

THEN (avg_fragmentation_in_percent * page_count) ELSE 0 END) /

Sum(CASE WHEN index_type_desc NOT IN ('HEAP') THEN page_count

ELSE 0 END) AS [Logical Fragmentation in %]

FROM sys.Dm_db_index_physical_stats(Db_id(),NULL,NULL,NULL,'DETAILED')

The script gives the following details:

Page Usage: It is the sum of used sections of each page in each table/index in entire the database. It is expressed as pages.

Available Pages: It is the sum of total number of pages available in the database.

Percentage Used: Ratio of Used Pages and Available Pages. Percentage Used is exactly the same number as Percentage Density seen in the result of DBCC Showcontig statement. But, this one is at the Database level but ShowContig is at table level. Percentage Used should be some where around 90 - 100% which means most of the pages are full and they are getting fully used.But the percentage also heavily depends on the fill factor selected when the indexes are designed.So the expected output for your database should be either close to Fill factors selected for huge tables or in excess of 90%. ( Default setting is 0% when fillfactors are not selected. 0% actually means 100% which implies that all your data pages can be 100% full and index pages can have one index entry space free ).Sudden variations in Percentage_Usage can cause a performance dip and hence its important for a DBA to monitor on a daily basis.

Maximum Extent Fragmentation: Maximum Extent Fragmentation indicates the highest extent fragmentation percentage obtained among all the tables with more than 1000 rows atleast. Extent Fragmentation is calculated by finding the ratio of out of order Extents and total number of extents in the table. Extent Fragmentation expressed here is not a cumlative figure for the entire database but highest experienced among the bigger tables of the database. If the highest is indicated as a value more than 10% then one can execute the script provided below to find the table with the high extent fragmentation and act accordingly. Ideal number expected is 0%. Less than 10% is acceptable.

No_of_Extent_Fragmented_Tables: No_of_Extent_Fragmented_Tables indicates Total number of tables that experience high extent fragmentation. Again tables with atleast 1000 rows and a fragmentation % greater than 10% are taken into consideration. If there more number of tables ( say even around 3-5 ) one needs to immediately find out the tables and fix the same.

Logical Fragmentation in %: Logical Fragmentation is the ratio of out of order pages and total number of pages. Best is 0%. Less than 10% is acceptable. These figures can have variations because updates/inserts/deletes. When such variations are detected its better to check the same figures at table level. The script provides a percentage of out of order pages at the database level. Table Reorg or Rebuilding indexes are couple of options for fixing fragmentation. Below is the script to check fragmentation at each object level.

SELECT Object_name(object_id) AS objectname,

Sum(Round((avg_page_space_used_in_percent / 100 ) * page_count,0))

AS used_pages,

Sum(page_count) AS available_pages ,

(Sum(Round((avg_page_space_used_in_percent / 100 ) * page_count ,0)) /

Sum(page_count) ) * 100 AS percentage_used,

Sum( CASE WHEN index_type_desc IN ('HEAP')

THEN (avg_fragmentation_in_percent * fragment_count) ELSE 0 END)/

CASE WHEN Sum ( CASE WHEN index_type_desc IN ('HEAP')

THEN fragment_count ELSE 0 END ) = 0 THEN 1

ELSE Sum (CASE WHEN index_type_desc IN ('HEAP')

THEN fragment_count ELSE 0 END ) END AS extent_fragmentation ,

Sum( CASE WHEN index_type_desc NOT IN ('HEAP')

THEN ( avg_fragmentation_in_percent * page_count) ELSE 0 END) /

CASE WHEN Sum ( CASE WHEN index_type_desc NOT IN ('HEAP')

THEN page_count ELSE 0 END ) = 0 THEN 1 ELSE

Sum ( CASE WHEN index_type_desc NOT IN ('HEAP')

THEN page_count ELSE 0 END ) END AS logical_fragmentation

FROM

sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL ,

'DETAILED') WHERE avg_page_space_used_in_percent !=0

GROUP BY Object_name(object_id)

ORDER BY Object_name(object_id)

To avoid divide by zero error empty tables are not taken into account. PS: Noticed that db_id() at times throws errors. In that case find your database id from sysdatabases and hardcode on your query.

Forgive me for the formatting of the query. Really tried my best but couldnt help it.Any tips on the same will be appreciated.