Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Logical Scan fragmentation increasing Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 3:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 29, 2009 7:30 AM
Points: 2, Visits: 3
My 4 main tables had the following fragmentation:

Date 25-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:


Date 7-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:


Date 29-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









Post #706595
Posted Wednesday, April 29, 2009 5:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #706646
Posted Wednesday, April 29, 2009 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 29, 2009 7:30 AM
Points: 2, Visits: 3
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.
Post #706671
Posted Wednesday, April 29, 2009 5:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #706682
Posted Wednesday, April 29, 2009 7:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #706792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse