High Fragmentation on SQL Monitor Repository Tables

jlray

The SQL Monitor Repository/DB is on one of the servers being monitored by SQL Monitor.  Some the indexes on the repository tables are generating fragmentation alerts.  The offending indexes are almost always on the various data.Cluster_SqlServer_* tables.  Index maintenance is performed early Saturday mornings. but by Monday morning (48 hrs) these indexes are fragmented over 90% with the number of pages exceeding way over 1000.  

• Is it normal for this much activity to happen on these tables so that they generate this much fragmentation in a relatively short period of time - especially since we don't even have a clustered environment but all this activity is happening against tables presumably used for monitoring a cluster?

• Are there any tuning/performance/index maintenance recommendations for the repository DB?

Thanks,

Jerry

  

Eddie D

Hi Jerry, thank you for your forum post.

SQL Monitor treats standalone machines as a cluster of one, so the data is stored in the data.cluster tables. These can get fragmented because SQL Monitor writes a lot of data to this table.

It will be exacerbated by the fact that SQL Monitor is monitoring the SQL instance that hosts its own repository, which is one reason why we recommend users not configuring SQL Monitor to monitor its own repository.

Many Thanks
Eddie

jlray

Thanks for the info!