Would it be possible to resolve the error "Cannot insert duplicate key in object" by setting MAXDop=1?

Question

Here's the corrected text with the description of the problem added:

"I am encountering a 'Cannot insert a duplicate key' error in object XXXXX, even though there are no duplicate data entries in the table. Here is the query I am using:

DECLARE @VALINDEX INT;
SET @VALINDEX = 0;
UPDATE WORKTABLE_xxxxxx 
SET VALINDEX = @VALINDEX, @VALINDEX = @VALINDEX + 1 
WHERE ISNULL(VALINDEX, 0) <> '-1';

The table WORKTABLE_xxxxxx is a temporary table.

I need to determine if adjusting the MAXDop = 1 setting resolves the issue. The problem seems to occur randomly.

When MAXDop is set to 0 or any value greater than 1, the question arises whether the same query can be executed simultaneously by different threads?

If the same query can indeed be executed by different threads simultaneously, what happens if, for instance, Thread 1 executes the query and sets the value of @VALINDEX = 50 + 1, and this information is then sent to another thread executing the same query?

If simultaneous execution by different threads is allowed and coordinated properly, there shouldn't be an issue. However, if not, the MAXDop setting might exacerbate the problem if the query is executed simultaneously by different threads."

Answer 1

Hi @Nechba

The MAXDOP hint limits both the number of threads used by each parallelism operator and the total number of logical CPUs used by all the parallel operators in the query plan. See this article for detailed explains.

When changing MaxDOP to a value > 1, SQL Server has to create multiple plans for each statement, one for the parallel and one for the serial execution. So, the compilation time will be higher for all statements and the system needs more memory for storing SQL plans.

The type of execution is determined through the SQL Server optimizer at runtime, depending on the load on the system and availability of system resources. A query that can run in parallel in the morning (as system load is low) might run in serial in the afternoon with a different runtime. This is especially true for small virtual or physical machines with limited system resources.

Refer to this article: Max degree of parallelism (MaxDOP)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

MAXDop = 1 setting resolves the issue

Absolutley no, never. MAXDOP define the MAX DOP = "Degree of Parallism" = "max CPU" used, nothing else.

If you have trash data, then you have to cleansing them before further processing.