SQL Server Performance

Removing objects from cache selectively

Discussion in 'Performance Tuning for DBAs' started by negmat, Nov 20, 2006.

  1. negmat New Member

    Hello everyone,

    I have the following question:

    I am aware that the objects currently in the cache can be viewed by issuing the following command:

    SELECT *
    FROM master.dbo.Syscacheobjects

    Further, cache contents can be cleared by issuing the following command:


    However, I know that clearing cache using this command is not advisable, especially in production environment, as everything is cleared up (which would adversely affect performance of other stored procedures).

    Is there a way to selectively clear things up? This selective clear up is always necessary when I am trying to find the true stored procedure performance.

    I know that with tables we can use something called 'UNPINTABLE'. Is there something similar for stored procedures?

    I tried the following command:

    DELETE FROM master.dbo.Syscacheobjects
    WHERE SQL = 'My_SP_Name'

    but got the following error:

    Msg 259, Level 16, State 2, Line 1
    Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

    Is there another way of clearing specific data (stored procedure execution plans) from cache?

    And if not, how exactly do I enable updates to system catalogs and what are the dangers associated with doing so?

    Thanks a lot
  2. MohammedU New Member

    Best way to handle this is recompile the procedure....

    It is not advisable to delete the data from system tables... Any way if you want to do it you have to run ...

    sp_configure 'allow updates', 1
    reconfigure WITH OVERRIDE
    Run your delete statement...
    sp_configure 'allow updates', 0
    reconfigure WITH OVERRIDE
  3. FrankKalis Moderator

    Btw...<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT OBJECTPROPERTY(OBJECT_ID('Syscacheobjects'),'TableIsFake')<br /> <br />----------- <br />1<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />SysCacheObjects is no true base table. It's a memory structure and materializes only when it is queries. So, I guess, you can't delete rows from that "table". There is however an, I think undocumented DBCC command: DBCC FLUSHPROCINDB(&lt;your db id&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> that will clear the cache only for the given dbid. But with all things undocumented, think carefully before using this in production or against a production server.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  4. negmat New Member

    Thanks guys - the comments are very helpful.

    I have a few follow up questions now:

    1. I know that I could add WITH RECOMPILE to the stored procedure definition during testing (to ensure that it never even gets to the cache). But what if its execution plan is already there - Is there a way to manually RECOMPILE the stored procedure (i.e. in order to remove it from the cache)?

    2. Out of pure curiosity, where exactly is the execution plan stored if one wants to look at it?

    Thanks a lot
  5. MohammedU New Member

    Thanks Frank, I totally forgot that syscacheobject is also Pseudo-table.

    1. The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

    2. It is stores in procedure cache(memory).

    Read the following article for more info...

  6. negmat New Member


    sp_recompile is exactly what I was looking for. I learned that this command is also useful when an index is added to a table to ensure that the associated execution plans (i.e. of stored procedures using this table) get re-created, ensuring optimality (under the assumption that addition of an index was a wise decision).

    As for the 2nd question, however, I did not find the information in the article specified above. My explanation was probably very not clear. I'd like to see the execution plan itself, as I would if I ran the query in the Query Analyzer, viewing the text output of the execution plan. I'm thinking that this plan must be stored somewhere, in some system table (And this is out of pure curiousity).

    Thanks a lot

  7. MohammedU New Member

    You can't see the exact plan details in any where in any system table because it stores in memory...

  8. FrankKalis Moderator

    For the actual query plan, you can use Profiler with the Performance<img src='/community/emoticons/emotion-7.gif' alt=':S' />how Plan Text event.<br />The execution plan itself is placed in cache after it has been created. So, Mohammed is correct. There is no system table storing this information. It would take too long retrieving the plan from anywhere else but the cache.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. negmat New Member

    "It would take too long retrieving the plan from anywhere else but the cache"

    Does the above statement imply that the plan is stored in cache? If so, is there a way to retrieve it from cache?

    Thanks a lot
  10. FrankKalis Moderator

    Yes, it is stored in cache and you can use the Profiler event I've mentioned to retrieve it. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  11. negmat New Member


    Thank you!
  12. MohammedU New Member

  13. negmat New Member

    Thanks guys.

    And for those reading this thread in the future, and possibly wondering where in Profiler this could be seen:

    When creating a Profiler trace, choose "Performance" Event in the "Event Selection" tab and then choose Execution Plan.
  14. FrankKalis Moderator

    Thanks for this hint! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. satya Moderator

    Just to add on RECOMPILE suggestions above, don't forget to add locking hints. Otherwise if the data volume is increasing day to day then you might have problems in accessing the data frequently.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  16. negmat New Member

    What exactly are the locking hints?

    Where are the locking hints added?

    Thanks a lot
  17. FrankKalis Moderator

    That is good explained in BOL. Check it out there. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page