locked
Can be stored procedures without cache? RRS feed

  • Question

  • Hello Guys!

    Today one procedure in my company after job for recompile, was no cache stored when I find with this select:

    SELECT [cp].[refcounts] 
    , [cp].[usecounts] 
    , [cp].[objtype] 
    , [st].[dbid] 
    , [st].[objectid] 
    , [st].[text] 
    , [qp].[query_plan] 
    FROM sys.dm_exec_cached_plans cp 
    CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st 
    CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp
    where st.objectid = OBJECT_ID('usp_xyzasdasdjkl')
    OPTION (RECOMPILE);

    Query Plan with NULL value

    This is one bug? What you think about this?

    Thanks!


    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    Tuesday, May 14, 2013 2:23 PM

Answers

  • If it is marked for recompile with sp_recompile then it wont show it in the script. But in that scenario if you re-run the stored procedure it should appear in the procedure cache.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, May 14, 2013 3:25 PM

All replies

  • Did you mean that you are not able to find a stored procedure in the cache after execution?

    Did you check whether the stored procedure has recompile specified?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, May 14, 2013 2:45 PM
  • Yes, with this query ever I can look if my procedure have cache. But, now this procedure don't have cache and have one long time to process.

    Do you have one query to check if the procedure is marked to recompile? But isn't marked to recompile in my opinion.


    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    Tuesday, May 14, 2013 2:56 PM
  • You can check the script of the stored procedure to see if it is set to recompile. else run this query in the database it will find it for you.

    select OBJECT_name(object_id),definition 
     from sys.sql_modules
     where definition like '%recompile%'

    • Edited by Ashwin Menon Tuesday, May 14, 2013 3:03 PM change
    Tuesday, May 14, 2013 3:01 PM
  • With this script I don't find my procedure marked to recompile. :S

    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    Tuesday, May 14, 2013 3:13 PM
  • If it is marked for recompile with sp_recompile then it wont show it in the script. But in that scenario if you re-run the stored procedure it should appear in the procedure cache.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, May 14, 2013 3:25 PM
  • After recompiled the procedure remains with query plan null.

    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    Tuesday, May 14, 2013 4:30 PM
  • Hi Vethor,

     Query plan might have got flushed out due to memory issue.

     Try to run the same query on any other server and see if you are seeing NULL again.

    Tuesday, May 14, 2013 7:12 PM
  • Ok, now I understand your problem. You actually have the Query in the cache, its just that the QueryPlan is showing as null.

    Try to find the plan_handle from sys.dm_exec_cached_plans and then input it for sys.dm_exec_query_plan and see if you can find the plan.

    Check the below link and see the Remarks section where it explains the reasons why the query_plan can be null.

    http://msdn.microsoft.com/en-us/library/ms189747.aspx

    hth,


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, May 15, 2013 8:11 AM
  • I don't understand your reply.

    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    Friday, May 17, 2013 7:11 PM