SSIS Job is causing high cpu usage (NETIQKB71195)

  • 7771195
  • 08-Aug-2008
  • 08-Aug-2008

Environment

SQL 2005

Security Manager 6.0

NetIQ_SM_SSIS

Situation

whenever the SSIS job runs the sqlservr.exe process runs at 100%.

The SSIS job runs for 6 or 7 hours.

The temp tables in the smcubedepot are being processed out without any errors.

Resolution

Stop the NetIQ_SM_SSIS  job and run this script in the smcubedepot database. After the script finishes running, start the job again.

use smcubedepot

DELETE  cube_script
WHERE   script_name    = 'ProcessDimension'
 
insert into cube_script values ('ProcessDimension','
INSERT  INTO dim_action
SELECT DISTINCT ISNULL(dim_action, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_action, '''') NOT IN ( SELECT dim_action FROM dim_action)
INSERT  INTO dim_status
SELECT DISTINCT ISNULL(dim_status, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_status, '''') NOT IN ( SELECT dim_status FROM dim_status)
INSERT  INTO dim_computer
SELECT DISTINCT ISNULL(od.an_model, ''''), od.idComputer, ISNULL(od.name, ''''), ISNULL(od.configgroup, '''')
FROM    olap_viewfordimensionsod
LEFT    JOIN    dim_computer   dc ON ( ISNULL(od.an_model, '''') = dc.an_model AND ISNULL(od.name, '''') = dc.name AND od.idComputer = dc.idComputer AND ISNULL(od.configgroup, '''') = dc.configgroup)
WHERE   dc.an_model    IS NULL
AND     dc.name        IS NULL
AND     dc.idComputer  IS NULL
AND dc.configgroup IS NULL
INSERT  INTO dim_object
SELECT DISTINCT ISNULL(od.dim_object_type, ''''), ISNULL(od.dim_object_name, '''') 
FROM    olap_viewfordimensionsod
LEFT    JOIN    dim_object do ON ISNULL(od.dim_object_type, '''')  = do.dim_object_type AND ISNULL(od.dim_object_name, '''') = do.dim_object_name
WHERE   do.dim_object_type IS NULL
AND     do.dim_object_name IS NULL
INSERT  INTO dim_classification
SELECT DISTINCT ISNULL(od.dim_native_classification, ''''), ISNULL(od.dim_classification_category, ''Default''), ISNULL(od.dim_classification, ''Default''), ISNULL(od.dim_netiq_classification, '''') 
FROM    olap_viewfordimensionsod
LEFT    JOIN    dim_classification dc ON ( ISNULL(od.dim_native_classification, '''') = dc.dim_native_classification AND ISNULL(od.dim_classification_category, ''Default'') = dc.dim_classification_category AND ISNULL(od.dim_classification, ''Default'') = dc.dim_classification AND ISNULL(od.dim_netiq_classification, '''') = dc.dim_netiq_classification)
WHERE   dc.dim_native_classification   IS NULL
AND     dc.dim_classification_categoryIS NULL
AND     dc.dim_classification          IS NULL
AND     dc.dim_netiq_classification    IS NULL
INSERT  INTO dim_event_source
SELECT DISTINCT ISNULL(dim_event_source, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_event_source, '''') NOT IN ( SELECT dim_event_source FROM dim_event_source)
INSERT  INTO dim_rule
SELECT DISTINCT ISNULL(dim_rule_number, ''''), ISNULL(dim_rule_name, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_rule_number, '''') + ISNULL(dim_rule_name, '''') NOT IN ( SELECT dim_rule_number + dim_rule_name FROM dim_rule)
INSERT  INTO dim_severity
SELECT DISTINCT ISNULL(dim_severity, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_severity, '''') NOT IN ( SELECT dim_severity FROM dim_severity)
INSERT  INTO dim_source_address
SELECT DISTINCT ISNULL(dim_source_address, ''''), 0 FROM olap_viewfordimensions
WHERE   ISNULL(dim_source_address, '''') NOT IN ( SELECT dim_source_address FROM dim_source_address)
INSERT  INTO dim_source_interface
SELECT DISTINCT ISNULL(dim_source_interface, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_source_interface, '''') NOT IN ( SELECT dim_source_interface FROM dim_source_interface)
INSERT  INTO dim_source_nodename
SELECT DISTINCT ISNULL(dim_source_nodename, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_source_nodename, '''') NOT IN ( SELECT dim_source_nodename FROM dim_source_nodename)
INSERT  INTO dim_source_port
SELECT DISTINCT ISNULL(dim_source_port, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_source_port, '''') NOT IN ( SELECT dim_source_port FROM dim_source_port)
INSERT  INTO dim_source_protocol
SELECT DISTINCT ISNULL(dim_source_protocol, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_source_protocol, '''') NOT IN ( SELECT dim_source_protocol FROM dim_source_protocol)
INSERT  INTO dim_source_user
SELECT DISTINCT ISNULL(dim_source_user, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_source_user, '''') NOT IN ( SELECT dim_source_user FROM dim_source_user)
INSERT  INTO dim_target_address
SELECT DISTINCT ISNULL(dim_target_address, ''''), 0 FROM olap_viewfordimensions
WHERE   ISNULL(dim_target_address, '''') NOT IN ( SELECT dim_target_address FROM dim_target_address)
INSERT  INTO dim_target_nodename
SELECT DISTINCT ISNULL(dim_target_nodename, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_target_nodename, '''') NOT IN ( SELECT dim_target_nodename FROM dim_target_nodename)
INSERT  INTO dim_target_port
SELECT DISTINCT ISNULL(dim_target_port, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_target_port, '''') NOT IN ( SELECT dim_target_port FROM dim_target_port)
INSERT  INTO dim_target_protocol
SELECT DISTINCT ISNULL(dim_target_protocol, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_target_protocol, '''') NOT IN ( SELECT dim_target_protocol FROM dim_target_protocol)
INSERT  INTO dim_target_servicename
SELECT DISTINCT ISNULL(dim_target_servicename, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_target_servicename, '''') NOT IN ( SELECT dim_target_servicename FROM dim_target_servicename)
INSERT  INTO dim_target_user
SELECT DISTINCT ISNULL(dim_target_user, '''') FROM olap_viewfordimensions
WHERE   ISNULL(dim_target_user, '''') NOT IN ( SELECT dim_target_user FROM dim_target_user)
', 'GLOBAL');

Cause

One of the steps in the SSIS job is called 'SQL dimension processing'. In this step we scan through all the temp tables to find out any new value in all of their columns. That sql query is what was causing this spike in the CPU. For some reason sql server is not able to figure out a good execution plan for the pattern of data the customer is having.

Additional Information

Formerly known as NETIQKB71195

This is resolved in 6.5.