Enabling Fragmentation Manager in SQL Sentry

Question

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

Answer 1

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 Suppor[email protected] or contact me directly on [email protected].

John Q. Martin Product Manager SentryOne

Answer 2

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.