how are index stats collected for recommendations

fatherjack2fatherjack2 Posts: 311
edited February 4, 2009 9:24AM in SQL Monitor Previous Versions
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

Senior DBA
Careers South West Ltd

Comments

  • 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 ...

    Senior DBA
    Careers South West Ltd
  • 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
  • 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

    Senior DBA
    Careers South West Ltd
  • dlkjdlkj Posts: 151
    edited April 7, 2010 10:58AM
    How odd,

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

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

    Senior DBA
    Careers South West Ltd
Sign In or Register to comment.