Raised case #112930.
Support responded very quickly with the advice that there is currently no orphan record cleanup process.
Suggested I raise a feature request (although I think that this is a design issue that should be treated as a bug).
We literally have 100's of thousands of orphaned records and the database is on a steady growth curve.
Dave.
An example of how this really hurts.
For probably a good application design reason, NCM executes the following query every 20 seconds:
select * from policycacheresults where cacheid = '0'
Unfortunately, as the table doesn't have an index (or a key), the query takes between 22 and 29 seconds to run.
To execute it requires a full table scan on our table which currently has 5,265,872 rows (give or take a few).
I think you can see where I'm going with this.....
Development either needs to provide a means to cleanup the table, create an index or allow us to modify the number of times the query is executed (anything would be better than please enter a feature request).
Dave.
As a temporary measure to deal with the worst performance hit (i.e. the policycacheresults table), I created a non-unique, non-clustered index on the table using the CacheID column.
CREATE NONCLUSTERED INDEX [tmp_idx_cacheid] ON [dbo].[PolicyCacheResults]
(
[CacheID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
The query time for the regular "select * from policycacheresults where cacheid =0" issued by NCM is down from 20+ seconds to almost 0.
Even logging on and building the node list is faster as a result.
It looks to have also solved the slow inventory problem I have been having (NCM INventory Runs very slowly).
Previously an 8 minute inventory would complete 11 devices. Post change, 61 were completed. I will run a full inventory overnight and see if this holds for the complete run.
It is also possible that the table was always meant to have an index, but for some reason was missing from my install. Will check with support on this one.
Dave.
Dave, thanks much for the posts on this one and for opening the support ticket. We have this in dev and are looking into it. For internal folks, this is being tracked as #4259.
Support response to record #112930 was to inform me that "developers informed that they will be improving the performance in a future release"
Not quite what I expected (I think it should be a bug fix), but at least I have bypassed the issue for ourselves.
Dave.
This doesn't appear to have been addressed in NCM v6.
Our PolicyCacheResults table is now almost 60 million rows - it can't handle the load captain!
I'll get the support call re-opened.
Dave.
Dave - I just checked our records and it looks like we did fix this in 6.0. Please do open a support ticket, as this issue should have been resolved with the upgrade. Maybe there is another problem. Please let us know the results.
--Christine
Hi Christine,
Support confirmed that the fix for #112930 (which deals with orphaned records) was meant to be in NCMv6.
It doesn't appear however to have made it - support provided the SQL which should have been included in the maintenance job to perform this operation.
However, this only removed <2% of the 60 million records in the policycacheresults table.
There was another related record (#112931) that also dealt with the number of records in the policycacheresults table (not related to the orphaned record issue).
It appears this one also hasn't been resolved - will get it re-opened as well.
Dave.
Resurrecting this thread as it seems to be unresolved?
My PolicyCacheResults table is currently 12GB after running db maintenance (seemed to have no effect), and I am wondering if it could be safely truncated? I am running NCM v7.0.1.
I have opened case #313887 regarding this.
Received this from support:
Thanks for contacting Solarwinds support
- On your Orion Server.
- Login to the SQL Database Manager(Programs > Solarwinds Orion Network Configuration Manager > SQL Database Manager).
- Select the NCM Database.
- Right-Click any table and choose Query Table.
- Clear out the default query and place the following query instead
- truncate table PolicyCache and PolicyCacheResult.
- Then clicked on Refresh.
- Next stop the NCM caching service and NCM polling service
- Go to the NCM web console, NCM > Compliance > Update Now
Let us know how it goes
That does not address the original issue, but at least cleared out the large table.
Mlan--
Thank you for updating this thread with what you've gotten from Support. I'll re-mark this for the PM to comment with an update.
Thanks,
DH
