locked
Recompile RRS feed

  • Question

  • Why does every time a query execute fast with option(recompile) but slow without it? As I understood once a query is run with recompile the following executions without recompile should use same plan and run fast as well. But this has not been the case.

    Also, without recompile, the plan shows that estimated no. of rows is far lesser than the actual no. of rows but with recompile the estimated no. of rows is higher than actual no. of rows by few.


    • Edited by Curendra Friday, June 5, 2020 2:19 AM
    Friday, June 5, 2020 2:02 AM

Answers

  • Following is the code.

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04'
    SELECT   SUM(CH.Amount) 
    FROM COMMISSION_HISTORY CH 
    WHERE CH.UserId = @UserId 
    AND CH.Created_Date  BETWEEN @DATE AND GETDATE()

    Parameter sniffing is not the answer here - it is rather the total lack of it.

    If you run the above without OPTION (RECOMPILE), the optimizer does not know the values of the variables, because the entire batch is compiled at once. It will therefore make blind assumption on how many rows the BETWEEN condition will it. And when it comes to the condition on the UserID, it can only work from the density. That is, if there on average are 1000 rows per user, it will assume that this condition will hit 1000 rows, even if UserID 114 only hits 12 rows.

    When you add OPTION(RECOMPILE) this turns the tables completely. Now the optimizer knows the values of the variables, and it is probably also able to get the value of getdate(). Therefore it can make a much more accurate estimation, and you chances for an accurate plan are largely improved.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Sunday, June 7, 2020 1:53 AM
    Saturday, June 6, 2020 12:33 PM
  • I tried by creating a procedure and passing the value of userid to the parameter and that worked fine because of parameter sniffing. But there are many such queries in the sps where passing values to the parameters is not feasible. 

    For such case the hints like recompile or optimize for are the solutions?

    OPTION(RECOMPILE) is a very simple fix, and therefore tempting. The downside is if the piece of code is executed very frequently (like several times a second), the compilation overhead can be problematic. On the other hand, for something that runs at most once an hour, this is not a concern.

    Rather that writing a new stored procedure, an alternative is to stick it into dynamic SQL:

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04'
    EXEC sp_executesql N'SELECT   SUM(CH.Amount) 
                         FROM dbo.COMMISSION_HISTORY CH 
                         WHERE CH.UserId = @UserId 
                         AND CH.Created_Date  BETWEEN @DATE AND GETDATE()'
                       N'@UserId int, @date date', @UIserId, @DATE

    This creates a nameless stored procedure, and the parameters will be sniffed. The downside here is that permissions can be an issue, because ownership chaining does not apply.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Sunday, June 7, 2020 12:26 PM
    Sunday, June 7, 2020 12:16 PM

