Tracking FRAGMENTATION in tables in SQL Server Database


Here’s a diagnostic table and stored procedure to track the fragmentation of large tables within databases. The user passes the database name and the table name as parameters to the SP. The SP can be set up to run as a scheduled job every few hours to produce a historical trend of fragmentation.

While DBCC SHOWCONTIG produced the data I needed, it certainly wasn’t in a friendly form for storage. I use the executable file – OSQL.exe – to process the DBCC command and save its results to a temporary table. The Identity column (Record_Id) allows me to differentiate between the rows so I can apply specific parsing on the target row/content. I’ve augmented the data from SHOWCONTIG to include the Rowcount from the specified table.

_____________________________________________________________________________________________

use Master
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[dg_TableFragmentation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dg_TableFragmentation]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[tb_FragTrack_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb_FragTrack_log]
GO

CREATE TABLE [dbo].[tb_FragTrack_log] (
	[Record_id] [int] IDENTITY (1, 1) NOT NULL ,
	[Date_time] [datetime] NOT NULL ,
	[DBName] [varchar] (50) NOT NULL ,
	[Tablename] [varchar] (50) NOT NULL ,
	[TableRows] [int] NOT NULL ,
	[PagesScnd] [int] NOT NULL ,
	[ExtentsScnd] [int] NOT NULL ,
	[ExtentSws] [int] NOT NULL ,
	[AvgPagesExt] [real] NOT NULL ,
	[ScanDensity] [real] NOT NULL ,
	[ExtRatio] [varchar] (20) NOT NULL ,
	[LogicalScnFrag] [real] NOT NULL ,
	[ExtScanFrag] [real] NOT NULL ,
	[AvgByteFree] [real] NOT NULL ,
	[AvgPageDens] [real] NOT NULL
) ON [PRIMARY]
GO

CREATE Procedure dg_TableFragmentation @db varchar(50), @table varchar(50)

as

/** 	This Procedure will Run the DBCC SHOWCONTIG command against the table specified in the database specified.
	The data from the DBCC Command is augmented with the rowcount for the target table and saved with a Date_time.
	The goal is to produce a history of fragmenation change over time on a per table basis.

	Ralph Clark - Plantware,Inc 5/2/02
**/

Set NoCount ON

Declare @rows int,
	@start int,
	@end int,
	@ps int,
	@es int,
	@esw int,
	@ape real,
	@sd real,
	@rat varchar(10),
	@lsf real,
	@esf real,
	@bfp real,
	@apd real,
	@str nvarchar(500)

--get rows in target table
Set @str = 'SELECT @rows = rows FROM ' + @db + '.dbo.sysindexes WHERE id = OBJECT_ID(' + CHAR(39) + @db +'.dbo.' + @table + Char(39) + ') AND indid < 2'

Exec sp_ExecuteSql @str, N'@rows Int Out', @rows Out

--get fragmenation details
CREATE TABLE #cnt (Record_id Int IDENTITY (1, 1),  info VARCHAR(100))

DECLARE @tbl VARCHAR(15), @parm VARCHAR(255)

SELECT @tbl = CONVERT(VARCHAR(50), OBJECT_ID((@db + '..'+ @table)))

SELECT @parm = 'OSQL /E /Q"DBCC SHOWCONTIG(' + @tbl + ')" /d ' + @db

INSERT #cnt EXEC master..xp_cmdshell @parm

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 4

select @ps = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 4

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 5

select @es = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 5

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 6

select @esw = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 6

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 7

select @ape = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 7

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 8

select @sd = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 8

select @start = CHARINDEX('% [', info), @end =  CHARINDEX(']', info, @start) from #cnt where record_id = 8

select @rat = Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start))))) from #cnt where record_id = 8

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 9

select @lsf = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 9

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 10

select @esf = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 10

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 11

select @bfp = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 11

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 12

select @apd = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 12

Drop table #cnt

--save results

Insert PlantQueDG.dbo.tb_FragTrack_log
	(Date_time,
	DBName,
	Tablename,
	TableRows,
	PagesScnd,
	ExtentSws,
	ExtentsScnd,
	AvgPagesExt,
	ScanDensity,
	ExtRatio,
	LogicalScnFrag,
	ExtScanFrag,
	AvgByteFree,
	AvgPageDens)
values
	(getdate(),
	@db,
	@table,
	@rows,
	@ps,
	@es,
	@esw,
	@ape,
	@sd,
	@rat,
	@lsf,
	@esf,
	@bfp,
	@apd)

return
GO
__________________________________________________________________________________________________________
Advertisements

One response

  1. Good one..

    – Perika

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: