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: Tuesday, March 11, 2014 9:32 AM
Points: 52, Visits: 677
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: Today @ 1:58 PM
Points: 41,513, Visits: 34,428
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: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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: Friday, April 04, 2014 7:09 AM
Points: 164, Visits: 257
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: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
@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: Today @ 1:58 PM
Points: 41,513, Visits: 34,428
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