none
SQL Server 2008 Plan Cache maintenance RRS feed

  • Question

  • Hi,

    I have a question, please help/suggest me.

    I have a production SQL Server 2008 where there are 4 significant stored procedures running in every 15 seconds(Borwser auto refresh) from different users(100+ users connect to site from diff locations)

    Sometimes there is no data coming from these stored procedures and application is timing out 

    So we are running follwoing queries to resolve the issue in productuion server

    DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE

    Now we are planing to keep this as part of weekend maintenance activity, so please advise what are the parameters we have to check and what is best practices to clear cache?

    Thanks,

    Ali

    Sunday, August 24, 2014 6:55 AM

Answers

  • You should NEVER run these statements on a production system. These statements are intended to test things in a test environment.

    If the execution plan of these stored procedures are ineffient you have several options:

    • Specify WITH RECOMPILE in the stored procedure
    • Specify OPTION (RECOMPILE) in some sql statements of the stored procedure.
    • Execute sp_recompile to recompile the stored procedure.


    EntityLite: A Lightweight, Database First, Micro ORM




    • Edited by Jesús López Sunday, August 24, 2014 7:28 AM x
    • Marked as answer by AreefAli Tuesday, December 15, 2015 7:22 AM
    Sunday, August 24, 2014 7:22 AM
  • You can execute sp_recompile to recompile an stored procedure, instead of clear the whole plan cache. sp_recompile does not cause an stored procedure to be recompiled each time it executes. It recompiles just one time, when you execute sp_recompile. 

    If you want, you can write a SQL Server Agent Job that calls sp_recompile for each of these four stored procedures.

    Cleaning the buffer cache will not improve performance in any case, It maintains a cache of data, it has nothing to do with plan cache.

    You should investigate why these stored procedure get slow. May be, statistics are stale. You should update statistics and rebuild indexes. It also may invalidate execution plans


    EntityLite: A Lightweight, Database First, Micro ORM


    • Edited by Jesús López Sunday, August 24, 2014 8:10 AM x
    • Marked as answer by AreefAli Sunday, August 24, 2014 10:37 AM
    Sunday, August 24, 2014 8:06 AM
  • http://pratchev.blogspot.co.il/2007/08/parameter-sniffing.html

    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

    • Marked as answer by AreefAli Sunday, August 24, 2014 10:39 AM
    Sunday, August 24, 2014 8:28 AM
    Answerer

All replies

  • You should NEVER run these statements on a production system. These statements are intended to test things in a test environment.

    If the execution plan of these stored procedures are ineffient you have several options:

    • Specify WITH RECOMPILE in the stored procedure
    • Specify OPTION (RECOMPILE) in some sql statements of the stored procedure.
    • Execute sp_recompile to recompile the stored procedure.


    EntityLite: A Lightweight, Database First, Micro ORM




    • Edited by Jesús López Sunday, August 24, 2014 7:28 AM x
    • Marked as answer by AreefAli Tuesday, December 15, 2015 7:22 AM
    Sunday, August 24, 2014 7:22 AM
  • Hi Lopez,

    Thanks for your reply!!

    I can see your point, yes I can take care of them in stored procedures to recompile in every run. 

    Can I also keep anything in weekend maintence like Adhoc/StoreProc to monitor and clear buffers in a regular basic?

    Thanks,

    Ali

    Sunday, August 24, 2014 7:40 AM
  • You can execute sp_recompile to recompile an stored procedure, instead of clear the whole plan cache. sp_recompile does not cause an stored procedure to be recompiled each time it executes. It recompiles just one time, when you execute sp_recompile. 

    If you want, you can write a SQL Server Agent Job that calls sp_recompile for each of these four stored procedures.

    Cleaning the buffer cache will not improve performance in any case, It maintains a cache of data, it has nothing to do with plan cache.

    You should investigate why these stored procedure get slow. May be, statistics are stale. You should update statistics and rebuild indexes. It also may invalidate execution plans


    EntityLite: A Lightweight, Database First, Micro ORM


    • Edited by Jesús López Sunday, August 24, 2014 8:10 AM x
    • Marked as answer by AreefAli Sunday, August 24, 2014 10:37 AM
    Sunday, August 24, 2014 8:06 AM
  • http://pratchev.blogspot.co.il/2007/08/parameter-sniffing.html

    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

    • Marked as answer by AreefAli Sunday, August 24, 2014 10:39 AM
    Sunday, August 24, 2014 8:28 AM
    Answerer