This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(92.8, 51%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDL%3C/text%3E%3C/svg%3E)
Our old production SQL server was 2008 Standard, we migrated databases to our "newer" SQL 2016 Standard instance. This instance has been running for several years. Many of our .NET applications use dynamic SQL queries to retrieve data, in this case they do not use any stored procedure only dynamic SQL.
We connect via the .NET SQL Client (System.Data.SqlClient.SqlConnection) where the queries get executed via SqlCommand. The default timeout is the standard 30 seconds, in this case well enough time to execute the query. Since moving to 2016 we always get query timeouts. Running the same query through SSMS, runs in 100ms without issue.
Changing the code to use .NET SQL ODBC (System.Data.Odbc.OdbcConnection) also works without issue in about 130ms.
I've also setup a couple test instances of SQL 2016 Express and 2019 Express and restored the same database to them, both test instances run the query in 130ms using both the .NET SQL Client and also the ODBC .NET Client (as well as manually in SSMS).
We've done the following, all have failed to correct the problem
ARTHIBORT = ON (to simulate SSMS's connection)
set the legacy cardinality estimation to on.
Rebuilt all table indexes
Rebuilt statistics
Reorganized indexes
Updated SQL 2016 to latest CU16
Set the timeout on the SQL Connection to 10 mins (normal query takes 100ms)
Clear the execution plan cache
We've considered the parameter sniffing possibilities or other query performances, but again this query runs in 100ms via SSMS or another SQL express instance. Yes there are "select *" statements (insert comments here) but again these queries are "light-weight" IMO as they run without notice via the methods listed above. The query has about 19 inner joins on it, by no means excessive from other examples.
Any tips would be greatly appreciated.
I discuss this situation in my aritcle Slow in the Application, Fast in SSMS?. It seems that you have already covered some of the things I discuss, but some of the troubleshooting I discuss may help you.
Hi Erland, I did read most of your article and much of what we tried originated from it. Very well put together and beneficial for a .net developer like myself especially. I will dig deeper into some of the concepts. I'm tempted to change our application to use ODBC for these queries and abandon the .NET SQL client or only use for updates and inserts. Other queries work fine via the .NET client connection in the same application, just these larger queries fail, they really aren't very large.
I would not expect that the client API has anything to do with it. Which you in fact you have already proven when you find that the query runs fast in SSMS, which also uses .NET.
You will need to dig into the query plans and understand why you get different plans - which I assume that you do.
A 19-join query certainly offers possibilities for the optimizer to go astray.
After analyzing the execution plans that SSMS was using and looking at the CardinalityEstimationModelVersion it was using, I can draw these conclusions.
On our SQL 2016 Standard instance I can set the DB compatibility to any value available and SSMS will only use Version 70.
On a SQL 2019 Express instance, SSMS will use the version of the DB compatibility when I change it. On this same 2019 Express instance if I run as DB compatibility of SQL 2016 (v130) the query will timeout in SSMS as well. Changing the compatibility to any other version, SSMS uses the CardinalityEstimationModelVersion of the DB compatibility and runs the query just fine.
Back on our production SQL 2016 instance, the only way I was able to force the legacy cardinality estimator was to include option(use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION')) at the end of my long queries. Setting the legacy cardinality estimator on the DB in question as a database setting did not work on this instance.
I'm a little lost here. On which version(s) of the cardinality estimator is the query fast and when it is slow?
If it's fast only with version 70, I think you should look into the query more deeply.
On our SQL 2016 Standard instance I can set the DB compatibility to any value available and SSMS will only use Version 70.
SSMS as such does not make any decisions about the cardinality estimator. But check with DBCC TRACESTATUS if any trace flag is set. And check in sys.database_scoped_configurations if the legacy cardinality estimator has been forced.