SQL Server 2025 executing request with in SQL connexion is very slow

Question

Hello,

I installed SQL 2025 on a new VM to test the installation of my application, which currently runs on SQL 2022. I’m facing an issue with SQL 2025 (fresh installation on a new machine with a new DB): the queries are extremely slow when I use a SQL connection (SA or a new user). However, when I switch to a Windows connection, I get the same response times as with SQL 2022.

For example, I have a C# loop to insert 36K elements into a table via a simple INSERT like this:

INSERT INTO cpvilles(nomvil, copost) VALUES ('ville', 'copost')

With 2025 and SQL connection => 1 hour With SQL 2025 and Windows connection / SQL 2022 with SQL connection => 2 to 5 minutes

Another example: creating 35,000 foreign keys is also extremely slow (simple ALTER TABLE ADD CONSTRAINT).

I’m running exactly the same code between SQL 2022 and SQL 2025: my application hasn’t changed, and when I switch to a Windows connection on SQL 2025, I no longer have any issues.

Answer 1

It seems you are experiencing significant performance differences when using SQL Server 2025 with SQL authentication compared to Windows authentication. Here are a few potential factors to consider:

1. SET Options: The SQL Server settings (SET options) might differ between your SQL connection and Windows connection. Different SET options can affect how queries are executed. You can check the SET options for both connections and ensure they are consistent.
2. Parameter Sniffing: If your queries use parameters, SQL Server may optimize the execution plan based on the first set of parameters it encounters. This can lead to suboptimal performance for subsequent executions with different parameters. You may want to consider using OPTION (RECOMPILE) for your queries to force a new execution plan each time.
3. Execution Context: The execution context can differ between SQL and Windows authentication. Ensure that the user context has the same permissions and roles in both authentication methods, as this can affect query performance.
4. Network Latency: Although this seems less likely since you're comparing two authentication methods on the same server, ensure that there are no network issues affecting the SQL connection.
5. Database Compatibility Level: Check if the database compatibility level is set correctly for SQL Server 2025. Sometimes, compatibility levels can affect performance. You can check and change the compatibility level using:

      SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
      ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 150; -- For SQL Server 2019
   

6. I/O Performance: Since you mentioned that the performance is significantly better with Windows authentication, it might be worth investigating if there are any I/O issues or resource contention that could be exacerbated by the SQL connection.

You may want to run performance diagnostics to identify any bottlenecks or differences in execution plans between the two authentication methods.

---

References:

• Troubleshoot slow-running queries in SQL Server
• Troubleshoot query performance difference between database application and SSMS

Answer 2

I don't have the time to test this myself right now, but it sounds a bit interesting.

But only a bit interesting. If you are going to insert 35000 rows, you should do that with with 35000 single-row INSERT statements. That is simply not best practice.

Instead, insert 500 rows at time, like this:

INSERT tbl(col1, col2) VALUES(1, 2), (3, 4), ...

Also, you insist on doing single-row inserts, at least wrap them in BEGIN TRANSACTION and COMMIT TRANSACTION to speed things up.

As for the case adding foreign-key constraints: 35000 fk constrains are a lot. I have a database with some 8000 tables and we have been quite vigorous with defining FK constraints. Nevertheless there are only 2229 of them in this database.