EF should not produce differing newlines in SQL based on the OS it happens to run on

[Varorbc]

Ask a question

The same query statement code, slow query on linux, but normal on windows, I use SQL Server Profiler to monitor the query statement, found that the statement is exactly the same, the only difference is the newline character is different, and the execution plan is also different.

Linux

Windows

Include provider and version information

EF Core version: 6.0.12
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: win11
IDE: e.g. Visual Studio 2022 17.4

[roji]

@Varorbc as always, we need a code sample - two screenshot fragments are not enough to understand what's going on.

I'm also not sure this is related to EF; SQL Server should generally be able to accept either newlines. One thought that comes to mind is that EF should maybe not produce differing newlines in SQL based on the OS it happens to run on.

[Varorbc]

@roji I’m sorry, I don’t know what caused this issue, so I can’t provide an example code. The only thing I know now is the difference in newline characters

[Varorbc]

@roji I also asked New Bing and it gave me the following answer. Does this have anything to do with it?

According to the information I found, the line breaks in the statement executed by sp_executesql are linux line breaks, which do not affect performance. However, if you use NVARCHAR type parameters, you may encounter implicit conversion and parameter sniffing issues that can reduce performance . You can try adding OPTION (RECOMPILE) at the end of the statement to avoid this problem. Also, you can consider using stored procedures instead of sp_executesql to improve maintainability and security .

[roji]

That sounds like it's maybe discussing line breaks inside parameter values, as opposed to the SQL itself. For the SQL itself, I'm guessing there's going to be a problem if you send the same query with different line breaks (triggering plan cache misses at the very least). But beyond that, as always, we need some code/repro...

[Varorbc]

Finally, I cleaned up the fragments. The first query was slow, but the rest were normal, and the execution plans were consistent.

[ErikEJ]

If extecution plans are different for the same query it can often be due to parameter sniffing causing a sub-optimal plan to be chosen.

[roji]

The first query was slow, but the rest were normal, and the execution plans were consistent.

Yeah, that sounds like the above - the same SQL was possibly already cached in SQL Server with another line break, and so you had a cache miss. But then the SQL with the new line break got cached and you got normal perf again.

[Varorbc]

If extecution plans are different for the same query it can often be due to parameter sniffing causing a sub-optimal plan to be chosen.

One thought that comes to mind is that EF should maybe not produce differing newlines in SQL based on the OS it happens to run on.

I suspect the root cause is parameter sniffing.
Is it possible to keep newlines consistent across operating systems?

[roji]

Can you confirm that you're running the same EF application on different client operation systems?

[Varorbc]

yeah.

[Varorbc]

The query plan hash is a binary hash value calculated on the execution plan for a given query, and used to uniquely identify similar execution plans.

The query hash is a binary hash value calculated on the Transact-SQL text of a query, and is used to uniquely identify queries.

https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#cache-multiple-plans-for-the-same-query

@roji EF should use consistent line breaks across different operating systems