dinsdag 16 december 2008

The Best Solution for Defragmentaion of Indexes --SQL server 2000 --Fantastic

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.

sp_DefragIndexes --For SQL server 2005--The Best

USE [master]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- This stored procedure checks all indexes in the current
-- database and performs either offline or online defragmentation
-- according to the specified thresholds.
-- The stored procedure also updates statistics for indexes in which the last update
-- time is older than the specified threshold.
-- Parameters:
-- @onlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform online defragmentation (default 10%).
-- @offlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform offline defragmentation (default 30%).
-- @updateStatsThreshold specifies the number of days since the last statistics update
-- which should trigger updating statistics (default 7 days).
-- =============================================
CREATE PROCEDURE [dbo].[sp_DefragIndexes]
(
@databaseName sysname = null,
@onlineDefragThreshold float = 10.0,
@offlineDefragThreshold float = 30.0,
@updateStatsThreshold int = 7
)

AS
BEGIN

IF @databasename is null
BEGIN
RETURN;
END

DECLARE @SQL nvarchar(4000)
SET @SQL = 'USE '+ @databasename +'

set nocount on
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag float
DECLARE @command varchar(8000)
DECLARE @schemaname sysname
DECLARE @objectname sysname
DECLARE @indexname sysname

declare @AllIndexes table (objectid int, indexid int, fragmentation float)

declare @currentDdbId int
select @currentDdbId = DB_ID()

insert into @AllIndexes
SELECT
object_id, index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, ''LIMITED'')
WHERE index_id > 0

DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes

OPEN indexesToDefrag

-- Loop through the partitions.
FETCH NEXT
FROM indexesToDefrag
INTO @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid

SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid

IF @frag > @onlineDefragThreshold
BEGIN
IF @frag < @offlineDefragThreshold
BEGIN
SELECT @command = ''ALTER INDEX '' + @indexname + '' ON '' +
@schemaname + ''.'' + object_name(@objectid) +
'' REORGANIZE''
EXEC (@command)
END

IF @frag >= @offlineDefragThreshold
BEGIN
SELECT @command = ''ALTER INDEX '' +
@indexname +'' ON '' + @schemaname + ''.'' +
object_name(@objectid) + '' REBUILD''
EXEC (@command)
END;
PRINT ''Executed '' + @command
END

IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
BEGIN
SELECT @command = ''UPDATE STATISTICS '' + @schemaname + ''.'' + object_name(@objectid) +
'' '' + @indexname +'' WITH RESAMPLE''
EXEC (@command)

PRINT ''Executed '' + @command
END

FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag

END

CLOSE indexesToDefrag;
DEALLOCATE indexesToDefrag'

DECLARE @Params nvarchar(4000)
SET @Params = N'
@onlineDefragThreshold float,
@offlineDefragThreshold float,
@updateStatsThreshold int'

EXECUTE sp_executesql @SQL,
@Params,
@onlineDefragThreshold=@onlineDefragThreshold,
@offlineDefragThreshold=@offlineDefragThreshold,
@updateStatsThreshold=@updateStatsThreshold;
END
-------------------------------------------
exec sp_DefragIndexes 'AdventureWorks'

When you need to Run Stored Procedure in a JOB

if object_id(N'usp_DefragIndexes') <>0
drop proc usp_DefragIndexes
go

CREATE PROC usp_DefragIndexes @databasename SYSNAME = null,
@maxfrag DECIMAL

as

-- @maxfrag = 10.0
BEGIN

IF @databasename is null
BEGIN
RETURN;
END

DECLARE @SQL nvarchar(4000)
SET @SQL = 'USE '+ @databasename +'
SET NOCOUNT ON
.......;'

DECLARE @Params nvarchar(4000)
SET @Params = N'
@databasename SYSNAME ,
@maxfrag DECIMAL'

EXECUTE sp_executesql @SQL,
@Params,
@databasename =@databasename ,
@maxfrag = @maxfrag
END

SQL Server 2000 Defragmentation Indexes Script --The Best

/*
** Author: Rodrigo Acosta
** Email: acosta_rodrigo@hotmail.com
**
** Script to be included in a Job that manages fragmentation
** in all indexes in the executing database.
*/


Set Nocount On

/*
** *************Error Message Definition********************
*/

/* Declare Variables that wil be used for the error message */
Declare @Error_no Int
Declare @AddMessage Varchar(1500)

/* Calculates the last error number of the message */
Set @Error_no = (Select Top 1 error From master.dbo.sysmessages Order by error Desc)

/*
First checks if the message that will be added exists.
If it doesn't, calculates the available error number
and creates the message.
*/
If Not Exists
(Select description from master.dbo.sysmessages
Where description Like ('%View C:\Frag_Info.log for more info.'))
Begin
/*
If no user define error messages were added (Error < 50001).
Creates the message with the number 50001.
*/
If @Error_no < 50001
Begin
Set @Error_no = 50001
Set @AddMessage = 'EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ',
@severity=19,
@msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
@with_log=True'
EXEC (@AddMessage)
End
Else
/*
If there is an used define error (Error > 50001). Calculates the
first available error number and creates the error message.
*/
Begin
Set @Error_no = @Error_no + 1
Set @AddMessage='EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ',
@severity=19,
@msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
@with_log=True'
EXEC (@AddMessage)
End
End

/*
** *************Alert Definition********************
*/

/*
** If the Alerts Does not exists, it is created to response to the Error message
** created before.
*/
If Not Exists
(Select Name From msdb.dbo.sysalerts Where name = 'Fragmentation above 50% in user index.')
Begin
EXECUTE msdb.dbo.sp_add_alert @name = 'Fragmentation above 50% in user index.',
@message_id = @Error_no,
@Severity = 0,
@Enabled = 1,
@delay_between_responses = 0,
@category_name = '[Uncategorized]'
End


/*
** *************Selecting Sysindexes columns********************
*/


/* Creates a Temp Table to hold the results from DBCC ShowContig */

/* Creates a Temp Table to show the results from DBCC ShowContig */
Create Table #SaveResults
(ObjectName Sysname,
ObjectId int,
IndexName Sysname,
Indexid int,
[Level] int,
Pages Int,
[Rows] Int,
MinimumRecordSize Int,
MaximumRecordSize Int,
AverageRecordSize Int,
ForwardedRecords Int,
Extents Int,
ExtentSwitches Int,
AverageFreeBytes Int,
AveragePageDensity Int,
ScanDensity Int,
BestCount int,
ActualCount Int,
LogFragmentation Int,
ExtentFragmentation Int)

/* Creates a Temp Table to show the modified results from DBCC ShowContig */
Create Table ##ShowResults
(TableName sysname,
IndexName sysname,
IndexType Varchar (12),
[%Frag] Int,
Defrag VarChar (3),
Pages Int,
AvgFreeBytes Int)

/*
Declares a cursor and variables that holds the table and index Type (Clustered or Nonclustered)
and the FillFactor (original) from user tables with indexes.
*/
Declare @TableName sysname
Declare @IndexName sysname
Declare vCursor Cursor For
Select Object_name (i.id) As 'TableName',
i.name As 'IndexName'
From Sysindexes i Inner Join sysobjects o
On i.id=o.id
Where (Indid = 1 Or Indid Between 2 And 250) And xtype = 'U'

Declare @cmdDBCC Varchar (200)


Open vCursor
Fetch Next From vCursor Into @TableName, @IndexName
While @@Fetch_Status = 0
Begin
Set @cmdDBCC = 'DBCC ShowContig (' + @TableName + ', ' + @IndexName +
') With TableResults'
Insert #SaveResults
Exec (@cmdDBCC)
/* Fills the #SaveResults Table with all the results of the execution*/
Fetch Next From vCursor Into @TableName, @IndexName
End

/* Close the cursor because it don't use it anymore */
Close vCursor
Deallocate Vcursor

/* Declare variables to save info from the #SaveResults */
Declare @ObjectName Sysname
Declare @IndexName2 Sysname
Declare @ScanDensity Int
Declare @IndexType Varchar(13)
Declare @Pages Int
Declare @AverageFreeBytes Int
Declare @Rows Int
Declare @Defrag Char(3)
Declare vCursor2 Cursor For
Select ObjectName, IndexName, ScanDensity,
[IndexType]=
Case
When IndexId = 1 Then 'Clustered'
Else 'NonClustered'
End
,
Pages, [Rows], AverageFreeBytes
From #SaveResults
Open vCursor2
Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType,
@Pages, @Rows, @AverageFreeBytes

