I agree with the others, very interesting article and something that I did not know about. However, I am not quite sure that everything is fully correct in the article, or at least I am not sure what conclusions to draw from it. First off, I could not trace any SP:CacheMiss events when only the casing of the parameter(s) to procedures changed. No matter how I wrote them I never got a cache miss if that was the only change in the call to the procedure. And since only the procedure name is stored with the cache object (and used when looking for a cached plan) I can not see how it could work differently.

Furthermore, I do not think that the plans are actually recompiled, even though there is sometimes a SP:CacheMiss (when using incorrect case for procedure name). You wrote that even if a procedure is executed using incorrect casing it is still stored in the cache with the correct casing (the way it was once created using CREATE PROC). You can see this in syscacheobjects, where the sql column stores the procedure name in correct casing, so we know that the name is looked up. From this I draw the conclusion that the same thing happens when you later on call the procedure, using incorrect case. The SP:CacheMiss we are seeing is for the cached object with the incorrect casing. Since none can exist (it will always be correctly cased) we get a cache miss. This is of course good, since otherwise there would be different plans for the same procedure. Note that if the server would be using case-sensitive settings then there could be two procs with the same name, only using different casing, and they should of course have different plans.

After the cache miss SQL Server looks for a cached plan stored with the correctly cased procedure name (which it now looks up). If one is found then that plan is used. You can see this by watching the value in column UseCounts incrementing one step each time the procedure is executed, irregardless of whether or not the name is correctly cased and we are seeing an SP:CacheMiss or not. Also, in SQL Profiler, add the data column ObjectName to the output. Then you will see what the cache lookup is actually missing. Clear the cache (using DBCC FREEPROCCACHE) and then execute the procedure using incorrect casing for the proc name. In Profiler you will see two cache misses, first for the incorrectly cased name, next for the correctly cased name. Since this is the first time the proc is executed (after clearing the cache) this is the behaviour we could expect. After this there is an SP:CacheInsert event, which of copurse inserts that plan to the cache using the correctly cased name of the proc.

At least this is the conclusions I draw from experimenting a little with this, but I might be wrong here. Otherwise, in summary I think the implications of this problem is even smaller than it would be with a lot of recompiles, although I for one definitely agree that any small thing that helps performance is always a good thing. Like you said, if SQL Server is spending (any) time doing anything that it would not have needed to if we only coded correctly, then we should not be happy with ourselves.

Thanks again for the article and for teaching me something new.