CODEWITHSUNDEEP
indexingparallel-processingduplicatessql-server-2016sql-server-2019
TheSQLGuy
TheSQLGuy

Reputation: 1

Possible bug in SQL Server 2019, any solutions?

We did an in place upgrade on one of our lower environment servers from SQL Server 2016 to SQL Server 2019. We have two databases on that server. We put both databases in compatibility level 150. We have a stored procedure that joins Table A to Table B in database A to database B.

INSERT INTO TableA (ColumnA, ColumnB, ColumnC)
    SELECT A.ColumnA, A.ColumnB, B.ColumnB
    FROM TableA A 
    INNER JOIN TableB B ON A.ColumnD = B.ColumnD
    WHERE A.ColumnE = 2

When this was ran in SQL Server 2016, it inserted a unique list of data into TableA. Since upgrading to SQL Server 2019, it is duplicating some rows up to 60 times in one run. If I back the compatibility level down to 130, it goes back to being a unique list.

This only happens when the indexes are disabled. If I enable the indexes, it does not happen or if I make the SELECT perform under MAXDOP 1.

Any ideas? Why would it work so differently? If I were to take the data that would be inserted and look at it, as shown below, it that query only returns one record.

SELECT A.ColumnA, A.ColumnB, B.ColumnB
FROM TableA A 
INNER JOIN TableB B ON A.ColumnD = B.ColumnD
WHERE A.ColumnE = 2 
  AND A.ColumnA = 2342342 
  AND B.ColumnB = 9228382

Is this a bug? I've tried all the CU's to see if it's been fixed or if it was introduced by one of them. Nothing works.

Answers to a couple of the questions that I have been asked.

  1. Yes, this is reproduceable, even by creating a new tables in new databases and copying the data to the new tables before running the INSERT.
  2. I've completely dropped and recreated the indexes.
  3. I've completely dropped all stats and let them rebuild naturally
  4. I am not using NOLOCK
  5. Process runs in 5 minutes with indexes disabled, takes 30 minutes when they are enabled. Rebuild of indexes takes less than a minute. If I drop the indexes and put them back at the end, the build of the indexes takes 20 minutes. This is why we are just disabling them. Has always worked in the past, never an issue.
  6. I would love to post the execution plan, will try and get that done on Monday. Unfortunately, the server is going to be busy that day and I may not be able to get that done. I did take a casual glance at the execution plans for 130 and 150 and they looked identical, but I did not do a thorough examination.
  7. The indexes are definitely needed for other processes that occur on these tables, can't just remove them.

UPDATE:

I have a case open with Microsoft.

Upvotes: 0

Views: 452

Answers (0)

Related Questions