Post reply

DBCC FREEPROCCACHE

  • October 31, 2012 at 2:00 am

    #393504

    Can we use in DBCC FREEPROCCACHE in production database?

    Thanks in advance

  • October 31, 2012 at 2:04 am

    #1554501

    You can.

    It's not a great idea, will cause elevated CPU usage and probably reduced performance until SQL has repopulated it's plan cache.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 31, 2012 at 2:27 am

    #1554506

    Thanks

  • October 31, 2012 at 8:31 am

    #1554635

    Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

    Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

  • November 1, 2012 at 10:54 am

    #1555108

    Question:

    If I create a new index to more effectively support a query in a stored procedure, what is the most appropriate action to take (if any action is required) to make sure that index is considered in query plan creation the next time the stored procedure is executed?

  • November 1, 2012 at 11:04 am

    #1555113

    Please post new questions in a new thread. Thank you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 1, 2012 at 11:07 am

    #1555114

    Lee Crain (11/1/2012)

    Question:

    If I create a new index to more effectively support a query in a stored procedure, what is the most appropriate action to take (if any action is required) to make sure that index is considered in query plan creation the next time the stored procedure is executed?

    Run sp_recompile on the proc after index creation would be one way.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply