Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild on NONclustered Index Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 7:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:08 AM
Points: 52, Visits: 685
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......
Post #967412
Posted Wednesday, August 11, 2010 7:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
How big is the index? How many pages in total?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #967426
Posted Friday, August 13, 2010 5:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #968822
Posted Friday, August 13, 2010 6:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:57 AM
Points: 168, Visits: 271
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
Post #968829
Posted Friday, August 13, 2010 6:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
@gail

1000 pages or 10000 pages ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #968846
Posted Friday, August 13, 2010 6:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #968854
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse