Staging times from DLP exceed historical averages
Article ID: 259432
Updated On:
Products
Issue/Introduction
The RiskFabric Processing job or the RiskFabric Intraday Processing job runs longer than it has historically. The job step that exhibits the largest increase in duration is Run Staging Procedures.
Environment
Release : 6.x
Cause
Information Centric Analytics (ICA) imports data in motion (DIM) incidents' metadata from Symantec Data Loss Prevention (DLP) using an OPENQUERY function, which is simply a method for SQL Server to execute a SQL statement against an external database. For ICA's Symantec DLP integration, OPENQUERY calls are used to query the DLP database hosted in Oracle. Staging queries use a SELECT INTO statement to output the OPENQUERY call to a local table in the RiskFabric relational database (for example, Stg_SymantecDLP_ActiveIncidentList). An analysis of transaction data captured in the RiskFabric database's Log_DataTransformation table shows OPENQUERY calls to the Symantec DLP database are taking longer to complete than they have historically, yet are returning no more records than the historical average. This condition can be caused by a variety of factors, including: the performance of the Oracle database engine; network latency and throttling; the performance of the Oracle Client installed on the SQL Server host; and the performance of the SQL Server database engine itself.
Resolution
To assist with isolating the cause or causes of this condition, execute a sample query directly in both Oracle and through an OPENQUERY call from SQL Server and compare the time it takes each to complete. If the query runs substantially quicker in Oracle than using OPENQUERY, that indicates something is slowing down the data transfer (network, Oracle Client, disk I/O, etc.). If, however, the statement runs just as slowly in Oracle, that indicates the performance bottleneck lies with the Oracle database engine. Work with your Oracle DBA to troubleshoot.
Additional Information
The following query can be used to provide an extract from the Log_DataTransformation table for analysis:
USE RiskFabric;
GO
SELECT ldt.LogID,
ldt.LogGroupID,
ldt.LoginName,
ldt.LogName,
CAST(ldt.LogDescription AS nvarchar(255)) AS "LogDescription",
ldt.StartDate,
ldt.EndDate,
ldt.RunMinutes,
ldt.StatusFlag,
ldt.SQLAgentJobName,
ldt.StoredProcedureName,
ldt.SourceTableName,
ldt.DestinationTableName,
ldt.DMLAction,
ldt.RowsAffected,
ldt.ErrorCode,
ldt.ErrorDesc,
ldt.ErrorSource,
ldt.ErrorDate
FROM Log_DataTransformation AS ldt WITH (NOLOCK)
INNER JOIN Log_DataTransformationGroup AS ldtg WITH (NOLOCK)
ON ldt.LogGroupID = ldtg.LogGroupID
WHERE CAST(ldt.StartDate AS date) >= CAST(GETDATE()-1 AS date) AND
ldtg.LogGroupName = N'SymantecDLP Staging'
ORDER BY ldt.LogID DESC;
Feedback
