SQL transactions (creating/dropping indexes) every 30 seconds?

[Steve Kadish]

Hi all,

I just installed an instace of SIM at a location where it is only monitoring five Proliant servers and sending ping's to three Cisco devices. The SQL admin here has pointed out that there is a rediculous amount of SQL activity being generated. The following, which includes several selects and inserts and creates and drops an index, happens every 30 seconds:

-----------------------------
insert into taskQueryContents select DISTINCT notices.NoticeId, devices.deviceKey, 4, 2 from devices JOIN notices ON devices.deviceKey = notices.deviceKey and notices.NoticeType = 1 LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey WHERE ( deviceProtocolInfo.DMI > 0 OR deviceProtocolInfo.SNMP > 0 OR deviceProtocolInfo.WBEM > 0 )

insert into taskQueryContents select DISTINCT notices.NoticeId, devices.deviceKey, 3, 2 from devices JOIN notices ON devices.deviceKey = notices.deviceKey and notices.NoticeType = 1


insert into taskQueryContents select DISTINCT notices.NoticeId, devices.deviceKey, 72, 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 = '00000000864162de0000000400000006' WHERE ( NoticeType.typeIdStr = 'hp.nodestatus.unreachable' ) AND ( ( devices.productType != 7) )


insert into taskQueryContents select DISTINCT notices.NoticeId, devices.deviceKey, 48, 2 from devices LEFT OUTER JOIN notices ON devices.deviceKey = notices.deviceKey JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '00000000864162de0000000400000006' WHERE ( notices.NoticeSeverity = 5 OR notices.NoticeSeverity = 4 OR notices.NoticeSeverity = 3 )

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
----------------------------------------
Does anyone know what the heck this is doing and why it needs to do it so often?

Thanks,
- Steve

[Geoff Schunicht]

This is normal and expected operations.

The task system is checking to see if new events or systems now meet the list results for tools such as automatic event handling, or the initial data collection task.

[Steve Kadish]

Thanks for the answer. This seems like an awful lot of work to be done every few seconds. I was under the impression that SIM was more event-driven. Since the polling tasks only run every five or 10 minutes, by default, I would think that this sort of checking would happen only upon a status change or a received trap.

I installed the SIM database on the development server used by this group. They tell me that the other applications on this server are noticably slower since SIM has been running. Also, one day after starting the application, the transaction log was 2G.

Is there any way to make this more efficient?

Thanks,
- Steve

[Tim McGue]

Steve,

A couple of things. First your transaction log. Known problem (can turn out to be Big problem). Use the advice here from James Kennedy to correct:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=423686

Now for your original posted problem. I don't think you should accept this as standard procedure. I'll write more on that in a few minutes.

Tim

[David Claypool]

Steve:

The "every 5 or 10 minutes" as you mention are the defaults for a few of the polling operations. They can be scheduled more frequently by the user, all the way up to every 1 minute. Other tasks can also be created with a frequency ranging from 1 minute all the way up as well as scheduling an operation to begin at a particular point in time.

Geoff Schunicht who answered earlier in this thread is one of the architects on the Systems Insight Manager project. In a further conversation he indicated that this was actually (although counter-intuitive) the most efficient method they had worked out to perform the operation.

[Tim McGue]

A couple of things here.

First, the developers might want to consider a more efficient way of rebuilding the index. Take a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_271x.asp

Second, what happens if the last update statement fails?

Tim

[Geoff Schunicht · Accepted solution]

Thanks for the pointers to the SQL indexing performance. Unfortunately the primary reason for dropping the index is to allow the inserts into the taskQueryContents table to occur quicker without needing to maintain the index data. Later prior to selecting and then setting the old column we create the index for straight query and update performance. There is a performance break even point for this as well.

As to the transaction log, try setting the database recovery model to simple via SQL enterprise manager, that will help.

[Steve Kadish]

Thanks for all the in-depth information on this. I know that there is a problem with shrinking transaction logs when the database is using the "Full" model; we've run into that before. Setting it to "Simple" is not a problem. As for the high activity, we will probably create another SQL instance on the SIM server and move the database off of the development server.

- Steve

[Tim McGue]

Thanks Geoff for the details. It makes sense. I was taking a stab that this process might have been related to a major problem I'm having where every so ofter HP SIM sends me event notifications for every single event that ever occurred instead of just new ones.

Tim