Performance degraded under 2000 records in SQL Server 2022 compare to SQL Server 2019

Badhe, Ganesh 60

Hi,

I have observed that bulk delete operation is having poor performance SQL Server 2022

SQL Server 2019

Number of records - array:10000000

Operation - DELETE Operation

Delete Operation taken time is : 34703 ms

SQL Server 2022

Number of records - array:10000000

Operation - DELETE Operation

Delete Operation taken time is : 59620 ms

Note : All configuration are same, Same JDBC code we are using in both SQL Server versions.

We observed that performance issue on SQL Server 2022 compare to 2019. Even this issue we observed on 2000 objects as well.

Suggest if any observation and fixes.

Regards,

Ganesh

Erland Sommarskog 111.1K Reputation points MVP

2024-09-13T21:59:30.4966667+00:00

It is not clear what your operation is. Is that a single DELETE statement? But why do you talk about an array then? Your are not running a loop to delete one row at a time, are you?
ZoeHui-MSFT 36,511 Reputation points

2024-09-14T06:58:03.8133333+00:00

Hi @Badhe, Ganesh,

Have you compared the execution plans to see if there are any difference?
Badhe, Ganesh 60 Reputation points

2024-09-15T11:46:32.8133333+00:00

Erland Sommarskog : We are running 10000000 database rows and one row at a time and we obsreved its slower on 2022 sql server versions.
Badhe, Ganesh 60 Reputation points

2024-09-15T11:49:20.6733333+00:00

@ZoeHui-MSFT : We had raised that concerns with MSFT for 2000 rows and we found difference around 12 seconds comapre to 2019, According to MSFT that it is small difference and they were not able to find anything in the execution plan.

Badhe, Ganesh 60

@Erland Sommarskog : We are running 10million database rows with below code. same code we ran on both 2019 and 2022 and we observed above mentioned regression in performance. Hope you have clear idea now.

/**

CREATE TABLE "IntegerValue01"
(
"Id" INT,
"name" VARCHAR(450),
"address" VARCHAR(600),
"createTime" DATE,
"modifiedTime" DATE,
"updateCount" INT,
"value" INT
)

**/

private static void deleteOperation() {

    *System.out.println("------------Starting DELETE Operation -------------------");*

    *String sql = "DELETE FROM* IntegerValue01*";*

    *try (Connection conn = getConnection(); PreparedStatement prep = conn.prepareStatement(sql)) {*

        *TimeLogger timeLogger = new TimeLogger();*

        *setStartTimeLogger(timeLogger);*

        *boolean isDone = prep.execute();*

        *conn.commit();*

        *setEndTimeLogger(timeLogger);*

        *System.out.println("Delete Operation taken time is : " + (timeLogger.getTimeforProcessing()) + " ms");*

    *} catch (Exception e) {*

        *System.out.println("Error while performing delete operation:" + e.getLocalizedMessage());*

    *}*

    *System.out.println("------------Ended DELETE Operation -------------------");*

*}*

1 answer

Erland Sommarskog 111.1K Reputation points MVP

2024-09-15T19:03:19.7066667+00:00

So you are deleting all rows in the table?

If there no table referencing this table with foreign-key constraint, you could use TRUNCATE TABLE instead. This is a lot more efficient. Note that TRUNCATE TABLE requires an elevated permission on the table, to wit ALTER permission.

If there are referencing foreign keys, TRUNCATE TABLE will error out. One option is to drop the FKs and then restore them.

If you don't want to use TRUNCATE TABLE, you can still look at the indexes on the table. Disable all non-clustered indexes before you start, and re-enable them after the operation with ALTER INDEX REBUILD. This can improve the speed considerably.

As for why the DELETE operation are running slower, do you have SQL 2019 and SQL 2022 on the same hardware? It's quite common to move to new hardware (or a new VM) when upgrading, and it is more likely that there are difference in hardware configuration that matters than the SQL Server version.
Please sign in to rate this answer.
Badhe, Ganesh 60 Reputation points

2024-09-16T04:09:55.6+00:00

@Erland Sommarskog : Above provided code is just dummy code to showcase the slowness problem in newer SQL version i.e 2022. We have legacy code in our product which gives this regression on 2022, and I think fix should be provided at Microsoft level.

We are using the batching, and we are getting slowness issue if records are less than 2000 records, It involves many nested tables that means it could have foreign keys as well. After 2000 records we should

Note: We are using set of machine configuration.

Thanks.

Badhe, Ganesh 60 Reputation points

2024-09-16T04:11:10.1466667+00:00

After 2000 records performance gets increasing.

Erland Sommarskog 111.1K Reputation points MVP

2024-09-16T06:33:22.8933333+00:00

So the code you posted is not the one you are running? You need to post the actual code, so that we can understand what you are doing. You may understand what you are talking about 2000 records, but I don't.

Badhe, Ganesh 60 Reputation points

2024-09-16T06:53:27.6633333+00:00

@Erland Sommarskog : Thanks for your quick response, actual code I cannot post but I think the regression in existing code can resolve our issue. You can simply run that utility, and check exists execution plan to understand the regression in both the versions i.e 2019 & 2022.

But really appreciate your help.

Erland Sommarskog 111.1K Reputation points MVP

2024-09-16T20:39:28.4066667+00:00

You can simply run that utility, and check exists execution plan to understand the regression in both the versions i.e 2019 & 2022.

What utility?

If you want me to run somehing I need a full repro with both code and data.

I understand that it may not be permissible for you to share the code in public, but the response then is if we don't understand what you are doing, we can't help.

If you truly believe that this is a regerssion issue in SQL Server that Microsoft should fix, you need to open a support case. It would also be easier to share the code with a support engineer, since it will be in private between you and the support engineer.

Badhe, Ganesh 60 Reputation points

2024-09-21T14:50:34.98+00:00

do you have SQL 2019 and SQL 2022 on the same hardware ?: Yes we are trying on same machine ..
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Performance degraded under 2000 records in SQL Server 2022 compare to SQL Server 2019

1 answer

Your answer