April 7, 2009 at 3:29 pm
Right now, Rebuilding of indexes are running for 10 hr. I checked the logical reads and it's too high 335840650 and CPU time is 13593171.
How to trouble shoot the issue. ? I know if I kill the process then database will be in rollback state.
what should I do ?
April 7, 2009 at 3:32 pm
Its quite likely that this is just normal behaviour.
How are you rebuilding the indexes (maintenance plan, specific statements, something else)?
How big is the database? (or databases)
What is the specification of the hardware?
.
April 7, 2009 at 4:01 pm
SERVER Configuration : VM, 1 CPU, 1600 GH speed and 4 GB RAM
using following command.
ALTER INDEX ALL ON DBO.TABLENAME
REBUILD WITH (MAXDOP = 8,
FILLFACTOR = 95,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)'
Table size is 140 million rows.
April 7, 2009 at 4:07 pm
OK and table size in MB / GB is?
If it is a big table it takes a while.
Do you have non unique clustered indexes?
This also rebuilds all other indexes.
PRACTICAL ADVICE. If the IO and CPU count on sp_who2 continue to rise this is working. A rollback will take a while. Consider carefully before you do it!
.
April 7, 2009 at 7:49 pm
balbirsinghsodhi (4/7/2009)
SERVER Configuration : VM, 1 CPU, 1600 GH speed and 4 GB RAMusing following command.
ALTER INDEX ALL ON DBO.TABLENAME
REBUILD WITH (MAXDOP = 8,
FILLFACTOR = 95,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)'
Table size is 140 million rows.
Remove the MAXDOP option - it is not needed since you only have a single CPU on this server anyways. I would recommend modifying the VM and adding another CPU - running SQL Server with a single CPU can be problematic. Also note that indexes will only be built using multiple processors when you are running Enterprise Edition.
Validate that you really don't want statistics to be recomputed for this table and all indexes. By setting this to ON - SQL Server will not recompute statistics unless you explicity issue an UPDATE STATISTICS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 8, 2009 at 2:32 pm
I have fixed the issue. Actually, it was coding problem in the stored procedure not the db-reindex issue I know the server is not big enough to hold this huge database but dbreindex was working fine before and suddenly it started taking longer time after I modified the SP.
This stored procedure accept one parameter as a table name and based on that table , it captures all the indexes and then re-index based on fragmentation. Here is the script, if you want you can use it. save lots of time when I use this SP.
Thanks all of you for helping me.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Balbir Singh
-- Create date: May 30, 2008
-- Description:Re-INDEX based on fragmentation,
-- Modified : 03/12/2009 check fragmentation level.
-- EXEC DBO.POST_DBREINDEX 'transac_live'
-- =============================================
create Procedure [dbo].[POST_DBREINDEX]
@TABLENAME SYSNAME
AS
BEGIN TRY
IF NOT EXISTS (SELECT NAME FROM SYS.SYSOBJECTS WHERE NAME = @TABLENAME)
BEGIN
PRINT 'TABLE NOT FOUND'
RETURN
END
DECLARE @SQLREINDEX VARCHAR(2000)
DECLARE @INDEXNAME VARCHAR(255)
DECLARE @AVG_FRAG SMALLINT
DECLARE @PAGE_COUNT INT
DECLARE @OBJECT_ID INT
SET @OBJECT_ID = OBJECT_ID(@TABLENAME)
-- declare cursor
DECLARE FRAG_CURSOR CURSOR FOR
SELECT --OBJECT_NAME(dt.Object_id) TableName,
si.Name IndexName, DT.Avg_Fragmentation_In_percent, dt.Page_Count
FROM
(
SELECT
Object_Id, Index_Id, Partition_Number, Avg_Fragmentation_In_percent,
Avg_Page_Space_Used_In_Percent, Page_Count
FROM
Sys.Dm_Db_Index_Physical_Stats (db_id(), @OBJECT_ID, NULL, NULL, NULL)
WHERE
Index_Id NOT IN (0) AND Index_Level = 0
) AS dt
INNER JOIN Sys.Indexes si ON si.Object_id = dt.Object_id AND si.Index_Id = dt.Index_Id
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO @INDEXNAME, @AVG_FRAG, @PAGE_COUNT
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @INDEXNAME + ' '+CONVERT(VARCHAR(40),@AVG_FRAG)
-- Check fragmentation level
IF @AVG_FRAG > 30 AND @PAGE_COUNT > 1000
BEGIN
PRINT 'REBUILD STARTED'+' '+@INDEXNAME
SET @SQLREINDEX = ''
-- DBCC DBREINDEX (@TABLENAME, @PKNAME, 0)
-- REINDEX PRIMARY AS WELL AS SECOUNDARY INDEXES WITH 95% FILL FACTOR FOR INCREMENTAL LOAD
SET @SQLREINDEX = 'ALTER INDEX ['+@INDEXNAME+'] ON DBO.['+@TABLENAME+']
REBUILD WITH ( MAXDOP = 8,
FILLFACTOR = 95,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON
)'
EXEC(@SQLREINDEX)
END
IF (@AVG_FRAG > 10 AND @AVG_FRAG 1000
BEGIN
PRINT 'REORGANIZE STARTED'+' '+@INDEXNAME
SET @SQLREINDEX = ''
SET @SQLREINDEX = 'ALTER INDEX ['+@INDEXNAME+'] ON DBO.['+@TABLENAME+']
REORGANIZE'
EXEC(@SQLREINDEX)
END
FETCH NEXT FROM FRAG_CURSOR INTO @INDEXNAME, @AVG_FRAG, @PAGE_COUNT
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
SELECT '' AS ErrorMessage
END TRY
BEGIN CATCH
SELECT CONVERT (TEXT, ERROR_MESSAGE()) AS ErrorMessage
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
END CATCH
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply