January 5, 2010 at 9:58 am
I did read couple other posts here about rebuilding the index but i still do have some questions/clarifications. From my understanding from this post http://www.sqlservercentral.com/Forums/Topic448556-146-1.aspx?Highlight=rebuild:
i)select avg_fragmentation_in_percent,index_id,page_count from sys.dm_db_index_physical_stats(7,NULL,NUll,NULL,NULL)where index_id>0
and page_count >1000 order by avg_fragmentation_in_percent
I have set the page count more than 1000 since it seems we need to worry only for indexes for which table size is atleat 8 Mb? So from the result of above query i am assuming i would only need to take a look at those indexes.
ii) does rebuild indexing also rebuilds heap index?
Please answer accordingly and thanks again for your valuable advice.
January 5, 2010 at 10:14 am
iqtedar (1/5/2010)
I have set the page count more than 1000 since it seems we need to worry only for indexes for which table size is atleat 8 Mb? So from the result of above query i am assuming i would only need to take a look at those indexes.
1000 pages is the rough rule-of-thumb around where you should worry about fragmentation. It's not an absolute hard and fast number, just a guideline
ii) does rebuild indexing also rebuilds heap index?
A heap is not an index. A heap is a table without a clustered index. As its name implies, it's a heap of data. Since it is not an index, it cannot be rebuilt.
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
January 5, 2010 at 10:42 am
thanks Gila. Got it. I am trouble shooting on a performance issue where there are many tables with out any indexes on it, do you think creating a cluster index would help? My understanding is if there is a table with more than 200 records there should be a clustered index..please advice..
January 5, 2010 at 10:53 am
If they don't have any indexes, then appropriate indexes would help. There's no minimum rows below which an index shouldn't be used. Test and see.
Have a look at this introductory series on indexes. http://www.sqlservercentral.com/articles/Indexing/68439/
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply