Navigation
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
More options
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Setup a configurable Re-Index Solution
Maintaining Indexes
Setup a configurable Re-Index Solution
by
hmckillop
Posted
(Edited
)
This is going to be a long faq, but it should be worth it if you spend the time. This faq will display a potential method of maintaining your database indices to the precise level you require. It will allow for managing indices at a table level or at an individual index level,also re-indexing using indexdefrag or dbreindex depending on the requirements, and all of this on a series of relatively small tables.
We use this as our overall database index strategy across multiple databases.
Firstly we need to create the tables to hold the data.
Table1 is used to hold the list of indices you require to have non-default configuration. The re-indexing works on the understanding
that anything not in this table will be re-indexed when it meets the default conditions - shown later
The second and third tables hold the results from the DBCC ShowContig and will be populated every time the job usp_Defrag_report is run.
The actual code to populate these tables and re-index the database(s) are managed through 3 stored procedures. We have created jobs
on our production server to execute these procs nightly, but you can get them to run as often as you like.
The first procedure is described in the header.
This next proc is called from the usp_defrag proc and is used to get the scan densities for all table indexes where they are below a threslhold
The threshold is either the default - specified in parameter "target", or it is captured from the Fragmented_indexes_XConfig. Again
further description is in the header.
The last proc is then used to query the fragmented_indexes table and then perform any defragging necessary
This series of procs/jobs has evolved over time - things like configuration at an individual index level using the XConfig table was added
to give the flexibility to the DBA or programmers to maintain indices individually.
I hope this helps and you found it worthwhile.
We use this as our overall database index strategy across multiple databases.
Firstly we need to create the tables to hold the data.
Table1 is used to hold the list of indices you require to have non-default configuration. The re-indexing works on the understanding
that anything not in this table will be re-indexed when it meets the default conditions - shown later
Code:
use msdb
go
if exists (select * from msdb.dbo.sysobjects where name = 'Fragmented_Indexes_XConfig' and type = 'U')
drop table dbo.Fragmented_Indexes_XConfig
go
create table msdb.dbo.Fragmented_Indexes_XConfig (
DBName sysname NOT NULL,
ObjectName varchar(255) NOT NULL,
IndexName varchar(255) NOT NULL,
NewFill tinyint NULL,
ScanDensity tinyint NULL)
go
alter table msdb.dbo.Fragmented_Indexes_XConfig
add constraint PK_XConfig primary key clustered
(DBName, ObjectName, IndexName)
with fillfactor = 70The second and third tables hold the results from the DBCC ShowContig and will be populated every time the job usp_Defrag_report is run.
Code:
-- Must get rid of any old v1 & 2 Fragmented_Indexes table
if object_id('msdb.dbo.Fragmented_Indexes') is not null
drop table msdb.dbo.Fragmented_Indexes
go
-- Table modified v2 & v3
create table msdb.dbo.Fragmented_Indexes (
DBName sysname,
ObjectName VARCHAR (255),
ObjectId INT,
IndexName VARCHAR (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,
OrigFill tinyint)
go
Code:
if exists (select * from msdb..sysobjects where name = 'Fragmented_Indexes_History' and type = 'U')
drop table Fragmented_Indexes_History
go
create table msdb..Fragmented_Indexes_History (
DBName sysname,
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,
Origfill Tinyint,
start_time datetime,
end_time datetime,
newfill int)The actual code to populate these tables and re-index the database(s) are managed through 3 stored procedures. We have created jobs
on our production server to execute these procs nightly, but you can get them to run as often as you like.
The first procedure is described in the header.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_frag_report]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_frag_report]
GO
/*=========================================================================================
Description:
Calls usp_get_dnsty for each user DB in instance to fill msdb..Fragmented_Indexes with
all indexes with scan density less than @target
Notes:
Still need to look into _WA_Sys auto-statistics to see if they should be excluded. Also
need to investigate phenomena of fragmented non-clustered indexes on non-fragmented
clustered tables.
Installation:
msdb
Processing Steps:
If msdb..Fragmented_Indexes doesn't exist, create it
open cursor for all user DB names
call usp_get_dnsty for each with specified target scan density
Tables Used:
msdb.dbo.Fragmented_Indexes
master.dbo.sysdatabases
Parameters:
@target (tinyint, def: 85) scan density below which table/index is considered as execessively fragmented
@alert (bit, def: 1) 1: raiserror if fragmentation found; 0: silent execution - no raiserror
@minpages (int, def: 40)
=========================================================================================*/
create procedure usp_frag_report (
@target decimal(5,2) = 85.00,
@alert bit = 1,
@minpages int = NULL
)
as
begin
declare @dbname sysname
declare @frag_count int
declare @message varchar(30)
declare @err int
set nocount on
-- clear report table
if exists (select * from msdb..sysobjects where name = 'Fragmented_Indexes' and type = 'U')
truncate table Fragmented_Indexes
else
create table msdb..Fragmented_Indexes (
DBName sysname,
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,
Origfill Tinyint)
declare iw_dbcursor cursor for
select name
from master.dbo.sysdatabases
where name not in ('master', 'model', 'tempdb', 'distribution', 'msdb')
and (status & 1024) = 0 -- exclude DBs with read-only flag set - includes log shipped
order by dbid
open iw_dbcursor
fetch next from iw_dbcursor into @dbname
while @@fetch_status = 0
begin
print @dbname
if @minpages is null
exec @err = usp_get_dnsty @dbname, @target
else
exec @err = usp_get_dnsty @dbname, @target, @minpages
fetch next from iw_dbcursor into @dbname
end
close iw_dbcursor
deallocate iw_dbcursor
if object_id('msdb.dbo.Fragmented_Indexes') is not null
select @frag_count = count(distinct dbname) from Fragmented_Indexes
if isnull(@frag_count, 0) > 0 and @alert = 1
begin
set @message = convert(varchar, @frag_count) + ' database' + case when @frag_count > 1 then 's' else '' end + ' on Server ' + @@SERVERNAME
--raiserror(50016, 18, 1, @message) -- SQL_Admin error 50016 must be installed
end
end
GOThis next proc is called from the usp_defrag proc and is used to get the scan densities for all table indexes where they are below a threslhold
The threshold is either the default - specified in parameter "target", or it is captured from the Fragmented_indexes_XConfig. Again
further description is in the header.
Code:
use msdb
go
if object_id('usp_get_dnsty') is not null
drop proc usp_get_dnsty
go
/*=========================================================================================
Description:
Get scan densities for all table indexes in a SQL2K DB where scandensity < @target
Store results in (v3.0) msdb..Fragmented_Indexes
Depends On:
msdb..Fragmented_Indexes
Notes:
uses undocumented procedure sp_MSForEachTable
Installation:
msdb
Processing Steps:
create temp table #fraglist
fill with dbcc showcontig for all tables in target DB (with tableresults, all_indexes, all_levels)
insert into msdb..Fragmented_Indexes all records (except BLOB and heap pointers) where scandensity is below specified @target,
extentswitches > 0 and extents >= specified minimum extents (i.e. if your table only takes up 0.5 an extent it'll always look
50% fragmented even though it's not - similar distortion is also seen with tiny tables under 3 or 4 extents)
Tables Used:
msdb..Fragmented_Indexes
creates and drops #fraglist
Parameters:
@dbname (sysname) name of database to get densities for
@target (numeric 5,2 def: 85.00) scan density below which table/index is considered as execessively fragmented
@minpages (int def: 40) minimimum number of pages below which table/index fragmentation not significant
Return Value:
-1: error - invalid target DB; 0: OK
Called By:
usp_frag_report
Calls:
*sp_MSForEachTable
CHANGE NOTES:
Add Fragmented_Indexes_XConfig to allow specification of
divergent specific scandensities for individual tables
Removed WITH ALL_LEVELS option from DBCC SHOWCONTIG call
cos it's buggy (Level column is always 0) and it doesn't
really help us anyway.
=========================================================================================*/
create procedure usp_get_dnsty (
@dbname sysname,
@target decimal(5,2) = 85.00,
@minpages int = 40
)
as
set nocount on
declare @err int, @cmd varchar(1000)
-- Leave system databases alone
if @dbname = 'master'
or @dbname = 'model'
or @dbname = 'tempdb'
or @dbname = 'distribution'
or @dbname = 'msdb'
or isnull(@dbname, '') = ''
return -1 -- error
-- Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #fraglist (
ObjectName VARCHAR (255),
ObjectId INT,
IndexName VARCHAR (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)
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Update statistics first
set @cmd = 'use ' + @dbname + '; exec sp_updatestats'
exec(@cmd)
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Insert DBCC SHOWCONTIG output for all tables in database into #fraglist
set @cmd = 'use ' + @dbname + '; exec sp_MSForEachTable @command1 = ''insert into #fraglist exec(''''dbcc showcontig([?]) with tableresults, all_indexes'''')'''
exec (@cmd)
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Insert individually specified scandensity fragmented indexes
insert into msdb..Fragmented_Indexes
(DBName, ObjectName,ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents,
ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag, ExtentFrag)
select @dbname, rtrim(t.ObjectName), t.ObjectId, rtrim(t.IndexName), t.IndexId, t.Lvl, t.CountPages, t.CountRows, t.MinRecSize, t.MaxRecSize,
t.AvgRecSize, t.ForRecCount, t.Extents, t.ExtentSwitches, t.AvgFreeBytes, t.AvgPageDensity, t.ScanDensity, t.BestCount, t.ActualCount,
t.LogicalFrag, t.ExtentFrag
from #fraglist t inner join msdb..Fragmented_Indexes_XConfig x
on (t.ObjectName = x.ObjectName and t.IndexName = x.IndexName)
where x.DBName = @dbname
and t.extentswitches > 0
and t.indexid not in (0, 255)
and t.scandensity < x.ScanDensity
-- Remove specified scandensity fragmented indexes from general SHOWCONTIG list
delete from #fraglist
where exists (select x.ObjectName, x.IndexName
from Fragmented_Indexes_XConfig x
where x.DBName = @dbname
and x.ObjectName = #fraglist.ObjectName
and x.IndexName = #fraglist.IndexName)
-- TODO - test above
-- Add all indexes falling below @target to Fragmented_Indexes
insert into msdb..Fragmented_Indexes
(DBName, ObjectName,ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag, ExtentFrag)
select @dbname as DBName, rtrim(ObjectName), ObjectId, rtrim(IndexName), IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize,
AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag,
ExtentFrag
from #fraglist
where extentswitches > 0
and indexid not in (0, 255)
and scandensity < @target
and CountPages >= @minpages
order by objectid, indexid, lvl
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Clean up temporary SHOWCONTIG table
drop table #fraglist
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Query appropriate sysindexes table to get OrigFill settings for indexes
set @cmd = 'update msdb..Fragmented_Indexes set origfill = i.OrigFillFactor from msdb..Fragmented_Indexes f inner join ' + @dbname
+ '..sysindexes i on (f.ObjectId = i.id and f.indexid = i.indid) where f.origfill is null and f.DBName = ''' + @dbname + ''''
exec (@cmd)
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
return 0The last proc is then used to query the fragmented_indexes table and then perform any defragging necessary
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_defrag]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_defrag]
GO
/*=========================================================================================
Description:
Defragment indexes based on information stored in msdb.dbo.Fragmented_Indexes
by running usp_frag_report
Depends On:
usp_get_dnsty(e.g. prior script installations, exes, registry settings, etc.
Notes:
Assumes dbo owner of all objects
Installation:
msdb
Processing Steps:
*
Tables Used:
msdb..Fragmented_Indexes
msdb..Fragmented_Indexes_History
msdb..Fragmented_Indexes_XConfig
Parameters:
@dbname (sysname def: NULL) MyDbName: Database to defragment; NULL: All writeable user databases
@eachindex (bit def: 0) 1: defrag each index individually; 0: Just do clustered
@defaultfill (tinyint def: 85) 0-100 Fill factor % to use when rebuilding
@useorigfill (bit def: 0) 1: use original fill factor (if > 0); 0: use default regardless
@rebuild (bit def: 1) 1: use DBCC DBREINDEX; 0: use DBCC INDEXDEFRAG
Return Value:
0
Called By:
Defragmentation job
Calls:
usp_get_dnsty
CHANGE NOTES
Add fragmented_indexes_history table, and
fragmented_indexes_fillfactor table to override applying
default fillfactor. This code has been added in the
"Each index individually" section only. Also, after each index
has been rebuilt, the row for that index is deleted from the
fragmented_indexes table.
Commented out table defns (now in separate files) and changed
Fragmented_Indexes_Fillfactor to Fragmented_Indexes_XConfig
Refactored to remove split paths based on eachindex. Now only
one main loop/path. However, now means that if eachindex = 0,
only fragmentation in the clustered index will be processed
(i.e. fragmentation in nonclustered indexes on a table for which
the clustered index is within tolerance will not trigger a defrag)
=========================================================================================*/
create proc usp_defrag (
@dbname sysname = NULL,
@eachindex bit = 0, -- 0 = no 1 = yes
@defaultfill tinyint = 80,
@useorigfill bit = 0, -- 0 = no, use default; 1 = yes (except when origfill = 0 then use @defaultfill)
@rebuild bit = 1 -- 0 = DBCC INDEXDEFRAG; 1 = DBCC DBREINDEX
)
as
declare @cmd varchar(500),
@targdb sysname,
@tabname sysname,
@origfill tinyint,
@newfill tinyint,
@indexname sysname,
@err int,
@count int,
@start_time datetime,
@end_time datetime
-- Troubleshout erroneous defaultfill params
if @defaultfill < 0 or @defaultfill > 100
set @defaultfill = 80
set @cmd = ''
-- Define TARGETS cursor for indexes to defrag
if @dbname is null
-- All databases in instance
if @eachindex = 0
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from msdb..Fragmented_Indexes
where indexid = 1 -- Clustered Indexes only
else
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from msdb..Fragmented_Indexes
else
-- Specified database only
if @eachindex = 0
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from msdb..Fragmented_Indexes
where dbname = @dbname
and indexid = 1 -- Clustered Indexes only
else
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from msdb..Fragmented_Indexes
where dbname = @dbname
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Open TARGETS Cursor
open TARGETS
fetch next from TARGETS into @dbname, @tabname, @indexname, @origfill
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- Loop for each index to defrag
while @@fetch_status = 0
begin
if @rebuild = 0
begin
-- just do an INDEXDEFRAG
set @cmd = 'dbcc indexdefrag (''' + rtrim(@dbname) + ''', ''' + rtrim(@tabname) + '''' + ', '+ '''' + rtrim(@indexname) + '''' + ')'
end
else
begin
-- Do DBREINDEX
-- sort out what fill factor to rebuild the index to
select @newfill = newfill
from msdb..Fragmented_Indexes_XConfig
where dbname = rtrim(@dbname)
and objectname = rtrim(@tabname)
and indexname = rtrim(@indexname)
-- if a row is returned for this index from Fragmented_Indexes_XConfig table
-- then use that FILLFACTOR, otherwise use the CASE statement below to
-- decide which FILLFACTOR to use
if @newfill is null
select @newfill = case when @useorigfill * @origfill > 0 then @origfill else @defaultfill end
print 'Index: ' + @indexname +' newfill ' + convert(varchar(5), isnull(@newfill, 'NULL'))
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
-- now rebuild this specific index to chosen fill factor
set @cmd = 'dbcc dbreindex (''' + rtrim(@dbname) + '..' + rtrim(@tabname) + ''', ''' + rtrim(@indexname) + ''' , '
+ convert(varchar(3), @newfill) + ')'
end
set @start_time = getdate()
exec (@cmd)
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
set @end_time = getdate()
-- Move processed indexes record to History table and remove from Fragmented_Indexes
begin tran
insert into msdb..Fragmented_Indexes_History
(DBName, ObjectName,ObjectId, IndexName,
IndexId, Lvl, CountPages, CountRows,
MinRecSize, MaxRecSize, AvgRecSize,
ForRecCount, Extents, ExtentSwitches,
AvgFreeBytes, AvgPageDensity, ScanDensity,
BestCount, ActualCount, LogicalFrag, ExtentFrag, origfill,
start_time, end_time, newfill)
select DBName, ObjectName,ObjectId, IndexName,
IndexId, Lvl, CountPages, CountRows,
MinRecSize, MaxRecSize, AvgRecSize,
ForRecCount, Extents, ExtentSwitches,
AvgFreeBytes, AvgPageDensity, ScanDensity,
BestCount, ActualCount, LogicalFrag, ExtentFrag, origfill,
@start_time, @end_time, @newfill
from msdb.dbo.fragmented_indexes
where dbname = rtrim(@dbname)
and objectname = rtrim(@tabname)
and indexname = rtrim(@indexname)
delete from msdb.dbo.Fragmented_Indexes
where dbname = rtrim(@dbname)
and objectname = rtrim(@tabname)
and indexname = rtrim(@indexname)
set @count = @@rowcount
if @count = 1
begin
print 'Row deleted from msdb..fragmented_indexes for index ' + rtrim(@indexname)
+ ' on table ' + rtrim(@tabname) + ' on database ' + rtrim(@dbname)
commit tran
end
else
rollback tran
fetch next from TARGETS into @dbname, @tabname, @indexname, @origfill
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
end
-- Clean up TARGETS Cursor
close TARGETS
deallocate TARGETS
GOThis series of procs/jobs has evolved over time - things like configuration at an individual index level using the XConfig table was added
to give the flexibility to the DBA or programmers to maintain indices individually.
I hope this helps and you found it worthwhile.
Please Note: 1 is Bad, 10 is Good :-)
Part and Inventory Search
-
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.