N-Central Reporting Manager Indexes

Follow

Wanted to make a quick writeup here as we've been noticing more and more clients having issues pulling certain bits of data related to the N-Able Reporting Manager.  However before proceeding, please note:

These are only recommendations.  BrightGauge cannot provide support for anything that occurs if you take the steps below.  Please consult a database administrator if you have any questions before proceeding.

The issue that many clients are experiencing with pulling data from the Reporting Manager is that the queries timeout.  This is because there can be quite a bit of data contained within the tables of the database.  One way a database helps deal with processing large amounts of data is through indexes.  The reporting manager lacks such indexes as it can be labor intensive to the database when it performs it's nightly syncs with N-Central (the ETLs).  If you are ok with the increased sync times and processing cycles, then you can create your own indexes within the SQL database.

Below is a SQL statement that you can run to create the indexes needed for BrightGauge to run efficiently.  This was given to us by another N-Central customer who was having issues with timeouts.  You can run all of it at once.

Note: this is not a supported index to be run by Solarwinds or us.  Just sharing what has worked for another BrightGauge and Solarwinds customer.

CREATE NONCLUSTERED INDEX IX_fact_Utilization_dateid_bgs ON warehouse.dbo.fact_Utilization (dateid);

CREATE NONCLUSTERED INDEX IX_fact_Utilization_deviceid_bgs ON warehouse.dbo.fact_Utilization (deviceid);

Once you run the above, give it some time as generating the indexes will take a bit.  Once it completes, resave your Reporting Manager datasource in BrightGauge and things should be running much better now.

If you run into issues, you can revert the index creation using the following SQL statement (again run it all at once).

DROP INDEXIX_fact_Utilization_dateid_bgs ON warehouse.dbo.fact_Utilization;

DROP INDEXIX_fact_Utilization_deviceid_bgs ON warehouse.dbo.fact_Utilization;