SQL Server Performance

Procedure Cache Hit Ratio is Low - Need Advice

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DBADave, Mar 18, 2008.

  1. DBADave New Member

    I have a 32 bit SQL 2005 EE clustered installation with 10GB of physical memory and AWE enabled. Our monitoring tool, Spotlight, is reporting the Procedure Cache to be 384MB and a Hit Rate of 75% on a fairly regular basis. Sometimes the Procedure Cache increases to 495MB and a Hit Rate of 82%.
    (1) With 2005 can the Procedure Cache be increased?
    (2) What is the max size of Procedure Cache?
    (3) How do I increase the Hit Rate to a higher percentage?
    I do not encounter the issue on any other SQL Server installation, however this is our only cluster.
    num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
    64889 1135 1135 2896 364 364Thanks, Dave
  2. Adriaan New Member

    Applications are perhaps using ad-hoc queries rather than stored procedures?
  3. DBADave New Member

    If that's the case I wouldn't expect that to impact procedure cache. Wouldn't ad-hocs hit buffer cache? This particular system contains over 3,000 stored procedures.
  4. Adriaan New Member

    Strictly speaking, the cache is hit when a literal statement issued by the client is matched by a literal statement in the cache. If your server is getting hit by lots of ad-hocs that spell out specific filter values, then they weigh down on the percentage.
    If you use sp_ExecuteSQL with proper parameters for ad-hocs, then you increase your chances of hitting the cache even with an ad-hoc.
    The number of sprocs defined in the database is irrelevant to the percentage - it's whether they're actually being called.
  5. SQLDBcontrol New Member

    The procedure cache is used to store execution plans from SQL statements. This includes ad-hoc SQL statements as well as executions of stored procedures. The name "procedure cache" is a bit misleading because it makes you think it has something to with stored procedures.
    One thing that can affect the procedure cache hit ratio is whether you're fully qualifying object names or not.
    For example, say I'm logged onto the SQL Server with a login my_user (who isn't an sa) and say I run the following SQL Statement.
    select * from my_table.
    SQL Server will initially attempt to find an execution plan for the table my_user.my_table in the cache. Upon not finding it, it will result in a miss rather than a hit.
    However, if you fully qualify it with dbo (i.e. select * from dbo.my_table) that will result in a cache hit.
    Same with stored procedures. Always qualify it:
    exec dbo.my_procedure is better than exec my_procedure.
  6. DBADave New Member

    I have two questions. If the login id is a member of db_owner in the user database and the command is not fully qualified, will SQL Server know to look first at dbo.objectname instead of loginid.objectname?
    I just ran a test where, as sysadmin, I created a login id called DBA1. I added DBA1 to db_owner in database DBA_HOME. I then created a stored procedure usp_test and had the procedure run a select from a table. Loggin in as DBA1 I executed usp_test without fully qualifying the name. Profiler reported CacheMiss, CacheInsert. Since this was the first execution of the proc I expected to see CacheMiss. I then ran the proc a second time (no fully qualified) and saw CacheMiss, CacheHit. I thought this showed me that despite DBA1 being a member of db_owner, SQL Server looked for DBA1.usp_test and not dbo.usp_test. I then ran dbo.usp_test and unexpectedly saw CacheMiss, CacheHit. Why did I see another CacheMiss?
    Thanks, Dave
  7. SQLDBcontrol New Member

    This is interesting. Firstly, adding a user to the db_owner role doesn't make that user a dbo user. dbo users are special users that are only applied logins with syadmin privileges.
    That said, this doesn't explain what you're seeing. I logged on using an sa account and run execute on a similar test procedure and I saw the same thing, CacheMiss followed by a CacheHit. Looking at the CacheMiss a lot more closely I noticed that the Object Type was 20801 - AQ, which refers to an ad-hoc query cache miss. The subsequent CacheHit was 8272 - P, which refers to the hit on the procedure. I'm not quite sure what's going on here but it seems that running the statement "exec usp_test" is considered an ad-hoc query (which may or may not be cached, according to BOL).
    Interestingly, if I give my user select permissions on a table and I then do select * from user_table this records a CacheInsert. Subsequently, the same select statement records a CacheHit. If I then do select * from dbo.user_table this results in a new CacheInsert.
    Another interesting note is that I have a .NET application running in the background that executes stored procs against the database I was testing this on, all of the stored procedure executions recorded a CacheHit (i.e. there was no preceding CacheMiss associated with the "20801 - AQ" object type). Maybe, to do this test properly we should not be running stored procs through SSMS - maybe we should do it from a remote application?
    Here's a kb article, which refers to the problem (even though it refers to SQL 7 & 2000, it does apply to SQL 2005 as well):
  8. SQLDBcontrol New Member

    It looks like I was along the right lines when I suggested we should be testing from a remote application. Just spotted this note at the bottom of the article:
    Note If you try to execute a stored procedure as a Language Event instead of as an RPC, then SQL must parse and compile the language event query, figure out that the query is trying to execute the particular procedure and then try to find a plan in cache for that procedure. To avoid this situation where SQL must parse and compile the language event, make sure that the query is sent to SQL as an RPC.

Share This Page