February 16, 2010 at 4:09 am
Hi,
I have created a non-unique, non cluster index in one of the table. But when I run the query in my database
select * from sys.dm_db_index_physical_stats(db_id('Inventory'),null,null,null,null)
I can see that there is avg_fragmentation_in_percent of 75 for the newly created index. Ideally it should not have any fragmentation. Even after rebuilding and reorganizing the index also, the fragmentation is not getting reduced. It is showing the same.
Can anyone please explain me the reason?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
February 16, 2010 at 5:41 am
how many rows are in the table?
February 16, 2010 at 5:46 am
How big's the table? How many pages?
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
February 16, 2010 at 6:18 am
February 16, 2010 at 6:34 am
muthukkumaran (2/16/2010)
Object have cluster index ?
It's not required that a table have a clustered index in order to defragment a nonclustered index.
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
February 16, 2010 at 6:58 am
GilaMonster (2/16/2010)
muthukkumaran (2/16/2010)
Object have cluster index ?It's not required that a table have a clustered index in order to defragment a nonclustered index.
Yes I agreed Gail.
The OP create a Non clustered index,so i guess the table was Heap.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 16, 2010 at 7:03 am
muthukkumaran (2/16/2010)
The OP create a Non clustered index,so i guess the table was Heap.
How do you conclude that?
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
February 16, 2010 at 8:43 pm
Just guess....:-):-P
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 17, 2010 at 6:14 am
Hi All ,
sorry for the late reply. Actually the table is very small it has only 399 rows and size is 234MB. Previously it was a heap table. Just for testing purpose I created a non-unique, non-clustered index on that table.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
February 17, 2010 at 6:19 am
You shouldn't worry about fragmentation on a table with that few rows.
MS recomends that you should not be concerned with fragementation of indexes with less than 1000 pages
February 17, 2010 at 6:20 am
234 MB with only 400 rows? Sure it's not 234 kb? How many pages does the index (not the table) have?
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
February 17, 2010 at 6:54 am
Ok. But can you explain me why the avg_percent_in_fragmentation is not getting reduced for this scenario. For the same column if I create a clustered index in stead of non clustered index, I dont find any fragmentation.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
February 17, 2010 at 6:57 am
Hi Gail,
Sorry. It is 234KB. I typed wrongly.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
February 17, 2010 at 6:59 am
The index storage space is 55KB.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
February 17, 2010 at 7:02 am
Your index is less than 1 extent (8 pages) in size. There's no point in rebuilding that and, considering the way SQL allocates pages for very very small indexes, rebuilding it won't change the fragmentation anyway.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply