Reindex Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Reindex Problem

Author

Topic

fkeuris
Starting Member

5 Posts

Posted - 2009-10-14 : 09:25:57

Good Day

I have a question to ask about reindexing on a production system.
We start our reindexing at 00:00 but if stops running at 02:00. This is not due to it completing, I do not know why it stops. the reindexing is run through a SP and not using dbcc's. Can this be happening due to a log backup, a full backup or any other sort of this that happens in the DB?
our DB is very big, and the reindexing should be running for about 5 to 6 hours, so i do not understand why it stops. it says that the job is complete, but i have a table that keeps track of what was done, and there are still indexes that are fragmented.

Any help in this regard?

Thank you

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-14 : 09:44:00

Does the errorlog tell you anything?

Is there a step in your code that ensures it only runs for 2 hours? This may possibly be a fail-safe someone has written to ensure other jobs aren't delayed.

Do you only update a certain percentage of the indexes, say the worst 10% or so?

Without more information, it is difficult to say exactly what is happening.

fkeuris
Starting Member

5 Posts

Posted - 2009-10-14 : 09:59:13

Hi,

Sorry about that.
I have check there are no time limit and it does any indexes that has more that 20% frag. i have a table that it reads that fragmented indexes out of, and there are about 300 per day. it will only reindex the 1rst few, that it not a fixed number that it does. There are some days 10 other days 2. i have checked the error log and there is no messages in there that says why it has stopped

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-10-14 : 15:39:14

Show us the script used for reindexing ,then only we can figure out.

fkeuris
Starting Member

5 Posts

Posted - 2009-10-16 : 03:35:15

This is the sp that does the reindexing:

---- =============================================
---- Author: <Francois Keuris>
---- Create date: <03/03/2009>
---- Description: <Rebuilding the indexes that is fragmented>
---- =============================================
--
--
--If Object_id ('AVE_DBMaintinance_Reindexing','p') is not null
-- Drop Proc AVE_DBMaintinance_Reindexing
--Go
--
--Create Proc AVE_DBMaintinance_Reindexing
Declare
@CursorIndex VarChar(128),
@Execstr Varchar(255),
@StartTime DateTime,
@EndTime DateTime,
@dbname VARCHAR(128),
@tablename VARCHAR (128),
@indexname CHAR(128),
@frag DECIMAL,
@indexDesc VARCHAR(128),
@Date DateTime,
@correctDate Int

Set @date = Getdate()
Set @Correctdate = DatePart(day,DATEADD(dd, -1, @date))

Declare Cursorindex Cursor Forward_only For
Select DatabaseName,
TableName,
TableIndexName,
Avg_Fragmentation,
DatePart(day,Date) as date
from msDBMaintinanceReindex
Where DatePart(day,Date) = @correctDate

Open CursorIndex
Fetch Next
From CursorIndex
Into @dbname, @tablename, @indexname, @frag, @date

While @@Fetch_Status = 0
Begin
if @frag Between 20 and 40
Begin
Set Quoted_Identifier ON
Select @execstr = 'Alter Index '+ Rtrim(@indexname)+' On '+ Rtrim(@tablename)+' Reorganize'
Print 'Now executing: '
Print (@execstr)
Set @StartTime = GetDate()
Execute (@execstr)
Print 'Done'
Set @EndTime = GetDate()

Update msDBMaintinanceReindex
Set StartTime = @StartTime,
EndTime = @EndTime,
Action = 'Reorganize'
where TableName = @tablename And
TableIndexName = @indexname
End
Else
Begin
Set Quoted_Identifier ON
Select @execstr = 'Alter Index '+ Rtrim(@indexname)+' On '+ Rtrim(@tablename)+' Rebuild WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)'
Print 'Now executing: '
Print (@execstr)
Set @StartTime = GetDate()
Execute (@execstr)
Print 'Done'
Set @EndTime = GetDate()

Update msDBMaintinanceReindex
Set StartTime = @StartTime,
EndTime = @EndTime,
Action = 'Rebuild'
where TableName = @tablename And
TableIndexName = @indexname
End
Fetch Next From Cursorindex Into @dbname, @tablename, @indexname, @frag, @date
End

Close Cursorindex
Deallocate Cursorindex

Set @date = Getdate()
Set @date = Dateadd(Day,-1,@date)

Insert into msDBMaintinanceReindexHistory
Select * from msDBMaintinanceReindex
where convert(Varchar(7),date) = Convert(Varchar(7),@date)
Delete from msDBMaintinanceReindex
where convert(Varchar(7),date) = Convert(Varchar(7),@date)

Go

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-16 : 04:43:11

And the calling job? Can you script that as well?

fkeuris
Starting Member

5 Posts

Posted - 2009-10-16 : 07:11:42

The calling job is not in SQL. it is in ASG-Zena

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-17 : 09:59:24

If i understand you correctly, you believe your process is not completing successfully because some of the indexes that should be rebuilt are still fragmented?

How many pages exist for those indexes? How are the indexes selected to be rebuilt?

Are the rows in the table msDBMaintinanceReindex for those indexes updated showing the procedure rebuild/reorganized them?

Most likely, your selection criteria is selecting all tables without regard to size. Smaller tables will never be completely defragmented. Also, if you do not have enough space available in the data file - SQL Server cannot rebuild the indexes contiguously.

Finally, why are you telling SQL Server not to update statistics on the tables if they are rebuilt? Setting STATISTICS_NORECOMPUTE = ON is telling SQL Server to never update statistics on this index. That is not usually a good idea.

fkeuris
Starting Member

5 Posts

Posted - 2009-10-19 : 04:38:03

Hi

i am checking the table size, in my selection criteria i check for indexes with a page_count > 1280. I also rebuild with STATISTICS_NORECOMPUTE = ON, due to we running a seperate job to update that stats on the indexes,

The indexes are selected with the following:

Select
Cast(db_Name(f.Database_ID) As Varchar(50)) As 'DatabaseName',
Schema_Name(o.Schema_id)+'.'+Cast(Object_Name(f.[Object_ID]) As Varchar(128)) As 'TableName',
i.name As TableIndexName,
f.Avg_Fragmentation_In_Percent,
Null as StartTime,
Null as EndTime
From Sys.dm_db_Index_Physical_Stats (DB_ID(),Null,Null,Null, Null) f
Inner Join sys.indexes i On i.object_id = f.object_id And i.index_id = f.index_id
Inner Join sys.Objects o On o.object_id = f.object_id
Where (Schema_Name(o.Schema_id)+'.'+Cast(Object_Name(f.[Object_ID]) As Varchar(128)) like 'td%' Or
Schema_Name(o.Schema_id)+'.'+Cast(Object_Name(f.[Object_ID]) As Varchar(128)) like '%Merge%') And
avg_fragmentation_in_percent > 20 And
page_count > 1280 And
i.name Is Not Null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources