<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server Newbies
»
Logical Scan fragmentation increasing
Logical Scan fragmentation increasing
Rate Topic
Display Mode
Topic Options
Author
Message
cooke.duncan-1053439
cooke.duncan-1053439
Posted Wednesday, April 29, 2009 3:58 AM
Forum 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
Grant Fritchey
Grant Fritchey
Posted Wednesday, April 29, 2009 5:02 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 13,371,
Visits: 25,147
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
cooke.duncan-1053439
cooke.duncan-1053439
Posted Wednesday, April 29, 2009 5:28 AM
Forum 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
Grant Fritchey
Grant Fritchey
Posted Wednesday, April 29, 2009 5:40 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 13,371,
Visits: 25,147
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
GilaMonster
GilaMonster
Posted Wednesday, April 29, 2009 7:25 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 37,663,
Visits: 29,915
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
