'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.