Home Dashboard Directory Help
Search

DQS_Projects table failing with unique key constraint error by TreyColey


Status: 

Closed


1
0
Sign in
to vote
Type: Bug
ID: 780603
Opened: 3/4/2013 10:58:43 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

We are running the most recent version of SQL 2012 DQS (2011.110.3339.0) and I am running into errors. Unfortunately the errors are not being reported back to the SSIS package so the errors are not resulting in a failure.

Background:
DQS creates a set of tables for each knowledge base and each project (execution instance).

Here is a knowledge base table for CACH. (format = <Database>.<TableName>.<Table>)

DQS_MAIN.KnowledgeBase1000001.V_B_DOMAINS

Here is a project (execution instance) table for CACH. (format = <Database>.<TableName>.<Table>)

DQS_Projects.DQProject1000089.X_DOMAIN_CORRECTION_PROFILING



After waiting several hours for a 100K set of rows to load, I checked the DQServer.DQS_MAIN.log file to find that there was a unique key violation happening in two of the project tables involved in the current execution. The SSIS package never failed.
2/27/2013 5:03:03 PM|[]|12|ERROR|PUID|Microsoft.Ssdqs.Cleansing.Correction.Service.ProfilingCorrectionNotifications|

Failed committing correction profiling data for domain id 1057 in inverted range 1281916-1294447:

Violation of UNIQUE KEY constraint 'UNQ_X_RECORD_CORRECTION_PROFILING_RECORD'.
Cannot insert duplicate key in object 'DQProject1000089.X_RECORD_CORRECTION_PROFILING'.
The duplicate key value is (1024633, 1058).;

Violation of UNIQUE KEY constraint 'UNQ_X_DOMAIN_CORRECTION_PROFILING'.
Cannot insert duplicate key in object 'DQProject1000089.X_DOMAIN_CORRECTION_PROFILING'.
The duplicate key value is (1058, 1294447).;

The statement has been terminated.;
The statement has been terminated.

System.Data.SqlClient.SqlException (0x80131904):
Violation of UNIQUE KEY constraint 'UNQ_X_RECORD_CORRECTION_PROFILING_RECORD'.
Cannot insert duplicate key in object 'DQProject1000089.X_RECORD_CORRECTION_PROFILING'.
The duplicate key value is (1024633, 1058).;

Violation of UNIQUE KEY constraint 'UNQ_X_DOMAIN_CORRECTION_PROFILING'.
Cannot insert duplicate key in object 'DQProject1000089.X_DOMAIN_CORRECTION_PROFILING'.
The duplicate key value is (1058, 1294447).;

The statement has been terminated.;
The statement has been terminated.;



The domain associated with this error is “ExistingCOBCode”. The rules being applied to this column are:
Length is equal to 1
Value is numeric
Value is in 1,2,3,0

99, 104 records were successfully processed before this error appeared. I cannot imagine what I am doing to cause this error. I believe that this is a bug with DQS.
Details
Sign in to post a comment.
Posted by Microsoft on 8/19/2013 at 3:30 PM
Hi TreyColey,

The failures in the profiling are already caught by the code, so they do not fail the execution. According to the description, the execution never finishes. Unfortunately, it seems to be a performance issue related to a big number of domains (52 domains) and records.

You can try the following:
a. We have limitation on parallelization of execution on threads, set using the SQL Server Service Broker – kind of throttling mechanism. It is set automatically as a function of number of CPUs.
b. You can check it by examining properties of the following Service Broker queues: DQS_MAIN.ParallelCalibrationRequestQueue, DQS_MAIN.ParallelExecutionRequestQueue
c. Most likely that the value will be much lower than 52 (e.g. around 12)
d. You can try to alter this value to 52, to remove the throttling.

You can give this a try but unfortunately we are not sure if it is a good solution – we may encounter other types of issues, like SQL deadlocks or high disk I/O. But it is the only immediate proposal that we have – correction has known performance problems, that cannot be simply solved.

Hope this helps.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Validate Membership DQS.zip (restricted) 3/4/2013 -
DQServerLog.DQS_MAIN.log (restricted) 3/4/2013 -
DQS Issue Details.zip (restricted) 3/4/2013 -