SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logical Scan fragmentation increasing


Logical Scan fragmentation increasing

Author
Message
cooke.duncan-1053439
cooke.duncan-1053439
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40401 Visits: 32665
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
cooke.duncan-1053439
cooke.duncan-1053439
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40401 Visits: 32665
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88144 Visits: 45277
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search