solution for TrapService errors in event viewer yields HUGE performance increase

I was getting an average of 3 trap service errors per second on my new Orion server (9.1 service pack 5). I had my database admin look at it and deletion of outdated traps was taking FOREVER. He added an index for the Time/Date field in the traps table and the errors not only went away, but I got a HUGE performance increase overall. It was taking 45 seconds to open the Orion NPM homepage prior to the change. Now it takes less than 10 seconds. Searches in the trap viewer that used to take 30 seconds or more are now completing in less than a second.

Needless to say, i'm buying my SQL Admin lunch tomorrow!

Find more posts tagged with

Database

trap_service_error

timedate_field

index

Accepted answers

All comments

Karlo.Zatylny

Thanks. We'll look into adding this into the product on installation. This type of index might also benefit customers who gets lots of Syslogs into the Syslog table.

Great tip!

DGoodale

Always glad to post a helpful hint. :-)

brian_duvall

When I went from 9 to 9.1 I also have the Traps page take over 30 seconds to load every time no matter what. Support had me clear the varbinds and all my trap tables..same result and was never fixed. Now I just upgraded to 9.5 and now Events web page is doing same thing. I am wondering if the developers could review this fix to see if this should be done to all the tables like traps, events, syslog, etc to speed things up.

Any comment on when we should see this in the product? I don't want to customize my DB if this will be included in a service pack.

brian_duvall

FYI..have case 99257 opened for issue when Events became slow....case 64389 was opened when Traps became slow.

bshopp

We don't have a firm date I can tell you when that will be included/addressed

FormerMember

can i ask a dumb question, from an SQL server bginner.... how do i actually do this in reality on my database, i.e. step by step? my traps and syslog and events tables take ages to open....

help would be greatly appreciated

AlbanyNYMike

I am also having big problems with the Traps summary page in orion. It will not open, and just times out.

Running 9.5 SP1. SQL is shared, and I dont have rights to do any queries on it.

Events and Alerts pages open slow, but atleast open.

SW,.. please address this in a SP. I have seen lots of posts regarding Trap summarys and solution recomendations that require SQL intervention. Not all of us have access to SQL.
The SQL DBs that I know, do not like being asked to perform queires and insert indexes on camand.

DGoodale

My DBA says:

CREATE NONCLUSTERED INDEX [i1_Traps] ON [dbo].[Traps]( [DateTime] ASC) WITH PAD_INDEX, FILLFACTOR = 80 ON [PRIMARY]

Another thing to watch, though, is how many records you keep. In my environment, I have no need to keep syslog messages or snmp traps for long periods of time but somehow my Orion installation was configured to save all snmp messages for 2 years. I only use them for troubleshooting so for me, 30 days retention is plenty. So, it got faster again when I dropped about 50,000,000 records I didn't need. The setting for snmp and syslog record retention is in the viewers.