September 5, 2011 at 6:01 pm
We have 2 tables - Job and Jobroute
when i use
DBCC DBREINDEX('jobroute',' ', 80)
It takes about 10 seconds to reindex
when i use
DBCC DBREINDEX('job',' ', 80)
it takes forever to reindex.
The jobroute tables has 5 times records than job table so i thought i will take less time.
Any ideas why its taking long time ?
September 6, 2011 at 3:58 am
Can you provide more detail about the size and complexity of the indexes on the specified tables?
September 6, 2011 at 4:19 am
Here is the information from using the DBCC SHOWCONTIG command
DBCC SHOWCONTIG scanning 'job' table...
Table: 'job' (2142630676); index ID: 1, database ID: 64
TABLE level scan performed.
- Pages Scanned................................: 7391
- Extents Scanned..............................: 933
- Extent Switches..............................: 1132
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 81.55% [924:1133]
- Logical Scan Fragmentation ..................: 4.02%
- Extent Scan Fragmentation ...................: 59.70%
- Avg. Bytes Free per Page.....................: 1422.6
- Avg. Page Density (full).....................: 82.42%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
September 6, 2011 at 4:51 am
It's not a very big table. Is there lots of contention? Other processes accessing it while you're attempting to defrag? That would explain the slow down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2011 at 4:53 am
Yes it there are applications running which access these tables.
September 6, 2011 at 5:08 am
Then you shouldn't be surprised if it slows things down. Make sure you're running these processes at the slowest part of the day because they will affect each other. If you're running defrags at the height of the business day, the business users are going to notice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2011 at 5:24 am
As Grant suggested always do the maintenance activities like stats update, defrg, rebuild at the slowest time of your day, even taking the system down for some time for the activity. Doing at its peak time, you are giving enough room to users of the application to notice a difference in your application performance.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply