sys.dm_db_index_physical_stats - Doubt in the Results

  • For a table with 3 indexes, 1 clustered and 2 NC, Index Physical status dmv returned 53 rows with clustered index repeated 4 times with varying % fragmentation, similarly for other 2 indexes too.

    why multipls rows for one index?

  • One for each level of the index.

    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
  • http://msdn.microsoft.com/en-us/library/ms188917.aspx

    Returns size and fragmentation information for the data and indexes of the specified table or view. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • I ran indexphysical stats dmv and found 4 rows for 1 clustered index with depth 4 row having 80% fragmentation. Even after runnning, ALTER INDEX [INDEX] ON [dbo].

    REBUILD WITH (FILLFACTOR=90, online=on). Fragmentation remains at 100%.

    1. How to reduce this fragmentation in Clustered index at depth 4(in general for all depth rows)

    2. How to reduce frag for NC which too are at 100%

  • balasach82 (7/18/2013)


    1. How to reduce this fragmentation in Clustered index at depth 4(in general for all depth rows)

    Don't bother trying. The highest level is the root, which is only a single page.

    2. How to reduce frag for NC which too are at 100%

    Same way you do for a clustered index. Alter index ... rebuild or Alter index .. reorganize

    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
  • 1. The other 3 Depth rows (other than 1) has more frag >70. Rebuild reduced frag for depth id 1 row

    2. NC - I ran both rebuild and reorganize for couple of NC. There was no change in frag levels

    My select which accesses these tables runs very slow in one server, but runs very quickly in another server with less config setup (less cpu, ram etc)

  • Ignore the non-leaf levels unless they're absolutely huge.

    Let me guess, those nonclustered indexes are tiny, just a couple of pages?

    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
  • Yes, starting from 1 to 230 is the page count for such NC which has more % frag

  • Too small to bother with.

    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
  • Gail, what else can be done to improve query running time. Since query runs in <1min in another dev server but runs for 2hrs in production machine

  • Well index rebuilds are unlikely to make noticable improvements in performance, so maybe the question should rather be 'what can be done'

    Tune the query to use indexes, modify/add indexes to support the query. That's the basis of performance tuning.

    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
  • GilaMonster (7/18/2013)


    Well index rebuilds are unlikely to make noticable improvements in performance, so maybe the question should rather be 'what can be done'

    Tune the query to use indexes, modify/add indexes to support the query. That's the basis of performance tuning.

    I was reading this thread and couldn't really find an answer why the query is running so fast in Dev and takes long on Prod. Gail, can you suggest anything, in terms of that....because if everything you mentioned above could be the issue, then it should behave same in both env, correct?

    Regards,
    SQLisAwe5oMe.

  • SQLisAwE5OmE (7/18/2013)


    I was reading this thread and couldn't really find an answer why the query is running so fast in Dev and takes long on Prod. Gail, can you suggest anything, in terms of that....

    Data volumes, concurrent load, there are hundreds of possible causes.

    because if everything you mentioned above could be the issue, then it should behave same in both env, correct?

    No, definitely not.

    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
  • Gail,

    Both the db's are exact copy of each other and return same amount of records.

    In the server where its working i see parallel query threads. But in issue server, only one thread, no parallel execution. there is nothing against Wait type, its just blank in activity monitor.

    So i tried with max dop in query and gave 16. even then it keeps running. But in the dev server, which hs 2 processors, it runs fine.

    I am at clueless as to what to do next. Any guidance would be helpful

  • Tune the queries, tune the indexes. There's no magic 'go-faster' button

    Grant Frichey's written a good book on performance tuning or you can identify the problematic portion of the procedure and post a thread here for assistance in tuning

    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

Viewing 15 posts - 1 through 14 (of 14 total)

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