November 30, 2006 at 3:05 pm
A Reindex job that runs every week is running too long and in the process the cpu usage goes up to 96%. It happens only when that job is run. The cpu is normal at other times. How do I stop this from happening? Can someone please let me know what's happening? Thank you.
November 30, 2006 at 3:20 pm
What is too long? What index rebuild command are you using, DBCC DBREINDEX? Consider running DBCC SHOWCONTIG directly after your index rebuild job runs, directly before it runs, and sometime in between (middle of week maybe). Then compare the fragmentation levels on your heavy-hitter tables. You may find that your indexes are getting very fragmented in the course of your week and you may benefit from running your index job more often or by running DBCC INDEXDEFRAG periodically between index rebuilds. I would imagine if your index rebuilds are taking longer than you want them too, it's because you need to maintain your indexes more often.
November 30, 2006 at 4:02 pm
If you have a lot of large tables, you might want to consider doing the tables individually, or even doing the reindex by individual index.
-SQLBill
December 1, 2006 at 10:00 am
Depending on your specific requriements for the database you are reindexing, you may want to consider setting the database recovery model to "simple" to avoid logging all the transactions. I've found this helps speed up the dbreindex process. Here is some sample code that does the trick:
-- Change recovery mode to prevent excessive t-log growth--
DECLARE @DatabaseName VARCHAR(255)
SELECT @DatabaseName = DB_NAME()
EXEC (' ALTER DATABASE ' + @DatabaseName + ' SET RECOVERY SIMPLE ')
..... (your SQL code) ......
-- Set recovery model back to full --
EXEC (' ALTER DATABASE ' + @DatabaseName + ' SET RECOVERY FULL')
Cheers - DG
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply