11

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!

Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689
  • How to view execution plan in different contexts: http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Simon_Weaver Jan 02 '13 at 23:28

2 Answers2

7

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • i ran sp_updatestats which took about 7 minutes. then i ran DBCC FREEPROCCACHE which returned instantly with no errors. still the same behavior though. the SELECT you provided is showing todays date now for all the stats (some of which were 2 years old before!). i may have to try with a stored proc. ill try comparing SET options tomorrow. thanks! – Simon_Weaver Feb 22 '10 at 11:24
  • btw. even within LINQ it seems fine if the date is within the last 48 hours (not sure which query plan this is, but perhaps there are few enough rows that a scan is ok). once i go above about 60 hours it switches to the other query plan and never returns. but from Query Analyser i can do a month, year, 2 years and it returns instantly (this is a count of 233000 rows)! again same exact SQL taken from profiler. wierd... – Simon_Weaver Feb 22 '10 at 11:37
  • If your first LINQ query is within 48 hours, maybe SQL Server will generate a plan that optimizes for that case. What happens if you `dbcc freeproccache` and have the first query be above 60 hours? Also consider rewriting the Linq2Sql using ExecuteQuery (http://msdn.microsoft.com/en-us/library/bb399403.aspx), that allows you to specify `option (recompile)` – Andomar Feb 22 '10 at 12:07
  • still stumped here! i opened LINQPad on the server and ran a query iterating from 1 to 60 days and it managed to return this query in 5 seconds for 60 queries. again LINQPad is generating the same exact SQL as I see generated by my ASP.NET application - its just using a good query plan. i've even rebooted the server and upgraded (finally) to sql server 2008 - still its not doing what i want. i'm fine with switching to raw SQL if i have to - i just want to undestand whats going on. i've been remarkably lucky with LINQ to SQL performing well for me so far with much much more complicated queries – Simon_Weaver Feb 23 '10 at 20:22
  • @Simon: Are you connecting using the exact same connection string? – Andomar Feb 24 '10 at 08:54
  • probably not the exact same string when using linqpad. what possible difference are you thinking about? when i switched to a stored proc i was using the exact same instance of datacontext/connection string – Simon_Weaver Feb 24 '10 at 21:39
  • @Simon: Credentials can make a difference, and default language too – Andomar Feb 25 '10 at 08:50
0

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

Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689