June 18, 2012 at 1:56 pm
New job. So I inherited the old DBA's DBs.
After finding fragmentation on the drive (sys admins fixed)...I ran showcontig.
The results for most of the tables on most of the instances look about like this:
TABLE level scan performed.
- Pages Scanned................................: 13
- Extents Scanned..............................: 8
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 1.6
- Scan Density [Best Count:Actual Count].......: 25.00% [2:8]
- Extent Scan Fragmentation ...................: 87.50%
- Avg. Bytes Free per Page.....................: 4700.8
- Avg. Page Density (full).....................: 41.92%
How concerned should I be over these results? I'm really worried about extent scan fragmentation.
June 18, 2012 at 3:11 pm
presuming this is in fact sql 2000 database?
If all the tables have only about 13 pages not too concerned as the tables are too small to benefit from defragmentation, and reindexing might not in fact do anything to them. If other table are larger (above say 500 - 1000 pages) then you have heavy fragmentation and you should run dbreindex against them.
run a reindex script that filters on larger tables and an extent fragmentation of greater than 30%.
the pages are also rather empty, reindex should also fix that unless fillfactor is set to low or you have rows averaging just over 4000 bytes.
---------------------------------------------------------------------
June 18, 2012 at 3:26 pm
thanks George
June 18, 2012 at 4:05 pm
I doubt you're seeing major performance hits from the fragmentation, but I'd go ahead and defragment anyway. You're only storing, on average, 1.6 pages per extent, so you'll get some benefits from defragging them.
"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
June 19, 2012 at 2:02 am
Grant Fritchey (6/18/2012)
I doubt you're seeing major performance hits from the fragmentation, .
why do you say that grant?
---------------------------------------------------------------------
June 19, 2012 at 5:03 am
george sibbald (6/19/2012)
Grant Fritchey (6/18/2012)
I doubt you're seeing major performance hits from the fragmentation, .why do you say that grant?
Just because, at least from what you've posted, the tables are not very big. If you have decent disks, you're just not seeing it. However, performance will improve some when you defrag them, just not much.
"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
June 19, 2012 at 5:15 am
based on all the tables being about that size, I agree with you, you'll be glad to hear.
not my post btw 🙂
---------------------------------------------------------------------
June 19, 2012 at 5:28 am
george sibbald (6/19/2012)
based on all the tables being about that size, I agree with you, you'll be glad to hear.not my post btw 🙂
Ooops. Just saw the question. Didn't look at who posted it. Sorry.
"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
June 19, 2012 at 12:10 pm
thanks guys
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply