'LINQ query plan' horribly inefficient but 'Query Analyser query plan' is perfect for same SQL!

Question

I have a LINQ to SQL query that generates the following SQL :

exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[SessionVisit] AS [t0]
WHERE ([t0].[VisitedStore] = @p0) AND (NOT ([t0].[Bot] = 1)) AND 
([t0].[SessionDate] > @p1)',N'@p0 int,@p1 datetime',
@p0=1,@p1='2010-02-15 01:24:00'

(This is the actual SQL taken from SQL Profiler on SQL Server 2008.)

The query plan generated when I run this SQL from within Query Analyser is perfect. It uses an index containing VisitedStore, Bot, SessionDate. The query returns instantly.

However when I run this from C# (with LINQ) a different query plan is used that is so inefficient it doesn't even return in 60 seconds. This query plan is trying to do a key lookup on the clustered primary key which contains a couple million rows. It has no chance of returning.

What I just can't understand though is that the EXACT same SQL is being run - either from within LINQ or from within Query Analyser yet the query plan is different.

I've ran the two queries many many times and they're now running in isolation from any other queries. The date is DateTime.Now.AddDays(-7), but I've even hardcoded that date to eliminate caching problems.

Is there anything i can change in LINQ to SQL to affect the query plan or try to debug this further? I'm very very confused!

Answer 1

This is a relatively common problem that surprised me too when I first saw it. The first thing to do is ensure your statistics are up to date. You can check the age of statistics with:

SELECT 
    object_name = Object_Name(ind.object_id),
    IndexName = ind.name,
    StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc

Statistics should be updated in a weekly maintenance plan. For a quick fix, issue the following command to update all statistics in your database:

exec sp_updatestats

Apart from the statistics, another thing you can check is the SET options. They can be different between Query Analyzer and your Linq2Sql application.

Another possibility is that SQL Server is using an old cached plan for your Linq2Sql query. Plans can be cached on a per-user basis, so if you run Query Analyser as a different user, that can explain different plans. Normally you could add Option (RECOMPILE) to the application query, but I guess that's hard with Linq2Sql. You can clear the entire cache with DBCC FREEPROCCACHE and see if that speeds up the Linq2Sql query.

Answer 2

switched to a stored procedure and the same SQL works fine. would really like to know what's going on but can't spend any more time on this now. fortunately in this instance the query was not too dynamic.

hopefully this at least helps anyone in the same boat as me

Answer 3

We couldn't nail this issue down either. Here are our findings so far:

1) Couple of passes of sp_updatestats and DBCC FREEPROCCACHE's did not change the situation.

2) Running the query inside LINQ Query Visualizer also had the same effect.

3) Extracting query out of sp_executesql and running itself without parameterization used optimized query plan.

4) And finally, running sp_updatestats "once more" fixed the issue for some reason.

It definitely seems like a problem with the nature of ADO.NET connection, compared to Query Analyzer's own. It looks like SQL Server makes a decision based on some SET option which is SET on "Query Analyzer" but not SET on ADO.NET.

The only option that seems to affect query plan decision looks like SET FORCEPLAN. Since the issue is fixed now we'll have to wait for this happen again. Until then, feel free to contribute by trying SET FORCEPLAN, and possibly other SET options out.

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement. The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way in which SQL Server processes the tables to satisfy the query.