Reducing the trafic to the SQL server from the IM server

[David Cosaboom]

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

[David Claypool]

Yep, that's the way it works.

[David Cosaboom]

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

[David Claypool]

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.

[David Cosaboom]

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.

[Aravindh Rajaram]

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.