November 26, 2008 at 2:45 pm
I have just taken over a DBA role, I'm from a development background, and one of the scheduled jobs was for the re-indexing of various indexes using DBCC Reindex.
When the job ran I suspected that some of the re-indexing was clashing with a batch job that needed access to certain tables, so to find out the individual times of each re-index I placed the indexing of each table on a job step.
This allowed me to capture results over a period of time and find out times for the re indexing of certain tables but what I have found is really curious is that a re-index on a certain table would consistently take on average a few min's then for some reason take an hour plus on a single occurrence then return to its normal average of a few min's.
Anybody have any idea of why this can occur and its not just the same table it happened to different tables on different dates?
Thanks in advance,
baby DBA Baz
November 27, 2008 at 10:19 am
Tough to guess without knowing too much
But it could depend on the workload at the job execution time, the fragmentation of the indexes, blocking processes...
Are these OFFLINE re-indexes? are they only executed with over say 30% fragmentation?
November 28, 2008 at 9:15 am
Hi Jerry,
All the re-indexing is offline as its uses the DBCC DBReindex and the fragmentation figure (taken from data diagnostic tool idrea ) is usuallu below 10%.
I have read that the target figure before you re-index or defragment should be 30%, is that the genral rule of thumb?
December 1, 2008 at 10:56 am
30% is usually the rule of thumb (below 30%, you REORGANIZE or do nothing, above it you REINDEX)
Take a look at Books Online from Microsoft (sample script uses 30%)
DBCC INDEXDEFRAG
Defragments indexes of the specified table or view.
http://msdn.microsoft.com/en-us/library/ms177571.aspx
As for slowness, I can't offer much without knowing much
Maybe manually run the REINDEX to see how long it takes vs. schedule job duration to have a baseline
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply