How can we help you today? How can we help you today?

how are index stats collected for recommendations

Hi,

I have 4 servers monitored by SQL Response and there are nearly always indexes listed as having excessive fragmentation. The down side to this is that I have an overnight job that reviews index fragmentation and rebuild those that are the worst.

My job collects the statistics using DBCC SHOWCONTIG ('<tablename>') with fast, tableresults, all_indexes and based on the logical fragmentation value goes on to rebuild the index if necessary. The results from this query however dont seem to give the same results as SQL Response recommendations... My job executes and does work and SQL Response lists other indexes that are fragged and at the same time running DBCC SHOWCONTIG before my job runs will list loads of fragmented indexes that SQL Response doesnt seem concerned about.

Is there some sort of filter or threshold in place based on index size or some other statistic that would explain the difference or is SQL Response using a different method to collect the stats?

Would it be possible to use your method in my job?

Which is better?

Jonathan
fatherjack2
0

Comments

5 comments

Sort by
  • fatherjack2
    Hi, me again!

    Are you using sys.dm_db_index_physical_stats with just the filters as per the recommendation settings - eg 50% + 16 pages..? Thats the closest I can find to match by results with DBCC ...
    fatherjack2
    0
  • dlkj
    Hi Fatherjack.

    You're correct, we use DBCC SHOWCONTIG (...) WITH TABLERESULTS, FAST and then filter the results by the LogicalFragmentation and minimum page count according to the Fragmented index recommendation configuration for that server.

    Cheers,
    --
    Daniel
    dlkj
    0
  • fatherjack2
    Daniel,

    If you are using DBCC SHOWCONTIG too how would I be getting different results when I run it locally?

    Even straight after a recommendations refresh (hourly) the results shown in SQL Response are quite different from the results I get in SSMS.

    Jonathan
    fatherjack2
    0
  • dlkj
    How odd,

    Could you email me a screenshot of the recommendation and the output you are getting in SSMS

    Thanks,
    --
    Daniel
    dlkj
    0
  • fatherjack2
    details showing SSMS with one index fragmented and SQL Response showing 12 emailed.
    fatherjack2
    0

Add comment

Please sign in to leave a comment.