Technical Article

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
*/

Rate

3.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.8 (5)

You rated this post out of 5. Change rating