March 21, 2008 at 6:16 pm
TheSQLGuru (3/21/2008)
+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)?
I think you are on the mark here... 🙂
The reference below mentions that proper CASE (and exact spacing, mind you) matters for ad hoc queries and batches but does not mention CASE as a factor for stored procedures:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
I think it is worth trying out an experiment on this one...
__________________________________________________________________________________
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 24, 2008 at 9:27 am
Kimberly.Hahn (3/21/2008)
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.
For some reason the case of SP's is not considered when re-using a cached plan but ad-hoc SQL statements the case does matter. I just finished testing it.
Grant,
Here is a quote from the bottom of this article (http://support.microsoft.com/kb/263889), actually found the article on a Cross-post by the OP on sql-server-performance.com:
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.
I tested this and from a .NET app using a SQLCommand with a CommandType of StoredProcedure you only get the CacheHit. Interestingly enough if you change it to a CommandType of Text, but still use a parameter I am getting 2 CacheHits, the first with an ObjectType of 20816 - PQ which is "Prepared Queries which may be cached". When you are getting the CacheMiss in SSMS it is on an ObjectType of 20801-AQ which is "Adhoc Queries which may be cached". So SSMS is executing it as a Language Event not an RPC.
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 24, 2008 at 2:57 pm
The case sensitivity thing is a new one for me also. I still don't understand why I am getting SP:CacheMiss followed by SP:CacheHit every time I execute a proc. I verified the case is not a problem.
Create Procedure dbo.usp_wb_who
as
Begin
select login_name from user_activity
where login_name = 'domain\dmh'
End
Exec dbo.usp_wb_who
(1) The case of the proc in the Exec statement matches the case of the proc when it was created
(2) I am not running SELECT *, but retrieving a specific column
(3) The Exec statement is fully qualified with the database owner
(4) The object is owned by the default schema
(5) My login id is a member of sysadmin
So what am I missing?
Dave
March 24, 2008 at 3:25 pm
According to the article noted in my previous post, if you are running the sp from SSMS, Query Analyzer, or using .NET with a commandtype of text you will get an extra Cache Event (Miss or Hit depending on the application) due to the server having to parse and compile the language event query to find out it is an sp being called. Then you get the CacheHit for the sp. Another reason to make sure in applications using are using a command object with the correct properties.
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 25, 2008 at 5:36 am
Nice catch Jack. I just hadn't been aware of that. It's good to know because it's the kind of thing that can lead to a lot of confusion. Just to verify it (for my own piece of mind, not questioning you), I ran Profiler against one of the production servers, just looking at the cache hits & misses for a while. I used one that I knew had properly written code (we have some that don't) so that I could see your stuff in action. I can confirm that RPC calls don't have the miss and hit, but instead only have a hit. That's good news. The bad news is I saw a whole bunch of other stuff causing problems that I didn't know about before... Crud!
----------------------------------------------------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 25, 2008 at 6:15 am
Grant Fritchey (3/25/2008)
Nice catch Jack. I just hadn't been aware of that. It's good to know because it's the kind of thing that can lead to a lot of confusion. Just to verify it (for my own piece of mind, not questioning you), I ran Profiler against one of the production servers, just looking at the cache hits & misses for a while. I used one that I knew had properly written code (we have some that don't) so that I could see your stuff in action. I can confirm that RPC calls don't have the miss and hit, but instead only have a hit. That's good news. The bad news is I saw a whole bunch of other stuff causing problems that I didn't know about before... Crud!
Wish I could take credit for it. I was just testing the affects of case and found the different object types and googled it. That led me to someone else's find.
I really did not think that case would matter in a case insensitive install of SQL Server, so I never even thought about it. I typically do everything in lower case and at my last employer we used lower case and "_" for all object names so it was never an issue with in-house projects. Where I am now my main co-worker is using mixed case with "_" in object names, so now we are going to have to discuss how to write queries.
Now we as DBA's need to look at how Linq passes queries, if you allow it;).
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 27, 2008 at 11:33 am
Whilst I don't know the answer you're looking for DBADave, I can put forward an offical reference to something mentioned earlier re: schema qualifying...
'...
A query that contains schema-qualified table names...is safe for sharing by different users...
... This applies only to queries in ad hoc or prepared batches. Queries in stored procedures, triggers, or user-defined functions are unambiguous even without schema-qualification because tables referenced by a T-SQL object always resolve as if they were being referenced by the user who created the object. In other words, there is no performance or memory penalty for lack of schema-qualification of tables referenced in T-SQL objects, though you may wish to qualify even queries in your stored procedures for the sake of consistency and clarity
...'
Reference: Henderson K, et al, 2007, SQL Server 2005 Practical Troubleshooting the Database Engine, p194 (Chapter 4 - By Bart Duncan)
I haven't carried out tests on this myself, so cannot vouch for it, save that the book was editted by the great man himself (RIP) and carries a lot of weight for me. So, I don't think this would be the issue for your procs (is it another myth in development???)...
Also, not heard of the capitalisation thing either...
March 28, 2008 at 5:27 pm
I wrote an article which was never published a few years ago which covers some of this. It still holds true in sql2005, I've never found issues with capitalisation of tables and columns within a proc causing this though. http://www.grumpyolddba.co.uk/sql2000/user%20sp%20procs.htm
As far as I know this dates back to sql server 7 and possibly earlier. It's just something I've always done as far back as I can remember ( qualifying objects and proc capitalisation )
the worst effect is serialisation of the procedure which if it's in a highly concurrent environment is a real nightmare.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 28, 2008 at 8:29 pm
From http://support.microsoft.com/kb/263889:
If an owner-qualified procedure is executed with a different case than what the owner-qualified procedure was created as, the owner-qualified procedure can get a CacheMiss or request a COMPILE lock, but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations the request for a COMPILE lock can get into a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than what the procedure was created as. This is true regardless of the sort of order or collation being used on the server or on the database. The reason for this behavior is that the algorithm being used to find the procedure in cache is based on hash values (for performance reasons) which can change if the case is different.The workaround is to drop and create the procedure with the same case as it is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.
__________________________________________________________________________________
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 31, 2008 at 6:46 am
I have read that article several times over the years and missed that VERY important section every time!! What a nugget of info to remember!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2008 at 6:10 pm
It's still a mystery to me why Microsoft doesn't use the same COLLATION-based character normalization before hashing that it does before normal table lookups.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2008 at 7:19 pm
rbarryyoung (3/31/2008)
It's still a mystery to me why Microsoft doesn't use the same COLLATION-based character normalization before hashing that it does before normal table lookups.
Think about the operation that would be required for every query on this one... therefore SQL Server only does it when it needs it. If the case is standardized in coding practice, this doesn't become an issue. I know, I know, easier said than done.
BTW, in ad hoc queries, different case WILL result in a new execution plan being generated and stored, even in server configs with insensitive case configurations. Easy way to show it is to look at the procedure cache while specifying a SELECT against a table once with all lowercase and again with all uppercase.
K. Brian Kelley
@kbriankelley
March 31, 2008 at 10:16 pm
K. Brian Kelley (3/31/2008)
rbarryyoung (3/31/2008)
It's still a mystery to me why Microsoft doesn't use the same COLLATION-based character normalization before hashing that it does before normal table lookups.Think about the operation that would be required for every query on this one... therefore SQL Server only does it when it needs it.
String normalization is an extremely low overhead function. It's essentially an XLATE transform when COLLATION settings are used, which the same or less overhead than any hash function itself is. And it since it doesn't change, it only has to be done once. In fact it could be done when the module is complied, which would make the overhead zero. Also, depending on what kind of hash function they use, it is possible to pre-build the XLATE table from the COLLATION setting so that it can do the normalization and the hash calculation at the same time.
And what really gets me, is that they already do it for every other string lookup and character matching in a database, including probes of index and data caches.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 3, 2008 at 9:03 am
I go away on vacation (Disney) for a week and come back to find some good discussions. Thanks everyone for the feedback on this topic.
Any suggestions on what Events to run in Profiler to see why I'm experiencing the low Cache Hit rate?
Grumpy,
I ran your custom report for procedure cache and now need to understand how to interpret the results. If I read about sys.dm_os_memory_cache_hash_tables will this explain the columns and what numbers are considered good/bad?
I've never dug this deep into memory before so I'm not sure where to begin. In the past I've always concentrated on pagefile useage to determine a memory issue and have never seen issues with a low Hit Rate for Procedure Cache. I guess I've been lucky.
I should add that my monitoring tool this morning is reporting only 195MB for Procedure Cache, with a 76.9% Hit Rate.
Thanks, Dave
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply