<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 2008
»
SQL Server 2008 Administration
»
SQL Server index fragmentation is high after...
20 posts, Page 1 of 2
1
2
»»
SQL Server index fragmentation is high after rebuilt
Rate Topic
Display Mode
Topic Options
Author
Message
Luke79
Luke79
Posted Saturday, December 01, 2012 9:24 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:25 AM
Points: 258,
Visits: 351
I have few indexes in my db, which report high fragmentation even after we rebuild indexes. The page count on these indexes is 493934 pages and size in MB is 3854.
Post #1391651
GilaMonster
GilaMonster
Posted Saturday, December 01, 2012 1:45 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 37,648,
Visits: 29,900
Heaps?
Autoshrink or manual shrink?
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 #1391668
Luke79
Luke79
Posted Saturday, December 01, 2012 1:49 PM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:25 AM
Points: 258,
Visits: 351
It's a clustered index. Not a heap table.
Post #1391671
itsmemegamind
itsmemegamind
Posted Monday, December 03, 2012 12:40 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, February 04, 2013 12:43 AM
Points: 10,
Visits: 45
Usually, Fragmentation will be caused by 3 reasons.
1. Because of Page Splits. (If the newly inserted records fall in between existing set of records).
2. because of allocation strategy sql internal follow while allocating pages for table/index. SQL Server allocates pages for a new table or index from mixed extents. When the table or index grows to eight pages, all future allocations use uniform extents. So, if the no of pages crosses 8, then only it can allocate Uniform extent, otherwise it will allocate Mixed extent..
3. Because of assigning Next Page to another leaf level in index. Since Index Non-Leaf Levels also increase along with Leaf Level data, suppose if Page 1000 is assigned to Level 0, if in Level 1, a new page needs to be created, then Storage Engine will assign 1001 to Level1. This will cause the Fragmentation in Level0. Assigning a new page from another extent for Level 1 makes the disk move forward and back, which delays the write operations.
you can also use commercial tool for defragment your SQL sever database like Redgate SQL Monitor, Lepide SQL Storage Manager etc
Post #1391790
GilaMonster
GilaMonster
Posted Monday, December 03, 2012 1:19 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 37,648,
Visits: 29,900
itsmemegamind (12/3/2012)
you can also use commercial tool for defragment your SQL sever database like Redgate SQL Monitor, Lepide SQL Storage Manager etc
SQL Monitor does not defragment the DB and all that Storage Manager will do is schedule the built-in index rebuild or reorg operations.
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 #1391797
Luke79
Luke79
Posted Monday, December 03, 2012 7:36 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:25 AM
Points: 258,
Visits: 351
The strange thing is that one of the tables in particular, the one which contains over 400k pages and is 3.8GB in size doesn't grow all that much. There was no data written to it in the past week, the fill factor is 0 and yet the fragmentation is over 95% after recent rebuilt. It was rebuilt on 11/30 and no data appended or updated since that time.
Post #1391944
Bhuvnesh
Bhuvnesh
Posted Wednesday, December 05, 2012 3:39 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
Luk (12/3/2012)
the fill factor is 0 and yet the fragmentation is over 95% after recent rebuilt.
the index rebuild works on the basis of the fill factor and in this case i dont think fill factor will help here. can you set the fillfactor 80 or 75 so that you can ge the benefit of index rebuild and disk mangement (with 0 or 100 fillfactor ..more space is required and IO also get affected)
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1392888
GilaMonster
GilaMonster
Posted Wednesday, December 05, 2012 6:18 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 37,648,
Visits: 29,900
Bhuvnesh (12/5/2012)
the index rebuild works on the basis of the fill factor and in this case i dont think fill factor will help here.
What are you trying to say here?
can you set the fillfactor 80 or 75 so that you can ge the benefit of index rebuild and disk mangement
Index rebuilds don't require any specific fill factor. Simply setting fill factor to a lower value with no analysis and no investigation would not be a good thing to do.
(with 0 or 100 fillfactor ..more space is required and IO also get affected)
Actually no, with 0 fill factor less space is required.
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 #1392962
Perry Whittle
Perry Whittle
Posted Wednesday, December 05, 2012 7:02 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201,
Visits: 11,150
Bhuvnesh (12/5/2012)
with 0 or 100 fillfactor ..more space is required and IO also get affected)
You cannot specify a fillfactor of 0 in the rebuild command, any attempt to do so results in the following message
SQL Server database engine
Msg 129, Level 15, State 1, Line 1
Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100.
Now, the fillfactor setting in sp_configure has a default value of 0, this equates to a value of 100.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
Post #1392995
Luke79
Luke79
Posted Wednesday, December 05, 2012 7:51 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:25 AM
Points: 258,
Visits: 351
My guess is that when the db was setup nobody played with fill factor settings and it shows up as 0 on the fragmentation report that I now run once a week. I'll try and restore this db to another box and play with it there. I have limited resources and can't be running stuff in prod during the day.
Post #1393020
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
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.
