topic Re: Reducing the trafic to the SQL server from the IM server in Server Management - Systems Insight Manager

Reducing the trafic to the SQL server from the IM server

David Cosaboom — Thu, 04 Aug 2005 13:56:59 GMT

Our DBA noticed that the HP IM 4.2 is triggering select statements to be executed every second on our SQL server. It is also triggering drop and create index statements. The following are examples of the statements being generated and executed.

SELECT trapItems.trap_id, RTRIM(trap_name), enabled_flag FROM trapItems,trapItemsEx WHERE trapItems.trap_id = trapItemsEx.trap_id AND trap_generic_id = 4
go

insert into taskQueryContents select DISTINCT notices.NoticeId, devices.deviceKey, 241, 2 from devices LEFT OUTER JOIN notices ON devices.deviceKey = notices.deviceKey LEFT OUTER JOIN NoticeType ON notices.noticeType = NoticeType.noticeType JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '00000000864162de000000040000000d' WHERE ( notices.NoticeSeverity = 5 ) AND ( NoticeType.category = 'HP StorageWorks HSV Controller Events' )
go

CREATE INDEX PK_taskcontents ON taskQueryContents(TaskId)
go

select TaskId, NoticeId, DeviceKey, sum(OldFlag) from taskQueryContents group by Noticeid, TaskId, DeviceKey having sum(OldFlag) != 3 order by TaskId
go

drop index taskQueryContents.PK_taskcontents
go

delete from taskQueryContents where OldFlag = 1
go

update taskQueryContents set OldFlag= 1
go

Is there what is making this occur and is there a way to slow it down?

Thanks,
Dave

Re: Reducing the trafic to the SQL server from the IM server

David Claypool — Thu, 04 Aug 2005 14:34:57 GMT

Yep, that's the way it works.

Re: Reducing the trafic to the SQL server from the IM server

David Cosaboom — Thu, 04 Aug 2005 15:17:06 GMT

That's the way it works meaning the select statements will execute every 1 second and the dropping and creating of indexes will contine every 5 to 7 seconds? I'll except your answer, but I just want to make sure I've got things straight.

Thanks for the help.
Dave

Re: Reducing the trafic to the SQL server from the IM server

David Claypool — Thu, 04 Aug 2005 17:10:44 GMT

HP SIM has to constantly scan the database for changes / actions to perform. It's been analyzed from a number of different angles and this is the most efficient way to do it.

Re: Reducing the trafic to the SQL server from the IM server

David Cosaboom — Mon, 08 Aug 2005 08:51:16 GMT

We manage less than 40 servers through insight manager. Perhaps the only way to prevent this trafic over our netwrok is to reinstall the IM using a local MSDE. Do you know if I can configure an installation of IM to use an instance of MSDE installed on another machine? I know that if this is possible we will most likely see the same activity accross the network, but my DBA wants to know. Thanks for your time.

Re: Reducing the trafic to the SQL server from the IM server

Aravindh Rajaram — Mon, 08 Aug 2005 09:57:14 GMT

One thing that you can try is increase the polling interval of the default hardware status polling tasks (If it is feasible for you. By default, it is scheduled to run every 5 minutes for servers and and every 10 minutes for non-servers). This is something that I can think of.