Check Database Fragmentation -SQL 2000

Again as requested, I have come up with a script to check
fragmentation for SQL 2000. This one as expected works using DBCC
Showcontig.Fairly simple script. Let us take a look

CREATE TABLE #fraglist

(

ObjectName CHAR(255),

ObjectId INT ,

IndexName CHAR(255),

IndexId INT ,

Lvl INT ,

CountPages INT ,

CountRows INT ,

MinRecSize INT ,

MaxRecSize INT ,

AvgRecSize INT ,

ForRecCount INT ,

Extents INT ,

ExtentSwitches INT ,

AvgFreeBytes INT ,

AvgPageDensity INT ,

ScanDensity DECIMAL ,

BestCount INT ,

ActualCount INT ,

LogicalFrag DECIMAL ,

ExtentFrag DECIMAL

);

INSERT

INTO #fraglist EXEC

(

'DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES'

)

SELECT SUM((Countpages * 8 ))/1024.0 AS Available_Space_in_MB,

CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 AS Space_Used_in_MB ,

(CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 )/( SUM((Countpages * 8 ))/1024.0 ) * 100 AS Percentage_Used ,

( SUM(

CASE

WHEN indexid IN (0,255)

THEN 0

ELSE CountPages * LogicalFrag

END /100.0) / SUM(CountPages ) * 100) [Total_Logical_Fragmentation_in_%],

( SUM(

CASE

WHEN indexid IN (0,255)

THEN 0

ELSE Extents * ExtentFrag

END /100.0)/ SUM(Extents ) * 100) AS [Total_Extent_Fragmentation_in_%],

(CONVERT(DECIMAL,SUM(BestCount))/CONVERT(DECIMAL,SUM(ActualCount))) * 100 AS Scan_Density

FROM #fraglist

WHERE ActualCount!=0

AND Extents !=0

AND CountPages !=0

Let me run thro this script briefly. A temporary table
#fraglist is created.

The Showcontig results for each table in the database are dumped
into temp table using the command

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES.

A query to give a consolidated report at database level is ran
against the temp table.

A quick run thro results provided. Most of columns have the same
definition as provided in my script for SQL 2005.But, there are few differences as well.

Available_Space_used_in_MB: Available_Space_used_in_MB is the sum of the space allocated to each table/index in the database.Available_Space_used_in_MB has
the same definition as the one in SQL 2005 script but the difference is that in 2k Script I have
expressed it in MB, but in 2k5 script I have expressed it in pages.Also, realise the difference between Available_Space_used_in_MB and the actual database size. The actual database size would include the space occupied by dropped objects and deleted objects. Unless you shrink your database, the database doesnt release the space occupied by dropped objects or deleted
rows. So, total database size would be always greater than the Available_Space_used_in_MB value provided by this query.

Space_Used_in_Mb: Space_Used_in_Mb is the sum of space actually used by each table.

Percentage Used: Percentage of Available_Space_used_in_MB used. Expected to be around 90%+ or close to the fillfactors set for your bigger tables.

Total_Logical_Fragmentation_in_%: Total_Logical_Fragmentation_in_%
percentage of out of order pages. As mentioned in my previous post less than 10% is
acceptable. Logical fragmentation applies only for indexed tables. and for not for heaps. If
logical fragmentation is greater than 30% go for a Rebuild or if its between 10 to 30% then DBCC INDEXDEFRAG(Databasename,tablename) would do.

Total_Extent_Framentation_in_%:
Total_Extent_Framentation_in_% refers to the percentage of extents that are out of order. Extent Fragmentation displayed by Showcontig doesnt apply for heaps.Please note the difference between script used for SQL 2k5 and 2k. In 2k5, sys.Dm_db_index_physical_stats
shows Extent fragmentation values only for heaps and not for indexed tables. But in Showcontig used in SQL 2000, Extent fragmentation applies only for indexed tables and not for
heaps at all. Again as mentioned before acceptable value for extent fragmentation is less than 10%.

Scan Density: Scan density is the ratio of minimum number of extents required to hold the table ( page count/8) and the number of extent switches required by the DBCC command to
scan the table, expressed in %. Extent switches basically represent the number of jumps made from one extent to another while accessing the entire table. Scan density Percentage is
common for both indexed tables and heaps. So, Scan density percentage can be used to measure the fragmentation percentage on heaps as Extent and logical fragmentation only apply for
indexed tables. Ideally, Scan density is expected to be in excess of 90%. I have noticed that the scan density provided by Showcontig is not always the exact ratio of minimum number of extents/Extent Switches as mentioned in BOL. But definitely a useful parameter to get an idea of fragmentation levels in the tables

Analyze the parameters specified above and if high values are obtained, then execute the query below on the same temp table to get fragmentation values at table level.

SELECT Objectname ,
SUM((Countpages * 8 ))/1024.0 AS Available_Space_in_MB,
CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 AS Space_Used_in_MB ,
(CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 )/( SUM((Countpages * 8 ))/1024.0 ) * 100 AS Percentage_Used ,
( SUM(
CASE
WHEN indexid IN (0,255)
THEN 0
ELSE CONVERT( DECIMAL,CountPages * LogicalFrag)
END /100.0) / SUM(CONVERT(DECIMAL,CountPages) ) * 100.0) [Total_Logical_Fragmentation_in_%], ( SUM(
CASE
WHEN indexid IN (0,255)
THEN 0
ELSE CONVERT( DECIMAL,Extents * ExtentFrag)
END /100.0)/ SUM(CONVERT(DECIMAL,Extents) ) * 100.0) AS [Total_Extent_Fragmentation_in_%],
(CONVERT(DECIMAL,SUM(BestCount)) /CONVERT(DECIMAL,SUM(ActualCount))) * 100.0 AS Scan_Density
FROM #fraglist
WHERE ActualCount!=0
AND Extents !=0
AND CountPages !=0
GROUP BY objectname
--DROP TABLE #fraglist

As mentioned earlier the #fraglist table contains the entire resultset of showcontig and has details like

Row count, Min/Max and Avg Row size, AvgFreeBytes etc. Refer to them while doing a detailed analysis.

Though this script is 2k5 and 2000 compatible, DMVs are definitley better. The major drawback with ShowContig is that it is not an online operation which implies that users can only select and not insert/update/delete when showcontig is executed on a particular table. Also noticed that DMVs are faster as well.

But anyways, this script would be extremly useful if you can spare a few minutes on your maintainence window.