While @@Fetch_Status = 0
Begin
/* If fragmentation is above 50 % Then fire the error */
If @ScanDensity < 50
Begin
/* Builts the raise error sentence */
Declare @Raise Varchar(150)
Set @Defrag = 'Yes'
Set @Raise = 'Raiserror (' + Convert(Varchar(10),@Error_no) +
', 18, 1,' + '''' + @IndexType + '''' + ',' + '''' + @IndexName2+ '''' + ',' + '''' +
@ObjectName + '''' + ',' + '''' + Convert(Varchar(20),@ScanDensity) + '''' + ')'
EXEC (@Raise)
End
/* If not just alter the variable to indicate no defragmentation is needed*/
Else
Begin
Set @Defrag = 'No'
End

/* Insert the results into the temp table created to show the results */
Insert ##ShowResults
Values
(@ObjectName, @IndexName2, @IndexType,
@ScanDensity, @Defrag, @Pages,
@AverageFreeBytes)

Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType,
@Pages, @Rows, @AverageFreeBytes
End

Close vCursor2
Deallocate vCursor2

Drop Table #SaveResults

/*
** *************Log File generation********************
*/
Declare @cmd Varchar(1000)
Set @cmd=' EXEC master.dbo.xp_cmdShell ' + '''' +
'OSQL -E -q"Set Nocount On Select Substring(@@ServerName,1,20) as [Executed On Server:] Select Substring(db_name(),1,20) ' +
'AS [Executed On Database:] Select Getdate() as [LogFile generated with the ' +
'results of fragmentation in all user Indexes. Date:] Select ' +
'SubString(TableName,1,20) as TableName,IndexType, Substring(IndexName,1,20) ' +
'as IndexName, [%Frag] as [%Defrag] , Defrag As [Need Defrag.?] from ##ShowResults" -oC:\Frag_Info.log' + ''''
EXEC (@cmd)
Drop Table ##ShowResults
Drop Table #SaveResults

sp_BlockedProcesses

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BlockedProcesses] Script Date: 12/16/2008 11:37:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[sp_BlockedProcesses]
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0

CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses

sp_ActiveProcesses

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ActiveProcesses] Script Date: 12/16/2008 11:35:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[sp_ActiveProcesses]
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0

UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver

sp_ListConnectionsOK

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ListConnectionsOK] Script Date: 12/16/2008 11:31:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ListConnectionsOK]
@DBUltra bit = 0,
@PCUltra bit = 0,
@DBIntra varchar(8000) = NULL,
@DBExtra varchar(8000) = NULL,
@PCIntra varchar(100) = NULL,
@PCExtra varchar(100) = NULL,
@DBTrain char(1) = NULL,
@PCTrain varchar(2000) = NULL
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(4000)

DECLARE @Work varchar(2000)

DECLARE @Host varchar(200)

DECLARE @SPID smallint

DECLARE @SPOT smallint

SET @SPOT = CASE WHEN CHARINDEX('2000 - 8',@@VERSION,1) > 0 THEN 50 ELSE 12 END

SET @Work = REPLACE(@PCTrain,CHAR(94),CHAR(39))

IF @PCTrain IS NULL

BEGIN

SELECT P.spid AS [ID]
, RTRIM(P.hostname) AS [Client]
, RTRIM(P.loginame) AS [Login]
, RTRIM(P.program_name) AS [Application]
, RTRIM(O.name) AS [Database]
, CONVERT(varchar(20),P.login_time,20) AS [Connection]
FROM master.dbo.sysprocesses AS P
JOIN master.dbo.sysdatabases AS O
ON P.dbid = O.dbid
LEFT JOIN master.dbo.sysprocesses AS Z
ON P.spid = Z.blocked
WHERE P.spid > @SPOT
ORDER BY [Client],[Login],[Application],[Database],[Connection]

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

END




IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR ('Windows error [%d]',16,1,@Status)

SET NOCOUNT OFF

RETURN (@Status)

exec sp_ListConnectionsOK