http://www.tek-tips.com/faqs.cfm?fid=5367
=============================================
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
1:
=====
USE [DBeheer]
GO
/****** Object: Table [dbo].[Fragmented_Indexes_XConfig] Script Date: 12/18/2008 14:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Fragmented_Indexes_XConfig](
[DBName] [sysname] NOT NULL,
[ObjectName] [varchar](255) NOT NULL,
[IndexName] [varchar](255) NOT NULL,
[NewFill] [tinyint] NULL,
[ScanDensity] [tinyint] NULL,
CONSTRAINT [PK_XConfig] PRIMARY KEY CLUSTERED
(
[DBName] ASC,
[ObjectName] ASC,
[IndexName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
----------------------
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.
2:
====
USE [DBeheer]
GO
-- Must get rid of any old v1 & 2 Fragmented_Indexes table
if object_id('DBeheer.dbo.Fragmented_Indexes') is not null
drop table DBeheer.dbo.Fragmented_Indexes
go
-- Table modified v2 & v3
CREATE TABLE [dbo].[Fragmented_Indexes](
[DBName] [sysname] NOT NULL,
[ObjectName] [varchar](255) NULL,
[ObjectId] [int] NULL,
[IndexName] [varchar](255) NULL,
[IndexId] [int] NULL,
[Lvl] [int] NULL,
[CountPages] [int] NULL,
[CountRows] [int] NULL,
[MinRecSize] [int] NULL,
[MaxRecSize] [int] NULL,
[AvgRecSize] [int] NULL,
[ForRecCount] [int] NULL,
[Extents] [int] NULL,
[ExtentSwitches] [int] NULL,
[AvgFreeBytes] [int] NULL,
[AvgPageDensity] [int] NULL,
[ScanDensity] [decimal](18, 0) NULL,
[BestCount] [int] NULL,
[ActualCount] [int] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ExtentFrag] [decimal](18, 0) NULL,
[OrigFill] [tinyint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------
3:
===
USE [DBeheer]
GO
if exists (select * from DBeheer..sysobjects where name = 'Fragmented_Indexes_History' and type = 'U')
drop table Fragmented_Indexes_History
go
CREATE TABLE [dbo].[Fragmented_Indexes_History](
[DBName] [sysname] NOT NULL,
[ObjectName] [char](255) NULL,
[ObjectId] [int] NULL,
[IndexName] [char](255) NULL,
[IndexId] [int] NULL,
[Lvl] [int] NULL,
[CountPages] [int] NULL,
[CountRows] [int] NULL,
[MinRecSize] [int] NULL,
[MaxRecSize] [int] NULL,
[AvgRecSize] [int] NULL,
[ForRecCount] [int] NULL,
[Extents] [int] NULL,
[ExtentSwitches] [int] NULL,
[AvgFreeBytes] [int] NULL,
[AvgPageDensity] [int] NULL,
[ScanDensity] [decimal](18, 0) NULL,
[BestCount] [int] NULL,
[ActualCount] [int] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ExtentFrag] [decimal](18, 0) NULL,
[Origfill] [tinyint] NULL,
[start_time] [datetime] NULL,
[end_time] [datetime] NULL,
[newfill] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
----------------------
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.
4:
==
USE [DBeheer]
GO
if exists (select * from DBeheer.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 DBeheer..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:
DBeheer
Processing Steps:
If DBeheer..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:
DBeheer.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 [dbo].[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 DBeheer..sysobjects where name = 'Fragmented_Indexes' and type = 'U')
truncate table Fragmented_Indexes
else
create table DBeheer..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('DBeheer.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
GO
------------------------
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.
5:
==
USE [DBeheer]
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) DBeheer..Fragmented_Indexes
Depends On:
DBeheer..Fragmented_Indexes
Notes:
uses undocumented procedure sp_MSForEachTable
Installation:
DBeheer
Processing Steps:
create temp table #fraglist
fill with dbcc showcontig for all tables in target DB (with tableresults, all_indexes, all_levels)
insert into DBeheer..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:
DBeheer..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 [dbo].[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 DBeheer..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 DBeheer..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 DBeheer..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 DBeheer..Fragmented_Indexes set origfill = i.OrigFillFactor from DBeheer..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 0
------------------------
The last proc is then used to query the fragmented_indexes table and then perform any defragging necessary
6:
==
USE [DBeheer]
GO
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 DBeheer.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:
DBeheer
Processing Steps:
*
Tables Used:
DBeheer..Fragmented_Indexes
DBeheer..Fragmented_Indexes_History
DBeheer..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 [dbo].[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 DBeheer..Fragmented_Indexes
where indexid = 1 -- Clustered Indexes only
else
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from DBeheer..Fragmented_Indexes
else
-- Specified database only
if @eachindex = 0
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from DBeheer..Fragmented_Indexes
where dbname = @dbname
and indexid = 1 -- Clustered Indexes only
else
declare TARGETS cursor for
select distinct dbname, objectname, indexname, origfill
from DBeheer..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 DBeheer..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 DBeheer..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 DBeheer.dbo.fragmented_indexes
where dbname = rtrim(@dbname)
and objectname = rtrim(@tabname)
and indexname = rtrim(@indexname)
delete from DBeheer.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 DBeheer..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
GO
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.
Abonneren op:
Reacties posten (Atom)
Geen opmerkingen:
Een reactie posten