April 29, 2009 at 3:58 am
My 4 main tables had the following fragmentation:
Date25-3-2009
Company 62,53%
*_RM_Assessment 25,52%
*_Assessment_Team_Memb 10,37%
*_RM_Acceptance 89,29%
My dba ran an Rebuild index process and statistics and we then had the following:
Date7-4-2009
Company 16.91%
*_RM_Assessment 0.10%
*_Assessment_Team_Memb 0.11%
*_RM_Acceptance 0.27%
The fragmentation increases everyday. The dba has scheduled and reindex process and statistics to run every sunday but every monday, the fragmentation % has not decreased and I am not sure why. presently, the values are as follows:
Date29-4-2009
Company 23,48%
*_RM_Assessment 0.51%
*_Assessment_Team_Memb 0.75%
*_RM_Acceptance 3.04
The only table that concerns me is the copany table but the overall picture is that the fragmentation goes up everyday and the reindex process does not bring it down again. Any advise would be appreciated
April 29, 2009 at 5:02 am
How much data is in the table, specifically how many pages?
For very small tables, less than 10 pages, you might not see any change in fragmentation, even after a rebuild of the index and certainly not after a defrag. For tables less than 1000 pages, again, a defrag might not affect it and, according to Microsoft, you're unlikely to run into issues.
"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
April 29, 2009 at 5:28 am
Hi grant
thanks for the reply. the table I am concerned about is the company table and it has 16965 tables.
the full scan info :
- Pages Scanned................................: 16965
- Extents Scanned..............................: 2133
- Extent Switches..............................: 5584
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 37.98% [2121:5585]
- Logical Scan Fragmentation ..................: 23.48%
- Extent Scan Fragmentation ...................: 9.75%
- Avg. Bytes Free per Page.....................: 1383.6
- Avg. Page Density (full).....................: 82.91%
At the moment, the performance of scripts querying this table is still good but I am worried that we will see a degredation in speed as the fragmentation increases.
April 29, 2009 at 5:40 am
It could be something you have to monitor. Do you have an idea as to why you're getting so many page splits? Does the table have a clustered index that's less than optimal?
You might just have a table that's a hot spot. Make sure that you keep statistics up to date on the table. Use a full scan if you have to. You can run the defrag more frequently, even once a day during slow periods, but I'd make sure that it checks the fragmentation first.
Also, use sys.dm_db_index_physical_stats to get information on the fragmentation. It's easier to read and easier to work with than the old DBCC stuff.
"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
April 29, 2009 at 7:25 am
Are you shrinking the database? Manual or autoshrink?
I usually only worry about fragmentation if it's above about 25-30%.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply