There is an ongoing known issue we're having with the N-Able Reporting Manager that we wanted to make everyone aware of. This issue is related to a SQL table that is core to a few datasets in our integration: fact_utilization.
This is a table that currently powers 3 datasets:
- Server Utilization
- Current Server Diskspace
- Current Workstation Diskspace
And 4 main gauges:
- Server Disk Space Usage
- Workstation Disk Space Usage
- Server Disks above 95% Used
- Server CPU Utilization Trending
From what we understand after a few years working on this table, this table is a collection of sensor readings for each device setup to report these within N-able. The sensor collection (at least) contains:
- CPU usage
- RAM usage
- Disk usage (a row for each disk)
This data then records once an hour. So if an MSP has 500 endpoints, they'll be recording at least 36,000 (3 sensors * 24 hours * 1000 endpoints) rows of data a day. While we're not pulling all of that data, we're starting to run into trouble pulling ANY usable data. The problem is that the table doesn't have "indexes", which is something that makes it easy for a SQL Query to be able to approach a table of data and be directed to where it wants to go. Without an index, the query has to go to each row and check out the data. When we have to check each row, that lengthens the time required to get data, so we get time outs. We did ask N-Able about this so there is some reasoning, but still not helpful for what we're trying to accomplish.
We discovered that one customer has over 17 million rows (and growing each day). We reached out to Solarwinds (makers of N-Able) for help but we couldn't get the right folks on a call through a few mutual partners. We finally got this response from Solarwinds when a customer of ours asked about an index.
We don’t have a formal DB admin in house that can take a look at the partner’s system this at this time. I would suggest the partner’s DB admin review the SQL environment and gather more detailed troubleshooting around the issue at hand and see if there are SQL remediation steps that could be taken to help. The partner can also engage our Support Team via a case to see what can be done if there are native RM performance issues or help answer any questions about RM as it pertains to the SQL environment. They will be able to provide any specifics about mechanics behind the RM data capture and transformations that are also are outlined in N-central online help.
We wouldn’t recommend inserting any new indexes into our warehouse as that can actually have a negative impact on our performance with the ETLs (ODS>Warehouse).
It seems like there isn't much more we can do on our end (without their help). If you experience issues for the above datasets, please reach out to to Solarwinds support for more help.
You can copy and paste this blurb if you'd like:
Hi SolarWinds Support,
We are currently using BrightGauge and some SQL Queries we/they use to crunch data are timing out against our Report Manager database. The fact_utilization table seemingly is the main issue and is necessary for some great visualizations we want to send our customers. They said it's something to do with millions of rows with no index?
Do you have any ideas for us/them to help improve performance of this query for the Report Manager database?
Let us know if you need any help!
For now we have to stop supporting these datasets and gauges for new customers.