title: Query Runs Slower The Second Time
body: <p>I have this massive query that I can typically run in under 2 minutes. However, when I run it a second time about a minute after, it goes on infinitely... so I kill the process and my SSMS session. I don't have any other jobs running in the background. </p>
  
  <p>Is something else being retained on the server? I think I'm missing something as far as how SQL Server works.</p>
  
  <p>Thanks.</p>
  
  <p>EDIT: Here's the SQL (had to do a little obfuscation)</p>
  
  <pre><code>SELECT  pl.OrangeLocationID ,
      e.EventID ,
      cr.Title AS [Event Type] ,
      su.LastName + ', ' + su.FirstName AS FMR ,
      CONVERT(VARCHAR(20), pl.Report_Date, 101) AS [Report Entry Date] ,
      l.Name ,
      l.Number ,
      ll.SodaPopLocationID AS [SodaPop Location ID] ,
      l.Zip ,
      c.Channel ,
      pl.DT AS [ReportedDate] ,
      RIGHT(pl.DT_start, 8) AS [ReportedStartTime] ,
      RIGHT(pl.DT_end, 8) AS [ReportedEndTime] ,
      [CMS].dbo.dDateDiff(pl.DT_start, pl.DT_end) AS [ReportedDuration] ,
      pl.scheduled_date AS [ScheduledDate] ,
      RIGHT(pl.scheduled_start, 8) AS [ScheduledStartTime] ,
      RIGHT(pl.scheduled_end, 8) AS [ScheduledEndTime] ,
      [CMS].dbo.dDateDiff(pl.scheduled_start, pl.DT_end) AS [ScheduledDuration] ,
      e.HoursPaid AS [Rep Hours Worked] ,
      ISNULL(PP.[RepCount], 0) AS [RepCount] ,
      CASE WHEN [CMS].dbo.dDateDiff(pl.DT_start, pl.DT_end) = ( e.HoursPaid / ISNULL(PP.[RepCount], 1) )
           THEN [CMS].dbo.oa_HourDateDiff(pl.DT_start, pl.DT_end)
           WHEN [CMS].dbo.dDateDiff(pl.scheduled_start, pl.DT_end) = ( e.HoursPaid / ISNULL(PP.[RepCount], 1) )
           THEN [CMS].dbo.oa_HourDateDiff(pl.scheduled_start, pl.DT_end)
           ELSE ( e.HoursPaid / ISNULL(PP.[RepCount], 1) )
      END AS [FinalDuration] ,
      g.[Description] AS [OA Market] ,
      g.SodaPop_Region AS [SodaPop Region] ,
      g.SodaPop_Area AS [SodaPop Area] ,
      coup4 ,
      coupo ,
      coupo_e ,
      card_num ,
      promo ,
      promo_no ,
      promo_no_o ,
      highlight1 ,
      highlight2 ,
      highlight3 ,
      mgmt_reaction ,
      mgmt_reaction_e ,
      comm_p ,
      comm_n ,
      r.comments ,
      s_fname ,
      s_lname ,
      v_title ,
      ll.KeyAccountCorp AS [Key Account Corp.] ,
      interact_new + interact_rep AS [interact_total] ,
      samp_new + samp_rep AS [samp_total] ,
      purch_new + purch_rep AS [purch_total] ,
      23 / ( NULLIF(( interact_new + interact_rep ), 0) * 1.0 ) AS [Int_Crate] ,
      CASE WHEN sampletype = 11 THEN ( purch_new + purch_rep ) / ( NULLIF(( samp_new + samp_rep ), 0) * 1.0 )
           ELSE NULL
      END AS [Samp_Crate] ,
      coup1 + coup2 AS [coup_total] ,
      CASE WHEN coup1 + coupo &gt; 0 THEN 1
           ELSE 0
      END AS [CoupDist] ,
      DATEPART(month, pl.DT) AS [Visit_Month] ,
      DATEPART(quarter, pl.DT) AS [Quarter] ,
      DATEPART(weekday, pl.DT) AS [Weekday] ,
      CASE DATEPART(weekday, pl.DT)
        WHEN 6 THEN 'Fri'
        WHEN 7 THEN 'Sat'
        WHEN 1 THEN 'Sun'
        ELSE 'Mon-Thurs'
      END AS [Weekday_Grouped] ,
      CASE WHEN dbo.Exception(pl.OrangeLocationID, 12) = 1
                OR dbo.Exception(pl.OrangeLocationID, 13) = 1
                OR dbo.Exception(pl.OrangeLocationID, 14) = 1 THEN 1
           ELSE 0
      END AS [EVolume] ,
      CASE WHEN dbo.DoesHaveException(pl.OrangeLocationID, 18) = 1 THEN 1
           ELSE 0
      END AS [CVolume] ,
      CASE WHEN dbo.eException(pl.OrangeLocationID, 9) = 1
                OR dbo.eException(pl.OrangeLocationID, 22) = 1 THEN 1
           ELSE 0
      END AS [Volumes] ,
      CASE WHEN dbo.eException(pl.OrangeLocationID, 8) = 1
                OR dbo.eException(pl.OrangeLocationID, 21) = 1 THEN 1
           ELSE 0
      END AS [Sales Price] ,
      CASE WHEN dbo.eException(pl.OrangeLocationID, 11) = 1 THEN 1
           ELSE 0
      END AS [Sample Volume] ,
      ISNULL(i.[NormalizedSold], 0) AS [EQBottlesSold] ,
      CASE WHEN ISNULL(purch_new, 0) = 0 THEN 0
           ELSE ISNULL(i.[NormalizedSold], 0) / ( purch_new + purch_rep )
      END AS [EQBottlesSoldPerPurch] ,
      ac.AvgSales ,
      ac.STDEVSales ,
      ( ISNULL(i.[NormalizedSold], 0) - ac.AvgSales ) / ac.STDEVSales AS [sl] ,
      ac.AvgPurchasers ,
      ac.STDEVPurchasers ,
      ( ISNULL(r.purch_new, 0) - ac.AvgPurchasers ) / ac.STrchasers AS [ZScore_Purchasers] ,  
  
      ac.AvgConversions ,
      ac.STDEVConversions ,
      ( ISNULL(( purch_new + purch_rep ) / ( NULLIF(( interact_new ), 0) ), 0) - ac.AvgConversions )
      / ac.STDEVConversions AS [ZScore_Conversions] ,
      ac.[AvgSalesPerPurchaser] ,
      ac.[STDEVSalesPerPurchaser] ,
      ( ISNULL(( CASE WHEN ISNULL(purch_new, 0) = 0 THEN 0
                      ELSE ISNULL(i.[NormalizedSold], 0) / ( purch_new + purch_rep )
                 END ), 0) - ac.[AvgSalesPerPurchaser] ) / ac.[STDEVSalesPerPurchaser] AS [SalesPerPurchaser] ,
      ( ( ( ISNULL(i.[NormalizedSold], 0) - ac.AvgSales ) / ac.STDEVSales )
        + ( (ISNULL(( CASE WHEN ISNULL(purch_new + purch_rep, 0) = 0 THEN 0
                           ELSE ISNULL(i.[NormalizedSold], 0) / ( purch_new + purch_rep )
                      END ), 0) - ac.[AvgSalesPerPurchaser]) ) ) / 4 AS [core] ,
      ( ( (( ISNULL(i.[NormalizedSold], 0) - ac.AvgSales ) / ac.STDEVSales) ) / 4 ) + 3 AS [core] ,
      su.aaUserID ,
      l.LsocationID 
  FROM [CMS_SodaPop].dbo.Schedule pl WITH ( NOLOCK )
      INNER JOIN [CMS_SodaPop].dbo.Report r WITH ( NOLOCK ) ON r.OrangeLocationID = pl.OrangeLocationID
      INNER JOIN [CMS].dbo.Users su WITH ( NOLOCK ) ON su.UserID = pl.Rep_FMR
      INNER JOIN [CMS].dbo.Locations l WITH ( NOLOCK ) ON l.LocationID = pl.LocationID
      INNER JOIN [CMS].dbo.OrangeReports cr WITH ( NOLOCK ) ON cr.RedID = pl.RedID                                                                 
      INNER JOIN [CMS_SodaPop].dbo.Events e WITH ( NOLOCK ) ON e.OrangeLocationID = pl.OrangeLocationID
      INNER JOIN [CMS_SodaPop].dbo.MarketList g WITH ( NOLOCK ) ON g.GroupID = pl.GroupID
      INNER JOIN [CMS_SodaPop].dbo.Locations ll WITH ( NOLOCK ) ON ll.LocationID = pl.LocationID
      LEFT JOIN [CMS_SodaPop].dbo.Channels c WITH ( NOLOCK ) ON ll.ChannelID = c.ChannelID
      LEFT JOIN ( SELECT  PLocationID ,
                          COUNT(DISTINCT UserID) AS [RepCount]
                  FROM    [CMS_roll].dbo.rollItems WITH ( NOLOCK )
                  WHERE   RedID = 154
                  GROUP BY OrangeLocationID
                ) PP ON PP.OrangeLocationID = pl.OrangeLocationID
      LEFT JOIN ( SELECT  OrangeLocationID ,
                          SUM(NormalizedSold) AS [NormalizedSold]
                  FROM    [Analysis].dbo.[vSodaPop_Retail_Inventory] WITH ( NOLOCK )
                  GROUP BY OrangeLocationID
                ) i ON i.OrangeLocationID = pl.OrangeLocationID
      LEFT JOIN [Analysis].dbo.[vSodaPop_Calculations] ac WITH ( NOLOCK ) ON ac.[Quarter] = CASE WHEN DATEPART(MM,
                                                                                                      [DT]) IN ( 10,
                                                                                                      11, 12 ) THEN 4
                                                                                            END
                                                                             AND ac.[Year] = DATEPART(YY, pl.DT)
  WHERE   pl.Activity = 1
      AND pl.RedID = 154
      AND pl.GroupID &lt;&gt; 444
      AND pl.[DT] &lt; GETDATE()
      AND DATEPART(YY, [DT]) &gt;= 2010
      AND ISNULL(i.NormalizedSold, 0) &gt;= 0
      AND DATEPART(year, GETDATE()) = DATEPART(year, r.Insert_Date)
  </code></pre>