Post reply

Relation between range_scan_count and fragmentation

  • October 22, 2013 at 2:58 pm

    #278353

    Hi,

    I am trying to setup a re index job and I was trying to understand what relation should I be using between range_scan_count and fragmentation to rebuild or reorganize an index. lets suppose if the range_scan_count is high will the fragmentation of the index will be high? Could some one clarify if there is any relation ship between range_scan count and fragmentation..

    Thanks in advance,

    Abhishek.

  • October 22, 2013 at 4:34 pm

    #1660754

    There isn't a relationship.

    range_scan_count

    Cumulative count of range and table scans started on the index or heap.

    So that's the number of range scans executed against that index since SQL Server was restarted.

    Logical fragmentation is a physical measure of how disorganised an index is.

    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

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

  • October 22, 2013 at 4:45 pm

    #1660757

    lets suppose an example... If for particular index we have logical fragmentation as 10% and the range_scan_count as 400000.. will that index need to re-indexed?

  • October 22, 2013 at 4:53 pm

    #1660758

    abhishekreddy62 (10/22/2013)

    lets suppose an example... If for particular index we have logical fragmentation as 10% and the range_scan_count as 400000.. will that index need to re-indexed?

    Logical fragmentation looks low so not on that basis, but since you haven't shown avg_page_space_used or page count, can't really say. It might need rebuilding due to low page density. On the other hand, the page count may be too low for it to benefit from rebuilding at all.

    The range scan count is pretty close to irrelevant. To see one reason why, take that scenario you described, restart SQL and then ask exactly the same thing. Logical fragmentation will still be 10%, range scan count will be 0.

    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

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

  • October 22, 2013 at 4:56 pm

    #1660760

    That make perfect sense ... Thanks 🙂

  • October 22, 2013 at 5:00 pm

    #1660761

    One more quick question.. sorry to bother..

    How do i increase the procedure cache hit ratio on sql server???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply