August 11, 2010 at 7:39 am
I done Rebuild On Non clustered Index,
before rebuild on my index, framentation percentage is 80%,
after rebuilding indxes it is showing me same percentage,
Can Please Suggest me on this......:-)
August 11, 2010 at 7:57 am
How big is the index? How many pages in total?
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
August 13, 2010 at 5:57 am
appple (8/11/2010)
I done Rebuild On Non clustered Index,before rebuild on my index, framentation percentage is 80%,
after rebuilding indxes it is showing me same percentage,
Can Please Suggest me on this......:-)
rebuild doesnt work well for small table( less than 1000 data pages)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 13, 2010 at 6:05 am
As general thumb rule most experts agree having the table or index greater than 10000 pages de-fragmentation improves performance lesser , table scan is a effective as non clustered index seek for < 10K pages
There little performance impact for lesser than 10000 data pages.
Cheers
Sat
Cheer Satish 🙂
August 13, 2010 at 6:24 am
@gail
1000 pages or 10000 pages ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 13, 2010 at 6:34 am
1000
That's no more than an educated estimate of the point where fragmentation will start to have a noticeable effect on range scans from disk. It is not the point where an index seek is cheaper than a table scan (which could be anything from a few pages to never, depending on covering state of index and count of rows returned)
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply