Why does the same Linq-to-SQL query consume much more CPU time on the database server for a different project?

Question

I have a legacy .Net 4 project (name it "A") which uses Linq-to-SQL to query a database and I have another .Net 4 project (name it "B") with similiar but not the same code which queries the same database as "A".

Both projects:

• are C# projects {FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}
• use the same assemblies (version v4.0.30319, same folder)
  • System.dll
  • System.Data.dll
  • System.Data.Linq.dll

The auto-generated DataContext is specific for each project but instantiated the same way:

• same connection string using SQL authentication
• both DataContext set their CommandTimeout from the default to 60 seconds
• all other configuration options for the DataContext are the defaults

The way the Linq query is constructed is not exactly the same for the projects but the resulting Linq query is the same. The generated (T-)SQL select statement is the same as well! (monitored and verified the SQL handles on the db server)

The database server is:

• Microsoft SQL Server Enterprise 2005 x64 (9.00.4035.00)
• Operating System: Microsoft Server 2003 R2 SP2 x64

If ran the monitored CPU time (on db server) increased drastically for the query of project "A" and a command timeout exception was thrown. (System.Data.SqlClient.SqlException: Timeout expired)

On the other hand the query of "B" executed within seconds (around 3). I was able to reproduce the behavior by calling the code of "A" with the same parameters again (no changes to code or database). "B" even executed within seconds at the same time "A" was increasing its CPU time.

Regrettably after a co-worker created the indices anew I can no longer reprocude the behavior. The same co-worker mentioned that the query ran fast "last month" (although no code changed from "last month"...).

I debugged the code for both projects - both DataContext instances looked alike. The db server process' sql handle contains the same SQL statement. But "A" threw a timeout exception and "B" executed within seconds - repetitive!

Why does the same Linq-to-SQL query consume much more CPU time on the database server for project "A" as for "B"?

To be precise: If the query runs "slow" due to reasons - repetitive - how can the same query run faster just because it is called by another Linq-to-SQL code?

Can there be side effects I do not know of (yet)? Are there some instance values of the DataContext I have to look at runtime specifically?

By the way: the SQL statement - via SSMS - does use the same query plan on each run.

For the sake of completeness I have linked a sample of:

• the C# code fragments of project "B" (the SqlRequest.GetQuery part looks alike for both projects)
• the SQL file contains the appropriate database schema
• the database execution plan

Please keep in mind that I cannot disclose the full db schema nor the code nor the actual data I am querying against. (The SQL tables have other columns beside the named ones and the C# code is a bit more complex because the Linq query is constructed conditionally.)

Update - more insight at run-time

Some properties of both DataContext instances:

Log = null;
Transaction = null;
CommandTimeout = 60;
Connection: System.Data.SqlClient.SqlConnection;

The SqlConnection was created from a connection string like that (both cases):

"Data Source=server;Initial Catalog=sourceDb;Persist Security Info=True;User ID=user;Password=password"

There are no explicit SqlCommands being run to pass SET options to the database session. Neither contains the inline TVF SET options.

Answer 1

You need to run a trace on SQL Server instead of debugging this from the C# side. This will show you everything both A and B are executing on the server. The execution plan does you no good because it's precisely that - just a plan. You want to see the exact statements and their actual performance metrics.

In the rare event you were to tell me that both SELECT statements are exactly the same but had vastly different performance I would be virtually certain they are running under different transaction isolation levels. A single SQL command is an implicit transaction even if you aren't explicitly creating any.

If for whatever reason the trace doesn't make it clear you should post the commands being ran along with their metrics.

Note: running a trace has some performance overhead cost to it so I would try to keep the timeframe small or run during off-peak if possible.