title: SQL Server 2008: Very slow query performance when dealing with specific date boundary
body: <p>Right, this is an odd one.</p>
  
  <p>We have a web service that returns data to a silverlight client. The queries are generated against a SQL Server 2008 database using the entity framework. Most of these queries are date range based - pull results between this date and that, for example. Also, a view is used to make things a little easier.</p>
  
  <p>We have noticed that when issuing a specific set of queries that start on or after a certain date, execution will be very slow. This date is the 5th November, any year. If our start date is one day earlier execution will be instant. So, 5th - 6th, slow. 4th - 6th - quick.</p>
  
  <p>Here is the SQL that EF generates:</p>
  
  <p><strong>THIS QUERY WILL BE VERY SLOW (30 seconds)</strong></p>
  
  <pre><code>SELECT 
  1 AS [C1], 
  [GroupBy1].[K1] AS [Name], 
  [GroupBy1].[A1] AS [C2]
  FROM ( SELECT 
  [Extent1].[Name] AS [K1], 
  SUM([Extent1].[Value]) AS [A1]
  FROM (SELECT 
    [view_answers].[Value] AS [Value], 
    [view_answers].[Comment] AS [Comment], 
    [view_answers].[NewStockist] AS [NewStockist], 
    [view_answers].[NewDistPoint] AS [NewDistPoint], 
    [view_answers].[VoucherUsed] AS [VoucherUsed], 
    [view_answers].[CashTotal] AS [CashTotal], 
    [view_answers].[AnswerType] AS [AnswerType], 
    [view_answers].[StartTime] AS [StartTime], 
    [view_answers].[ActualEndTime] AS [ActualEndTime], 
    [view_answers].[Complete] AS [Complete], 
    [view_answers].[UserID] AS [UserID], 
    [view_answers].[UserName] AS [UserName], 
    [view_answers].[QuestionType] AS [QuestionType], 
    [view_answers].[ProductSKU] AS [ProductSKU], 
    [view_answers].[BrandID] AS [BrandID], 
    [view_answers].[TeamID] AS [TeamID], 
    [view_answers].[Name] AS [Name], 
    [view_answers].[Stage] AS [Stage], 
    [view_answers].[Question] AS [Question]
    FROM [dbo].[view_answers] AS [view_answers]) AS [Extent1]
    WHERE 
       ([Extent1].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D') 
       AND (N'Distribution Points' = [Extent1].[QuestionType]) 
       AND ([Extent1].[StartTime] &gt;= '11/05/2009 00:00:00') 
       AND ([Extent1].[StartTime] &lt;= '11/08/2009 00:00:00') 
       AND (1 = [Extent1].[Complete]) 
       AND (2 = [Extent1].[BrandID]) 
       AND (N'Distribution Points' = [Extent1].[QuestionType])
    GROUP BY 
       [Extent1].[Name])  
    AS [GroupBy1]
  </code></pre>
  
  <p><strong>THIS ONE WILL BE MUCH QUICKER</strong></p>
  
  <pre><code>    SELECT 
      1 AS [C1], 
      [GroupBy1].[K1] AS [Name], 
      [GroupBy1].[A1] AS [C2]
      FROM ( SELECT 
      [Extent1].[Name] AS [K1], 
      SUM([Extent1].[Value]) AS [A1]
      FROM (SELECT 
        [view_answers].[Value] AS [Value], 
        [view_answers].[Comment] AS [Comment], 
        [view_answers].[NewStockist] AS [NewStockist], 
        [view_answers].[NewDistPoint] AS [NewDistPoint], 
        [view_answers].[VoucherUsed] AS [VoucherUsed], 
        [view_answers].[CashTotal] AS [CashTotal], 
        [view_answers].[AnswerType] AS [AnswerType], 
        [view_answers].[StartTime] AS [StartTime], 
        [view_answers].[ActualEndTime] AS [ActualEndTime], 
        [view_answers].[Complete] AS [Complete], 
        [view_answers].[UserID] AS [UserID], 
        [view_answers].[UserName] AS [UserName], 
        [view_answers].[QuestionType] AS [QuestionType], 
        [view_answers].[ProductSKU] AS [ProductSKU], 
        [view_answers].[BrandID] AS [BrandID], 
        [view_answers].[TeamID] AS [TeamID], 
        [view_answers].[Name] AS [Name], 
        [view_answers].[Stage] AS [Stage], 
        [view_answers].[Question] AS [Question]
        FROM [dbo].[view_answers] AS [view_answers]) AS [Extent1]
      WHERE 
         ([Extent1].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D') 
         AND (N'Distribution Points' = [Extent1].[QuestionType]) 
         AND ([Extent1].[StartTime] &gt;= '11/04/2009 00:00:00') 
         AND ([Extent1].[StartTime] &lt;= '11/08/2009 00:00:00') 
         AND (1 = [Extent1].[Complete]) 
         AND (2 = [Extent1].[BrandID]) 
         AND (N'Distribution Points' = [Extent1].[QuestionType])
      GROUP BY 
         [Extent1].[Name])  
      AS [GroupBy1]
  </code></pre>
  
  <p>If we set the start date to the 5th Nov last year, execution will be slow, the 4th Nov last year, and its fast again. Looking at the data in the database there is nothing unusual around the 5th. Also, it appears that queries where the start date is after the 5th will run slow.</p>
  
  <p>I'm stumped!</p>
  
  <p>(the database is hosted remotely so I don't have direct access to it)</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thanks for thr replied guys. Firsltly, I should probably make it clear that my knowledge of sql server is skin deep. I build databases of varying degress of quality and then use something like Linq To SQL or EF to work with them. So I feel a little out of my depth here.</p>
  
  <p>Regarding the joins - the view I'm querying does include data from about 6-7 tables. I shall try and grab some stats next time at work and add some more info here. I dont really know much about execution plans, or is they are something I can see through SQL Server Management Studio </p>
  
  <p><strong>UPDATE</strong>
  Stats from slow query
  (3 row(s) affected)</p>
  
  <pre><code>Table 'tblProducts'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblQuestionTypes'. Scan count 0, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblQuestions'. Scan count 0, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblSessions'. Scan count 0, logical reads 28551, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblAnswers'. Scan count 1, logical reads 1976256, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblCalls'. Scan count 1, logical reads 439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblUsers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  </code></pre>
  
  <p>Stats from fast query</p>
  
  <pre><code>Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblAnswers'. Scan count 1, logical reads 7008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblProducts'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblQuestions'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblQuestionTypes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblCalls'. Scan count 1, logical reads 439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblSessions'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tblUsers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  </code></pre>
  
  <p><strong>UPDATE</strong>
  The query execution plan (a new thing to me) is suggesting that I add a new index to a row in one of the tables - questionid in the answers table. The suggested sql is as follows:</p>
  
  <pre><code>USE [brandfourcoke]
  GO
  CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
  ON [dbo].[tblAnswers] ([QuestionID])
  INCLUDE ([CallID],[Value])
  GO
  </code></pre>
  
  <p>Estimated improvements in query cost are 93%.
  Should I go ahead and do this? The database is in a live environment and uses sql server 2008's change tracking and the sync framework. As such Im always concerned that database changes will impact on the tracked data and break things for my clients. Will adding an index reset change tracking data?
  Thanks for all your help guys. I feel like a complete beginner here.</p>