title: SQL Server: Query fast, but slow from procedure
body: <p>A query runs fast:</p>
  
  <pre><code>DECLARE @SessionGUID uniqueidentifier
  SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'
  
  SELECT *
  FROM Report_Opener
  WHERE SessionGUID = @SessionGUID
  ORDER BY CurrencyTypeOrder, Rank
  </code></pre>
  
  <p><strong>subtree cost: 0.502</strong></p>
  
  <p>But putting the same SQL in a stored procedure runs slow, and with a totally different execution plan</p>
  
  <pre><code>CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
  SELECT *
  FROM Report_Opener
  WHERE SessionGUID = @SessionGUID
  ORDER BY CurrencyTypeOrder, Rank
  
  EXECUTE ViewOpener @SessionGUID
  </code></pre>
  
  <p><strong>Subtree cost: 19.2</strong></p>
  
  <p>I've run</p>
  
  <pre><code>sp_recompile ViewOpener
  </code></pre>
  
  <p>And it still runs the same (badly), and I've also changed the stored 
  procedure to</p>
  
  <pre><code>CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
  SELECT *, 'recompile please'
  FROM Report_Opener
  WHERE SessionGUID = @SessionGUID
  ORDER BY CurrencyTypeOrder, Rank
  </code></pre>
  
  <p>And back again, trying to really trick it into recompiling.</p>
  
  <p>I've dropped and recreated the stored procedure in order to get it to generate a new plan.</p>
  
  <p>I've tried forcing recompiles, <strong>and prevent parameter sniffing</strong>, by using a decoy variable:</p>
  
  <pre><code>CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
  
  DECLARE @SessionGUIDbitch uniqueidentifier
  SET @SessionGUIDbitch = @SessionGUID
  
  SELECT *
  FROM Report_Opener
  WHERE SessionGUID = @SessionGUIDbitch
  ORDER BY CurrencyTypeOrder, Rank
  </code></pre>
  
  <p>I've also tried defining the stored procedure <strong><code>WITH RECOMPILE</code></strong>:</p>
  
  <pre><code>CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier 
  WITH RECOMPILE
  AS
  SELECT *
  FROM Report_Opener
  WHERE SessionGUID = @SessionGUID
  ORDER BY CurrencyTypeOrder, Rank
  </code></pre>
  
  <p>So that it's plan is never cached, and I've tried forcing a recompile at execute:</p>
  
  <pre><code>EXECUTE ViewOpener @SessionGUID WITH RECOMPILE
  </code></pre>
  
  <p>Which didn't help.</p>
  
  <p>I've tried converting the procedure to dynamic SQL:</p>
  
  <pre><code>CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier 
  WITH RECOMPILE AS
  DECLARE @SQLString NVARCHAR(500)
  
  SET @SQLString = N'SELECT *
     FROM Report_OpenerTest
     WHERE SessionGUID = @SessionGUID
     ORDER BY CurrencyTypeOrder, Rank'
  
  EXECUTE sp_executesql @SQLString,
  N'@SessionGUID uniqueidentifier',
  @SessionGUID
  </code></pre>
  
  <p>Which didn't help.</p>
  
  <p>The entity "<code>Report_Opener</code>" is a view, which is not indexed. The view only references underlying tables. No table contains computed columns, indexed or otherwise.</p>
  
  <p>For the hell of it I tried creating the view with </p>
  
  <pre><code>SET ANSI_NULLS ON
  SET QUOTED_IDENTIFER ON
  </code></pre>
  
  <p>That didn't fix it.</p>
  
  <p>How is it that</p>
  
  <ul>
  <li>the query is fast</li>
  <li>moving the query to a view, and selecting from the view is fast</li>
  <li>selecting from the view from a stored procedure is 40x slower?</li>
  </ul>
  
  <p>I tried moving the definition of the view directly into the stored procedure (violating 3 business rules, and breaking an important encapsulation), and that makes it only about 6x slower.</p>
  
  <p>Why is the stored procedure version so slow? What can possibly account for SQL Server running ad-hoc SQL faster than a different kind of ad-hoc SQL? </p>
  
  <p>I'd really rather not</p>
  
  <ul>
  <li>embed the SQL in code</li>
  <li><p>change the code at all</p>
  
  <pre><code>Microsoft SQL Server  2000 - 8.00.2050 (Intel X86)
  Mar  7 2008 21:29:56
  Copyright (c) 1988-2003 Microsoft Corporation
  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
  </code></pre></li>
  </ul>
  
  <p>But what can account for SQL Server being unable to run as fast as SQL Sever running a query, if not parameter sniffing.</p>
  
  <hr>
  
  <p>My next attempt will be to have <code>StoredProcedureA</code> call <code>StoredProcedureB</code> call <code>StoredProcedureC</code> call <code>StoredProcedureD</code> to query the view.</p>
  
  <p>And failing that, have the stored procedure call a stored procedure, call a UDF, call a UDF, call a stored procedure, call a UDF to query the view.</p>
  
  <hr>
  
  <p>To sum up, the following run fast from QA, but slow when put into a stored procedure:</p>
  
  <p>The original:</p>
  
  <pre><code>--Runs fine outside of a stored procedure
  SELECT *
  FROM Report_OpenerTest
  WHERE SessionGUID = @SessionGUID
  ORDER BY CurrencyTypeOrder, Rank
  </code></pre>
  
  <p><code>sp_executesql</code>:</p>
  
  <pre><code>--Runs fine outside of a stored procedure
  DECLARE @SQLString NVARCHAR(500)
  SET @SQLString = N'SELECT *
  FROM Report_OpenerTest
  WHERE SessionGUID = @SessionGUID
  ORDER BY CurrencyTypeOrder, Rank'
  
  EXECUTE sp_executesql @SQLString,
          N'@SessionGUID uniqueidentifier',
          @SessionGUID
  </code></pre>
  
  <p><code>EXEC(@sql)</code>:</p>
  
  <pre><code>--Runs fine outside of a stored procedure
  DECLARE @sql NVARCHAR(500)
  SET @sql = N'SELECT *
  FROM Report_OpenerTest
  WHERE SessionGUID = '''+CAST(@SessionGUID AS varchar(50))+'''
  ORDER BY CurrencyTypeOrder, Rank'
  
  EXEC(@sql)
  </code></pre>
  
  <hr>
  
  <p>Execution Plans</p>
  
  <p>The <strong>good</strong> plan:</p>
  
  <pre><code>      |--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC))
             |--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[CurrencyType]
                  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID]))
                       |--Filter(WHERE:((([Currencies].[IsActive]&lt;&gt;0 AND [Currencies].[OnOpener]&lt;&gt;0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currencies].
                       |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH)
                       |         |--Nested Loops(Left Outer Join)
                       |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1016]), OBJECT:([GrobManagementSystemLive].[dbo].[Windows]))
                       |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Openers].[WindowGUID]))
                       |         |    |         |--Bookmark Lookup(BOOKMARK:([Bmk1014]), OBJECT:([GrobManagementSystemLive].[dbo].[Openers]))
                       |         |    |         |    |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_SessionGUID]), SEEK:([Openers].[SessionGUID]=[@SessionGUID]) ORDERED FORWARD)
                       |         |    |         |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows]), SEEK:([Windows].[WindowGUID]=[Openers].[WindowGUID]) ORDERED FORWARD)
                       |         |    |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
                       |         |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Currenc
                       |--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]))
                            |--Stream Aggregate(DEFINE:([Expr1006]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='ctCanadianCoin') OR [
                                 |--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH)
                                      |--Nested Loops(Inner Join)
                                      |    |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
                                      |    |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
                                      |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
  </code></pre>
  
  <p>The <strong>bad</strong> plan</p>
  
  <pre><code>       |--Sort(ORDER BY:([Expr1020] ASC, [Currencies].[Rank] ASC))
              |--Compute Scalar(DEFINE:([Expr1020]=If ([Currencies].[CurrencyType]='ctCanadianCash') then 1 else If ([Currencies].[CurrencyType]='ctMiscellaneous') then 2 else If ([Currencies].[CurrencyType]='ctTokens') then 3 else If ([Currencies].[Currency
                   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Openers].[OpenerGUID]))
                        |--Filter(WHERE:((([Currencies].[IsActive]&lt;&gt;0 AND [Currencies].[OnOpener]&lt;&gt;0) AND ((((((([Currencies].[CurrencyType]='ctUSCoin' OR [Currencies].[CurrencyType]='ctMiscellaneousUS') OR [Currencies].[CurrencyType]='ctUSCash') OR [Currenc
                        |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Currencies].[CurrencyGUID], [Openers].[OpenerGUID]) WITH PREFETCH)
                        |         |--Filter(WHERE:([Openers].[SessionGUID]=[@SessionGUID]))
                        |         |    |--Concatenation
                        |         |         |--Nested Loops(Left Outer Join)
                        |         |         |    |--Table Spool
                        |         |         |    |    |--Hash Match(Inner Join, HASH:([Windows].[WindowGUID])=([Openers].[WindowGUID]), RESIDUAL:([Windows].[WindowGUID]=[Openers].[WindowGUID]))
                        |         |         |    |         |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Windows].[IX_Windows_CageGUID]))
                        |         |         |    |         |--Table Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Openers]))
                        |         |         |    |--Table Spool
                        |         |         |         |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
                        |         |         |--Compute Scalar(DEFINE:([Openers].[OpenerGUID]=NULL, [Openers].[SessionGUID]=NULL, [Windows].[UseChipDenominations]=NULL))
                        |         |              |--Nested Loops(Left Anti Semi Join)
                        |         |                   |--Clustered Index Scan(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[IX_Currencies_CurrencyType]))
                        |         |                   |--Row Count Spool
                        |         |                        |--Table Spool
                        |         |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID] AND [OpenerDetails].[CurrencyGUID]=[Cu
                        |--Hash Match(Cache, HASH:([Openers].[OpenerGUID]), RESIDUAL:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]))
                             |--Stream Aggregate(DEFINE:([Expr1006]=SUM([partialagg1034]), [Expr1007]=SUM([partialagg1035]), [Expr1008]=SUM([partialagg1036]), [Expr1009]=SUM([partialagg1037]), [Expr1010]=SUM([partialagg1038]), [Expr1011]=SUM([partialagg1039]
                                  |--Nested Loops(Inner Join)
                                       |--Stream Aggregate(DEFINE:([partialagg1034]=SUM(If (((([Currencies].[CurrencyType]='ctMiscellaneous' OR [Currencies].[CurrencyType]='ctTokens') OR [Currencies].[CurrencyType]='ctChips') OR [Currencies].[CurrencyType]='
                                       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([OpenerDetails].[CurrencyGUID]) WITH PREFETCH)
                                       |         |--Clustered Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[OpenerDetails].[IX_OpenerDetails_OpenerGUIDCurrencyGUID]), SEEK:([OpenerDetails].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
                                       |         |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Currencies].[PK_Currencies_CurrencyGUID]), SEEK:([Currencies].[CurrencyGUID]=[OpenerDetails].[CurrencyGUID]) ORDERED FORWARD)
                                       |--Index Seek(OBJECT:([GrobManagementSystemLive].[dbo].[Openers].[IX_Openers_OneOpenerPerSession]), SEEK:([Openers].[OpenerGUID]=[Openers].[OpenerGUID]) ORDERED FORWARD)
  </code></pre>
  
  <p>The bad-one is eager spooling 6 million rows; the other one isn't.</p>
  
  <blockquote>
    <p><strong>Note:</strong> This isn't a question about tuning a query. I have a query that runs lightning fast. I just want SQL Server to run fast from a stored procedure.</p>
  </blockquote>