| Author |
Topic |
|
ahmad.osama
Posting Yak Master
183 Posts |
 Posted - 2009-03-01 : 03:54:34
|
| some of my index are not defrag even after rebuild command.any idea???Regards,Ahmad Osama |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-01 : 04:54:11
|
What defragmentation level do you have?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-01 : 05:35:17
|
quote:
Originally posted by Peso
What defragmentation level do you have?
E 12°55'05.63"N 56°04'39.26"
There are few indices with 50% or above avg frag ....Regards,Ahmad Osama |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-02 : 06:43:25
|
| any ideas...Regards,Ahmad Osama |
|
|
|
subhash chandra
Starting Member
40 Posts |
Posted - 2009-03-02 : 06:48:21
|
| How are you knowing that fragmentation is 50% or more? Can you share the command used and result you are getting? |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-02 : 06:52:55
|
quote:
Originally posted by subhash chandra
How are you knowing that fragmentation is 50% or more? Can you share the command used and result you are getting?
am using this...sys.dm_db_index_physical_statswell does it really matters what COMMAND i use to get the frag level....Regards,Ahmad Osama |
|
|
|
subhash chandra
Starting Member
40 Posts |
Posted - 2009-03-02 : 07:43:29
|
| COMMAND does not matter in returning the statics but I wanted to know so that I can try to find some resaons that may causing the issue. The issue may be because of ALTER INDEX command as in BOL I found below that :"If ALL is specified and the underlying table is a heap, the rebuild operation has no effect on the table. Any nonclustered indexes associated with the table are rebuilt." |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-02 : 09:50:59
|
| The reason is you can't defrag Heap table. That is why table should have clustered index |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-02 : 12:26:59
|
quote:
Originally posted by sodeep
The reason is you can't defrag Heap table. That is why table should have clustered index
all the tables have clustered index...I also found that clustered index have been defragmentedl, however there's no affect on the nonclustered index....Regards,Ahmad Osama |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
|
|
subhash chandra
Starting Member
40 Posts |
Posted - 2009-03-03 : 02:37:11
|
| How many reords are in the table? I think Tara is suspecting the very few number of index pages and because of that SQL Server may skipping the defragmentation. |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-03 : 05:03:01
|
quote:
Originally posted by subhash chandra
How many reords are in the table? I think Tara is suspecting the very few number of index pages and because of that SQL Server may skipping the defragmentation.
why is it skipping the defragmentation, even if index has few pages..Regards,Ahmad Osama |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-03 : 15:37:11
|
| This behavior is by design. I did this experiment before and my findings are below: 1) Once you increase the number of rows in smaller tables you will see that fragmentation % going down with rebuild. 2) This behavior is seen mostly on objects lesser than 64KB (1 extent) WHY? When you create objects lesser than 64KB pages are allocated from SGAM mixed extents and pages may belong to different extents. 3) If you enable T1118 as startup parameter in that case allocation unit is 1 extent across the board and extents are allocated from Uniform extent no matter the size of object. In that case you may not see that small objects fragmentation behavior. I didn't get time to experiment this but I strongly believe that fragmentation will be minimal or zero for small objects. If anyone has time please experiment it and post results. Does that sound good to you guys. |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-03 : 22:46:43
|
quote:
2) This behavior is seen mostly on objects lesser than 64KB (1 extent) WHY? When you create objects lesser than 64KB pages are allocated from SGAM mixed extents and pages may belong to different extents.
thanks for so...u mean that if an index has less than 8 pages then SQL SERVER will not defrag it...but there has to be a reason .... and the reason in this case is "it is by design" .... Regards,Ahmad Osama |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-03 : 23:11:39
|
| Since SQL Server cannot read less than one full extent from disk, defragmenting an index that is smaller than that doesn't provide any performance improvement. As Tara stated, it could be as high as 1000 pages (256 extents); since this can comfortably fit in SQL Server's buffer space it likely won't benefit from being defragmented. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-04 : 00:13:21
|
quote:
Originally posted by ahmad.osama
quote:
2) This behavior is seen mostly on objects lesser than 64KB (1 extent) WHY? When you create objects lesser than 64KB pages are allocated from SGAM mixed extents and pages may belong to different extents.
thanks for so...u mean that if an index has less than 8 pages then SQL SERVER will not defrag it...but there has to be a reason .... and the reason in this case is "it is by design" ....Regards,Ahmad Osama
Oh yes, there is always a reason behind everything; Even, I don't come to conclusion until I find stellar supporting reasons and my curiosity lead me to above conslusion. If you have had done experiment using this scenario and came across something else. I would be curious to know. BTW- Why its By design? First, Because you dont want SQL engine to work on something which is time taking (even in millisec or microsec) and don't improve query performance. Imagine a situation where a customer has thousands of small tables and you defrag it without query performance improvement in that case it will be a waste of effort and misutilization of resources. Every millisecond of CPU,Memory and IO resource is important, on a busy system resource allocated to one request is resource denied to other. It is super important to make database engine efficient and smart decisions. Second, you may also see this behavior on small tables with large data types. Verify your small table definition which are fragmented. Other scenarios I explained in above post. Its a simple logic unlike "what came first chiken or egg" problem. |
|
|
|
Next Page
|
Index Defrag issue:-