March 18, 2008 at 10:29 am
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.
DBCC PROCCACHE
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 364
Thanks, Dave
March 18, 2008 at 11:52 am
2005 Plan cache is pretty radically different than 2000.
Take a look at at this blog by some of the developers from MS. It might help.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 19, 2008 at 1:59 pm
I've seen the proc cache much bigger in x32 sql2005 - I posted some rdl files you can use to look at buffer and proc cache http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
You'll still struggle with lower memory so making sure you have as few as possible other processes taking lower memory will help. You may not have enough mmeory set for the o/s, I usually leave at least 4Gb.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 19, 2008 at 2:35 pm
I'll check out the link. Thanks.
I have 2GB reserved for the OS and SQL is configured to use a maximum of 8GB. The pagefile use isn't even 1% so memory consumption looks good.
The application connects through an application server using a single service account. This account is in db_owner for all user databases (as required by the application). I was told not qualifying the stored procedure calls would create a cache miss. Is this correct?
I ran a test where I created a login id (DBA1) and added it to db_dbowner in database DBA_HOME. While sysadmin I created a stored procedure (usp_test). I then connected to SQL Server as DBA1 and ran the stored procedure without fully quallifying the name. ex: usp_test. Profiler showed CacheMiss followed by CacheInsert & CacheHit. Since that was the first execution of the proc I expected a CacheMiss event. I ran it again and received CacheMiss followed by CacheHit. I then fully qualified the proc with dbo.usp_test and received the same results. This was not what I expected to see.
(1) Fully qualifying the execution of the stored procedure should not have caused a CacheMiss event correct?
(2) Since the id DBA1 is a member of db_owner, is it necessary to fully qualify the stored procedure. I wouldn't think it would look at DBA1.usp_test, but rather dbo.usp_test since DBA1 is a member of db_owner. Is this correct?
Thanks, Dave
March 19, 2008 at 4:07 pm
The procedure cache does not benefit from AWE memory extensions. The only benefit of AWE memory is on the data cache.
And, yes, do always qualify your database objects (tables, sprocs etc.) with the db owner (schema in SQL 2005), as this results in fewer recompilations, thus greater plan re-use.
Unfortunately, I don't have any comments on your test results. They are not what I would have expected either.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 4:27 pm
A couple of other things to consider when concerned about low cache-hit ratio:
Interleaving DML and DDL statements in code. Put all your DDL statements (such as temp-table creation etc) at the top of your sprocs before the DML statements.
Not preserving the exact case of objects when calling them in code. Case matters. Make sure you refer to all objects (tables etc.) in the exact case in which they were defined.
See this link for more info:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 20, 2008 at 5:44 am
I wouldn't have expected all those cache misses (miss's?) either. What did the test procedure do? Take a look at the link posted above to see if the test proc may have been the cause.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 20, 2008 at 2:12 pm
The test proc ran SELECT * FROM TABLE_NAME. That's it.
March 21, 2008 at 6:16 am
I'm sure it has been mentioned before, but be sure you're qualifying the procedure name and using proper case on the schema, proc, and all parameters.
That said. I'm running tests and seeing something a bit odd. I get a SP:CacheMiss event firing for the following procedure, followed by a SP:CacheHit. Now what the heck is up with that?
--create the proc
CREATE PROCEDURE dbo.x as
SELECT * FROM HumanResources.Department
--exec the proc
EXEC dbo.x
I've execute the proc multiple times and I get the same miss, hit combination each time. I'm assuming the hit is an indicator that it is in fact finding the proc, but why is there also a miss?
I've done a bunch of searches on this and found this post by Linchi Shea. He doesn't supply an answer either, but are you also looking for SP:CacheHit events. I think if you get both, it's a hit? But if you only get the miss or the miss followed by the insert, it's a miss? Hard to say.
And then there was this little set of tests, that at least somewhat parallel what I'm seeing in my tests. Again, hard to say.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 21, 2008 at 7:07 am
Its not just procedure names that should be capitalized properly and prefaced with the owner/schema to optimize cached plan re-use. The tables referenced within the proc also should have that same level of attention used.
For 2005, where plans are recompiled at the statement level, insuring that consistant capitalization and SQL formatting are used will also help.
March 21, 2008 at 9:33 am
Wow, this is the first time I have heard that the case of object names would make a difference in the re-use of query plans. I would have thought that would be the case in a case-sensitive collation of sql server, but not in a case-insensitive collation.
Everything else mentioned I had heard before. The cache-miss then cache-hit phenomenon is odd as well. I have read that this would happen when not fully qualifying objects, but not when objects are qualified. I'm interested to hear the resolution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 21, 2008 at 11:58 am
+1 to the group that has never heard about capitolization mattering for cache hits for stored procedure calls. ad hoc I did know about. Very surprised about sprocs if that holds true. Anyone got an official reference (not just some random Joe blogging)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2008 at 12:00 pm
I do think I can explain the cache miss: select * from table is a trivial plan and these are not cached.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2008 at 12:46 pm
I'm just unsure at this point. I tried with a larger query, 178 lines, not insane or stupid, but surely not trivial. The first time I ran it, I got a cache miss followed by a cache insert. The second time it was a cache miss followed by a cache hit.
I'm not seeing recompiles. I'm calling it the same way each time and the name & owning schema match the database exactly in terms of case... It still looks odd.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 21, 2008 at 2:57 pm
What schema is it in and what is your default schema? Maybe SQL is checking in the default schema and then finding it in the qualified schema.
Could be a bug.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply