SQL Server table fragmentation script SQL Server Tips by Burleson Consulting

*********************************************
-- up_bn_storage_tablediag7
********************************************
The diagnostic scripts that the DBA will need to run will vary 
quite a bit, depending on the version of the SQL Server that is 
being used. For SQL Server version 7.0, the up_bn_storage_tablediag7 and up_bn_storage_indexdiag7 procedures 
should be used:

IF OBJECT_ID('dbo.up_bn_storage_tablediag7') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.up_bn_storage_tablediag7
IF OBJECT_ID('dbo.up_bn_storage_tablediag7') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.up_bn_storage_tablediag7 >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.up_bn_storage_tablediag7 >>>'
END
go

CREATE Procedure up_bn_storage_tablediag7
@db sysname,
@filegroup sysname = '',
@tabowner sysname = '',
@scandlimit real = -1,
@lsflimit real = -1,
@extflimit real = -1,
@avgpdenslimit real = -1

as

Set NoCount ON

Declare
@username sysname,
@tablename sysname,
@rows int,
@start int,
@end int,
@ps int,
@es int,
@esw int,
@ape real,
@sd real,
@rat varchar(50),
@lsf real,
@esf real,
@bfp real,
@apd real,
@str nvarchar(500),
@tbl VARCHAR(50),
@osqlcall VARCHAR(255),
@logstring varchar(50),
@sql varchar(2000)

-- create temporary table to hold base table space information
CREATE TABLE #table_gen
(indid int NULL,
 owner sysname NULL,
 tablename sysname NULL,
 file_group nchar(128) NULL,
 table_reserved decimal(28,0) NULL,
 table_used decimal(28,0) NULL,
 table_rows int NULL)

-- create temporary table to hold table fragmentation information
CREATE TABLE #table_reorg_info
(
DBName varchar(50) NOT NULL,
Username sysname NOT NULL,
Tablename sysname NOT NULL,
PagesScnd int NOT NULL,
ExtentsScnd int NOT NULL,
ExtentSws int NOT NULL,
AvgPagesExt real NOT NULL,
ScanDensity real NOT NULL,
ExtRatio varchar(50) NOT NULL,
LogicalScnFrag real NOT NULL,
ExtScanFrag real NOT NULL,
AvgByteFree real NOT NULL,
AvgPageDens real NOT NULL
)

