none
WITH RECOMPILE at the sp execution level RRS feed

  • Question

  • Fellow SQLers,

    Trying to understand what WITH RECOPILE does differently at the EXEC level instead of inside of inside the sp. yes, I have read a number of the blogs but did not find exactly what I was looking for - yet.

    I did a test with running an sp WITH RECOMPILE on the execution line.  Before starting, I ran dbcc freeproccache. I then ran my command of
    spmytest 789 WITH RECOMPILE  numerous times. I noticed that the plan did not store. I read that SQL does not store a query plan with RECOMPILES inside the sp but did not read where it does not store the plan at the execution line.  Are my findings correct?

    using SQL 2008 R2

    Thanks,

    MG

    Tuesday, July 17, 2018 1:25 PM

Answers

  • its specified here

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017

    it says this

    RECOMPILE Forces a new plan to be compiled, used, and discarded after the module is executed. If there is an existing query plan for the module, this plan remains in the cache.

    Use this option if the parameter you are supplying is atypical or if the data has significantly changed. This option is not used for extended stored procedures. We recommend that you use this option sparingly because it is expensive.

    Note: You can not use WITH RECOMPILE when calling a stored procedure that uses OPENDATASOURCE syntax. The WITH RECOMPILE option is ignored when a four-part object name is specified.

    Note: RECOMPILE is not supported with natively compiled, scalar user-defined functions. If you need to recompile, use sp_recompile (Transact-SQL).


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by mg101 Tuesday, July 17, 2018 1:42 PM
    Tuesday, July 17, 2018 1:32 PM

All replies

  • its specified here

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017

    it says this

    RECOMPILE Forces a new plan to be compiled, used, and discarded after the module is executed. If there is an existing query plan for the module, this plan remains in the cache.

    Use this option if the parameter you are supplying is atypical or if the data has significantly changed. This option is not used for extended stored procedures. We recommend that you use this option sparingly because it is expensive.

    Note: You can not use WITH RECOMPILE when calling a stored procedure that uses OPENDATASOURCE syntax. The WITH RECOMPILE option is ignored when a four-part object name is specified.

    Note: RECOMPILE is not supported with natively compiled, scalar user-defined functions. If you need to recompile, use sp_recompile (Transact-SQL).


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by mg101 Tuesday, July 17, 2018 1:42 PM
    Tuesday, July 17, 2018 1:32 PM
  • You can see one plan per execution  (usecount =1)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 17, 2018 1:33 PM
    Answerer
  • Thanks Visakh16

    Found it. Correlates with my findings.

    MG

    Tuesday, July 17, 2018 1:42 PM
  • HI uri,

    thanks but it does not show in db_exec_query_stats dmv as Execution Counts.

    Tuesday, July 17, 2018 1:47 PM
  •  > RECOMPILE within stored procedure means SQL Server always compiles a new plan every time the procedure is called.

    > When RECOMPILE is used while calling the procedure , then particular is executed with new compiled plan instead of re-using existing plan. This comes in handy when you don't want SP to recompile every time, instead only when certain parameters are passed 

    check this ex,

    create proc test_hunt 
    as 
    
    BEGIN 
    select count(*) from sys.objects 
    END 
    go
    
    exec test_hunt RECOMPILE
    go 
    go
     --- this gives stored exec cplan
    select *   
     FROM sys.dm_exec_cached_plans AS cp
     CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
     CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
      where   OBJECT_NAME(st.objectid,st.dbid) ='test_hunt'
    order by  cp.usecounts desc 
    go
    dbcc freeproccache
    go
    exec test_hunt with RECOMPILE
    go 
    go
    --- the plan is not saved 
    select *   
     FROM sys.dm_exec_cached_plans AS cp
     CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
     CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
      where   OBJECT_NAME(st.objectid,st.dbid) ='test_hunt'
    order by  cp.usecounts desc 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 17, 2018 1:52 PM
  • thanks Sarat,

    Yes, I understand that. Answered above. Thx. MG

    Tuesday, July 17, 2018 2:15 PM