All replies

  • Without knowing the code it's difficult to say, but a common cause is "parameter sniffing"
    => SQL SERVER – Parameter Sniffing Simplest Example

    => Recompile a Stored Procedure => Recommandations


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Friday, June 5, 2020 6:01 AM
    • Marked as answer by Curendra Friday, June 5, 2020 8:21 AM
    • Unmarked as answer by Curendra Sunday, June 7, 2020 1:53 AM
    Friday, June 5, 2020 5:58 AM
  • Following is the code.

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04'
    SELECT   SUM(CH.Amount) 
    FROM COMMISSION_HISTORY CH 
    WHERE CH.UserId = @UserId 
    AND CH.Created_Date  BETWEEN @DATE AND GETDATE()

    You said parameter sniffing so I tried by using value of @userid in the where clause instead of the variable and that worked fine. But I cannot do that in live db because the variable @userid is declared and its value is selected from a table.

    And too many recompiles would increase resource consumption.



    • Edited by Curendra Friday, June 5, 2020 6:56 AM
    Friday, June 5, 2020 6:41 AM
  • Hi Curendra,

    According to your code, below maybe one reason why you executed query fast with option(recompile).

    Refer to an object (table, view, stored procedure, etc.) in a statement with its fully qualified Object Name (SCHEMA), not just the name of the object itself. SQL Server may prefer to save multiple execution plans for the same statement. Using object full names makes it easier to "bootstrap" SQL Server for reuse of execution plans.

    In your situation, you could have a try to add the schema before your table. For example: FROM dbo. COMMISSION_HISTORY.

    In addition, here are some other common cases where recompilation occurs.

    l  When any object (table or view) involved in an instruction or batch has a schema change.

    l  Executed Sp_recompile.

    l  Some actions remove all execution plans from memory, forcing everyone to recompile.

    l  The previous execution plans cannot be reused when the following SET switch values change.

    ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER

    l  When statistics on a table or view changed.

    Please help check whether your situation conforms to one of the above. Thanks.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, June 5, 2020 7:18 AM
  • Can you make a stored procedure of it, and call that proc? That way, you will get parameter sniffing.

    Or, instead of OPTION(RECOMPILE) use OPTION(OPTIMIZE FOR ...). That way you get to say what values the optimizer will calculate selectivity by, without incurring the cost of recompile.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, June 5, 2020 9:03 AM
  • Assuming the Amount column in COMMISSION_HISTORY doesn't change very often and this query is executed fairly often, you could create a nonclustered index with UserId and Created_Date as keys and Amount as an included column.  Then you would not need option recompile because the optimal plan would always be the same (scan only the needed rows in the index and never go the table).  And it should make this query run as fast as possible in all cases.  Of course, it comes with a tradeoff - you have to maintain the index.  The costs of maintaining the index would need to be weighed against the savings in running this query.  You haven't told us which indexes you have on this table, but if you already have a nonclustered index on UserId and Created_Date then adding Amount as an included column would probably carry only an small additional cost (unless you are running lots of updates that change the value in the Amount column).

    But if I were you, I would probably go with the option recompile.  Yes, that would incur the cost of the recompiles.  But that cost will be small, this is a simple straight forward query and the amount of work to generate the query is minimal.  For that small cost you avoid the cost of the wrong plan which can be very expensive if the plan used is scan the entire table when it should be use an index to find only the small number of rows that are needed.  You can afford lots of recompiles if it avoids one unnecessary table scan.

    Tom

    • Proposed as answer by Naomi N Friday, June 5, 2020 5:49 PM
    Friday, June 5, 2020 4:17 PM
  • Following is the code.

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04'
    SELECT   SUM(CH.Amount) 
    FROM COMMISSION_HISTORY CH 
    WHERE CH.UserId = @UserId 
    AND CH.Created_Date  BETWEEN @DATE AND GETDATE()

    Parameter sniffing is not the answer here - it is rather the total lack of it.

    If you run the above without OPTION (RECOMPILE), the optimizer does not know the values of the variables, because the entire batch is compiled at once. It will therefore make blind assumption on how many rows the BETWEEN condition will it. And when it comes to the condition on the UserID, it can only work from the density. That is, if there on average are 1000 rows per user, it will assume that this condition will hit 1000 rows, even if UserID 114 only hits 12 rows.

    When you add OPTION(RECOMPILE) this turns the tables completely. Now the optimizer knows the values of the variables, and it is probably also able to get the value of getdate(). Therefore it can make a much more accurate estimation, and you chances for an accurate plan are largely improved.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Sunday, June 7, 2020 1:53 AM
    Saturday, June 6, 2020 12:33 PM
  • Thanks Erland.

    I tried by creating a procedure and passing the value of userid to the parameter and that worked fine because of parameter sniffing. But there are many such queries in the sps where passing values to the parameters is not feasible. 

    For such case the hints like recompile or optimize for are the solutions?



    • Edited by Curendra Sunday, June 7, 2020 10:30 AM
    Sunday, June 7, 2020 10:29 AM
  • because the entire batch is compiled at once

    Is there a solution to this in a sp?


    Sunday, June 7, 2020 10:34 AM
  • I tried by creating a procedure and passing the value of userid to the parameter and that worked fine because of parameter sniffing. But there are many such queries in the sps where passing values to the parameters is not feasible. 

    For such case the hints like recompile or optimize for are the solutions?

    OPTION(RECOMPILE) is a very simple fix, and therefore tempting. The downside is if the piece of code is executed very frequently (like several times a second), the compilation overhead can be problematic. On the other hand, for something that runs at most once an hour, this is not a concern.

    Rather that writing a new stored procedure, an alternative is to stick it into dynamic SQL:

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04'
    EXEC sp_executesql N'SELECT   SUM(CH.Amount) 
                         FROM dbo.COMMISSION_HISTORY CH 
                         WHERE CH.UserId = @UserId 
                         AND CH.Created_Date  BETWEEN @DATE AND GETDATE()'
                       N'@UserId int, @date date', @UIserId, @DATE

    This creates a nameless stored procedure, and the parameters will be sniffed. The downside here is that permissions can be an issue, because ownership chaining does not apply.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Sunday, June 7, 2020 12:26 PM
    Sunday, June 7, 2020 12:16 PM
  • Why not use OPTIMIZE FOR? Just pick some value which gives you the desired selectivity/execution plan, and you don't have to pay for compilation at each execution!

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Sunday, June 7, 2020 4:16 PM
  • Why not use OPTIMIZE FOR? Just pick some value which gives you the desired selectivity/execution plan, and you don't have to pay for compilation at each execution!

    And what would that value be?

    For the user id, there can only be a problem if there is different selectivty for different users, that is, there is a skew. If there is an even distribution, the optimizer has enough information. And if there is a skew you want different plans for different users.
    The @date is a lot worse. In Cuendras example, the date is two years back, but say that in practice @date will be a few days before the current date. How you do you express that with OPTIMIZE FOR?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 7, 2020 5:33 PM
  • D'oh. I didn't read carefully enough and that GETDATE() in the predicate makes OPTIMIZER FOR a no-option. Thanks for keeping me straight, Erland!

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, June 8, 2020 9:20 AM