December 1, 2012 at 9:24 am
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.
December 1, 2012 at 1:45 pm
Heaps?
Autoshrink or manual shrink?
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
December 1, 2012 at 1:49 pm
It's a clustered index. Not a heap table.
December 3, 2012 at 12:40 am
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
December 3, 2012 at 1:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2012 at 7:36 am
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.
December 5, 2012 at 3:39 am
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 5, 2012 at 6:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2012 at 7:02 am
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 1Fillfactor 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" 😉
December 5, 2012 at 7:51 am
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.
December 5, 2012 at 7:58 am
You still haven't answered my question about shrinks.
Fill factor is not going to affect how the index rebuild works. It'll affect how the index fragments after that, but not the rebuild operation itself.
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
December 5, 2012 at 8:10 am
We don't shrink this db at all.
December 11, 2012 at 7:20 am
OK, so we're running the automated script that determines which indexes need to be rebuild and tries rebuilding them. For some reason the script logs to the table that the rebuild has taken place but it doesn't do anything. Something to look into I guess. Another question I have is SQL fragementation report. I don't think I should be expecting this situation but it happens. I have a table with a PK clustered index. The primary key has only one field which is a bigint. Now, when I run a fragmentation report that particular PK appears twice because it's allocated to both IN_ROW_DATA and LOB_DATA. Why would it be allocated to LOB_DATA?
December 11, 2012 at 7:34 am
Luk (12/11/2012)
Now, when I run a fragmentation report that particular PK appears twice because it's allocated to both IN_ROW_DATA and LOB_DATA. Why would it be allocated to LOB_DATA?
The table has a LOB column in it somewhere. Varchar(max), nvarchar(max), varbinary(max), XML or one of the old SQL 2000 LOB types.
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
December 11, 2012 at 7:41 am
Yes, that's correct, but the index itselft is composed only of that bigint.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply