SolarWinds Orion NPM Database Index Missing

Hello -

We are evaluating our SolarWinds performance, specifically with node summary pages loading a bit slow, and one of our DBA's raised this question. They found a fair amount of commonly hit queries that have no indexing.

Estimated ‘Improvement	Proposed Index
99.97	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[VoipCDRs] ([OrigPhoneID]) INCLUDE ([ID], [Pkid], [DateTimeDisconnectUTC], [VoipCCMMonitoringID], [GlobalCallID_callId])
99.94	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[VoipCDRs] ([DestPhoneID]) INCLUDE ([ID], [Pkid], [DateTimeDisconnectUTC], [VoipCCMMonitoringID], [GlobalCallID_callId])
99.75	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[VoipOperationResults_Detail] ([VoipOperationInstanceID], [Collapsed]) INCLUDE ([RecordTimeUtc])
99.69	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[cbQoSStats] ([PolicyID]) INCLUDE ([StatsID], [StartDate])
99.65	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[VoipCDRDetails] ([CdrID])
99.38	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[AlertObjects] ([RealEntityUri])
99.25	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[APM_HardwareItem_Detail] ([HardwareItemID], [TimeStamp]) INCLUDE ([Value], [Status], [OriginalStatus], [Message])
99.23	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[AlertObjects] ([RelatedNodeUri])
99.15	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[NodesData] ([Caption])
98.79	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[VoipCCMGateways] ([VoipCCMMonitoringID], [GatewayIndex])
98.7	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[IPAM_Vendors] ([Name]) INCLUDE ([Icon])
98.59	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[Events] ([EventType], [EventTime]) INCLUDE ([NetObjectID])
98.34	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[NodesData] ([DynamicIP], [EngineID], [External]) INCLUDE ([NodeID])
98.3	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[VoipCCMGateways] ([VoipCCMMonitoringID], [Name]) INCLUDE ([GatewayID], [RegionID])
97.54	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[NodesData] ([ObjectSubType], [EngineID], [SNMPVersion]) INCLUDE ([NodeID])
97.39	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[Interfaces] ([InterfaceIndex], [UnManaged])
96.35	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[AlertObjects] ([EntityUri])
96.09	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[NodesData] ([EngineID], [ObjectSubType]) INCLUDE ([NodeID])
95.05	CREATE INDEX <Name of Missing Index> ON [NetPerfMon].[dbo].[Interfaces] ([InterfaceID]) INCLUDE ([Device_Name], [tag])

SolarWinds doesn't believe that these should be indexed by their application. Does this seem right that these are not indexed by SolarWinds?

Appreciate any feedback!

Find more posts tagged with

Indexes

npm

index

Accepted answers

All comments

neomatrix1217

When you say SolarWinds are you saying that support said that it did not need an index?

jm_sysadmin

I am not a DBA. I am not a Developer. I do however spend my life fixing slow things.

Lets talk about what happens when you add an index, first, the information that you index is added to another table that is optimized for a specific search. Indexes aren't free, and the more that you have the more work needs to get done when information is added to a table. Think about the phone records and interface stats, they are constantly getting new information, but you aren't always looking at it. In my environment, I am constantly writing to those tables. The queries to get data that I run don't always have to hit them. Much of the summary information lives in other views, and I only hit the larger tables when I need specific details. Orion also appears to have background queries that deal with the summarization to make the use of the detailed data easier to display. Some of that is likely why its suggested to index them, but in fact the queries replace the index need and make writes less impactful. Think of some tables as a landing zone, optimized for writes. Think of others optimized for reads, to show the website. And there are queries that tie the two together working in the background.

If you take a look at the most expensive queries in my environment, its absolutely custom SWQL I wrote that pulls from tables with a lot of writes. That's on me, but my organization wants that view, so I do my my best, and give my SQL the power it needs. It boils down to what's the cost of adding an index vs not?

There is likely no one answer for what needs an index between customers. It depends on what data you record, and what data you display. The product is designed to prevent issues while returning the best performance (and I believe they do consider suggestions like yours when they tune the product). Personally, I have not seen a need to add an index.