Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Unmatched Interface Records

Hi All,

I'm currently trying to track down a database performance issue with our NCM implementation - the inventory process has gone from 2 hours to 17 hours plus (1700 devices). It was about the time we upgraded from SQL2000 to SQL2005 - so until I can show otherwise, I don't think that this is an NCM issue.

One thing I have noticed however, is the database size seems to be on a steady growth path.

There appears to be many thousands of unmatched rows in both the Interfaces table and PolicyCacheResults table (in our case the interfaces table has 148,679 records with a NodeID that has no match with a NodeID within the Nodes table)

I'm assuming that these records should be cleaned up when a Node is removed - is this correct? Is it possible that this is not working correctly?

Dave.

Find more posts tagged with

Accepted answers

All comments

savell

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.

savell

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.

savell

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 (?PageIndex=2).

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.

familyofcrowes

Nice job. Thanks, I will try this later today.

chris.lapoint

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.

savell

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.