SQL 2005 IndexDefrag solution
I submit this suggested solution for review. I created this because we cannot have downtime on this site(24/7), or scripts that slow the system down that run during peak business hours. Because I cannot do ReIndexing due to uptime, I went for second best namely Index defrag that will give a bit of a performance boost. The 2000 database was still very slow running on SQL Server2005. After this there was a huge improvement. Please take note that the IndexDefag syntax is still 2000. Convert this at your own discretion to 2005 format with alter index syntax. I further suggest that you implement this for all your databases and specify different times to spread the processor load. I trust this might solve someone's problem.
This Stored Procedure can only run at a specific time, between 2 and 4 in the morning. Keep in mind I am in South Africa so the US date format might be deferent. First create the table to contain the log data. Then the SP, but first run a couple of tries and debugging...good luck...
Kobus van der Walt
--create the table containing the log data
USE [AA]
GO
/****** Object: Table [dbo].[ZZDataBaseAdminLog] Script Date: 05/07/2008 08:09:33 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ZZDataBaseAdminLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ID2] [int] NULL,
[TableName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ilog] [int] NULL,
[Slog] [int] NULL,
[Stamp] [datetime] NULL CONSTRAINT [DF_ZZDataBaseAdminLog_Stamp] DEFAULT (getdate()),
CONSTRAINT [PK_ZZDataBaseAdminLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--create the SP for Index defrag
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].aa.[ZZDBAdmin2005]
as
BEGIN
SET NOCOUNT ON
--drop table ##Admin
SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent as FRAG
into ##Admin
FROM sys.dm_db_index_physical_stats(9, NULL, NULL, NULL, 'Limited') indexstats
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id
AND i.index_id = indexstats.index_id
where i.name is not null
order by indexstats.avg_fragmentation_in_percent desc
select top 10 * from ##Admin
alter table ##ADMIN add ID2 int identity
--select * from ##ADMIN where id2=176
DECLARE @strSql varchar(150)
set @strsql=''
declare @i int
set @i=0
declare @strPre varchar(25)
set @strpre='dbcc indexdefrag(aa,'
declare @strMid varchar(2)
set @strMid=','
declare @strEnd varchar(10)
set @strEnd=') '
print @i
while @i <>1000
begin
declare @strTable varchar(100)
declare @strIndex varchar(100)
declare @err varchar(2)
set @err=1
declare @start datetime
set @strTable=(select TableName from ##ADMIN where ID2=@i )
set @strIndex=(select IndexName from ##ADMIN where ID2=@i)
set @strSql=(@strpre+@strTable+@strmid+@strIndex+@strEnd)
--set @strSql=@strSql+ @strIndex+') '
--print(@strIndex) print @i
--print @strSql
if 2<= datepart(hh,convert(datetime,getdate(),121))
and 8>datepart(hh,convert(datetime,getdate(),121))
begin
exec (@strSql);
if @@error<>0
begin
set @err=0
end
print (@i)print (@err)Print(@start)print(@strTable)
insert into aa.dbo.ZZDataBaseAdminLog (ID2,tablename,ilog) values (@i,@strTable+' '+@strIndex, @err)
end
set @i=@i+1
end
end
/*
drop table ##Admin
go
SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,indexstats.*,
indexstats.avg_fragmentation_in_percent as FRAG
into ##Admin
FROM sys.dm_db_index_physical_stats(9, NULL, NULL, NULL, 'Limited') indexstats
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id
AND i.index_id = indexstats.index_id
where i.name is not null
order by indexstats.avg_fragmentation_in_percent desc
select * from ##admin
select top 10 * from sys.indexes
*/
