Enabling Fragmentation Manager in SQL Sentry

Posted on

Question :

Enabling Fragmentation Manager in SQL Sentry causes slowness on SQL instance ? does it take additional resources ?

Answer :

It depends on which elements of the Fragmentation Management component you enable. There are three different levels of capability that can be enabled;

  1. Collect Index usage stats & Buffer utilization capture
  2. Collect Index Fragmentation details
  3. Enable Index defragmentation capability

As Erik said, if you are collecting fragmentation data then that will have an overhead because if the way that the sys.dm_db_index_physical_stats() DMF works.

Rather than go into more details on the config options and other capabilities in more depth here. Please feel free to open a support ticket with us by emailing Support@SentryOne.com or contact me directly on Jmartin@SentryOne.com.

John Q. Martin
Product Manager
SentryOne

Of course it does. When you first enable it, it has to run sys.dm_db_index_physical_stats on all your tables and indexes. Depending on your server, the size of your data, etc. just checking for fragmentation can take hours.

You can look at an old question of mine for more details on a similar situation.

Leave a Reply

Your email address will not be published.