-- get initial table and space metadata using demographic filters passed in
select @sql = 'use [' + @db + '] 
select 
indid,
user_name(a.uid),
a.name,
filegroup_name(b.groupid),
b.reserved - isnull((select sum(convert(decimal(28,2),c.reserved)) 
                from sysindexes c where (c.indid > 1 and c.indid < 255) 
and c.id = b.id and upper(c.name) not like ''_WA_SYS_%''),0),
b.dpages,
rows 
from 
sysobjects a,
sysindexes b 
where 
a.id = b.id and 
a.type = ''U'' and 
b.indid in (0,1,255) and 
upper(b.name) not like ''_WA_SYS_%'''

if @filegroup <> ''
select @sql = @sql + ' and filegroup_name(b.groupid) = ''' + @filegroup + ''''

if @tabowner <> ''
select @sql = @sql + ' and user_name(a.uid) = ''' + @tabowner + ''''

insert into #table_gen exec (@sql)

-- declare cursor to obtain table fragmentation data
declare tab cursor for
SELECT 
distinct owner,
tablename
FROM
#table_gen

-- open cursor
open tab

-- loop through tables to get fragmentation information
FETCH NEXT
FROM 
tab
INTO 
@username, @tablename

-- create temporary table for fragmentation data

WHILE @@fetch_status = 0
BEGIN

--get fragmenation details

CREATE TABLE #fraginfo 
(rowid Int IDENTITY (1, 1), 
info VARCHAR(500))

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

SELECT 
@osqlcall = 'OSQL /w 500 /E /Q"DBCC SHOWCONTIG (' + 
@tbl + ') WITH NO_INFOMSGS" /d "' + @db + '"'

INSERT #fraginfo 
EXEC master..xp_cmdshell @osqlcall

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

select 
@ps = Convert(real,Ltrim(Rtrim(Substring(info, (@start+2), 
(@end - (@start+1)))))) 
from 
#fraginfo 
See code depot for full script
where 
 rowid = 4

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

select 
@es = Convert(real,Ltrim(Rtrim(Substring(info, (@start+2), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 5

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

select 
@esw = Convert(real,Ltrim(Rtrim(Substring(info, (@start+2),
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 6

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

select 
@ape = Convert(real,Ltrim(Rtrim(Substring(info, (@start+2), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 7

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

select 
@sd = Convert(real,Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 8

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

select 
@rat = Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start))))) 
from 
#fraginfo 
where 
rowid = 8

-- Add logic in case object doesn't have logical scan fragmentation (ie - no indexes)
select 
@logstring = Substring(info,1,9) 
from 
#fraginfo 
where 
rowid = 9
if @logstring = '- Logical'
begin
select 
@start = CHARINDEX(': ', info), 
@end = CHARINDEX('%', info) 
from 
#fraginfo 
where 
rowid = 9

select 
@lsf = Convert(real,Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 9

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

select 
@esf = Convert(real,Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start+1)))))) 
 from 
#fraginfo 
where 
rowid = 10

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

select 
@bfp = Convert(real,Ltrim(Rtrim(Substring(info, (@start+2), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 11

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

select 
    @apd = Convert(real,Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 12
end
else
begin
select 
@lsf = 0

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

select 
@esf = Convert(real,Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 9

select 
   @start = CHARINDEX(': ', info), 
@end = Len(info) 
from 
#fraginfo 
where 
rowid = 10

select 
@bfp = Convert(real,Ltrim(Rtrim(Substring(info, (@start+2), 
(@end - (@start+1)))))) 
from 
#fraginfo 
where 
rowid = 10

select 
@start = CHARINDEX(': ', info), 
@end = CHARINDEX('%', info) 
from 
#fraginfo 
where 
rowid = 11

select 
@apd = Convert(real,Ltrim(Rtrim(Substring(info, (@start+1), 
(@end - (@start+1)))))) 
  from 
#fraginfo 
where 
rowid = 11
end
-- save fragmentation details
Insert #table_reorg_info
(DBName,
Username,
Tablename,
PagesScnd,
ExtentsScnd,
       ExtentSws,
AvgPagesExt,
ScanDensity,
ExtRatio,
LogicalScnFrag,
ExtScanFrag,
AvgByteFree,
AvgPageDens)
values
(@db,
@username,
@tablename,
@ps,
      @es,
@esw,
@ape,
@sd,
@rat,
@lsf,
@esf,
@bfp,
@apd)

drop table #fraginfo

FETCH NEXT
FROM tab
INTO @username, @tablename

END

-- close and deallocate cursor
close tab
deallocate tab

-- present table space/fragmentation data if no fragmentation filters 
-- are specified or if logical/extent fragmentation limits are required
select @sql = 'select 
a.owner,
a.tablename,
file_group =
case 
when a.file_group IS NULL then ''LOG''
else a.file_group
end,
c.table_rows,
is_clustered = case c.indid
when 1 then ''Yes''
else ''No''
end,
table_reserved_kb = sum(convert(decimal(28,2),a.table_reserved * 8)),
table_used_kb = sum(convert(decimal(28,2),a.table_used * 8)),
table_free_kb = sum(convert(decimal(28,2),a.table_reserved * 8)) - 
sum(convert(decimal(28,2),a.table_used * 8)),
PagesScnd,
ExtentSws,
ExtentsScnd,
AvgPagesExt,
ScanDensity,
     ExtRatio,
LogicalScnFrag,
ExtScanFrag,
AvgByteFree,
AvgPageDens
from 
#table_gen a,
#table_reorg_info b,
#table_gen c
where 
a.owner = b.Username and
a.tablename = b.Tablename and
a.owner = c.owner and
a.tablename = c.tablename and
c.indid in (0,1) '

if @scandlimit >= 0
select @sql = @sql + 'and ScanDensity <= ' + convert(varchar,@scandlimit) + ' '

if @lsflimit >= 0
select @sql = @sql + 'and LogicalScnFrag >= ' + convert(varchar,@lsflimit) + ' '
 
if @extflimit >= 0
select @sql = @sql + 'and ExtScanFrag >= ' + convert(varchar,@extflimit) + ' '

if @avgpdenslimit >= 0
select @sql = @sql + 'and AvgPageDens <= ' + convert(varchar,@avgpdenslimit) + ' '

select @sql = @sql + ' group by a.owner,
a.tablename,
a.file_group,
c.table_rows,
c.indid,
PagesScnd,
ExtentSws,
ExtentsScnd,
AvgPagesExt,
ScanDensity,
ExtRatio,
LogicalScnFrag,
ExtScanFrag,
AvgByteFree,
AvgPageDens
order by 1,2'

exec (@sql)

-- drop temporary tables
DROP TABLE #table_gen
DROP TABLE #table_reorg_info

return
go
IF OBJECT_ID('dbo.up_bn_storage_tablediag7') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.up_bn_storage_tablediag7 >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.up_bn_storage_tablediag7 >>>'
go