Just thought that I would share a little something that I heard at last weeks SQL DevCon 2005 in London.  They did say that it wasn't documented yet but I think it’s a fairly useful snippet of information.

As I’m sure that most SQL DBAs already know, when you execute a stored procedure without specifying the 2 part name <owner>.<StoredProcedure> that it will first check the procedure cache to see if a plan exists for the stored procedure and will specify the owner as the current login being used <login>.<StoredProcedue>.

So if you login to SQL Server with an account called User and enter EXEC <StoredProcedure> SQL Server automatically checks the procedure cache to see if there’s a plan available for User.<StoredProcedure>. If it doesn’t find one (a cache miss) it then checks to see if one for dbo.<StoredProcedure> exists and uses that one if it’s available.

Nothing new there I hear you say, that’s the way it’d meant to work because you might have the objects with the same name but different owners.

If you again login as User but this time enter Exec <dbo>.<StoredProcedure> SQL Server will check it’s cache and if a plan exists it will attempt to reuse it, in other words a cache hit will be generated (if it doesn’t find an entry then it will insert one for <dbo>.<StoredProcedure> for future use, this inserted entry is for dbo

rather than User).

As I have already said, most DBAs already know this (hopefully so do most developers) and to reduce the resources utilized by SQL Server they qualify all stored procedures with their 2 part name. If you’re one of the people not doing this by default then you should really consider going back and making the changes to existing systems (albeit carefully as you don’t want to break anything on a production server).

However, something new that came to light last week is that the SQL Server procedure cache is actually case sensitive (regardless of the sort order setting you configured during setup), this means that if you don't specify the Stored Procedure and any additional parameters being passed to it in exactly the same case as they are laid out in the create procedure statement then it will always generate a cache miss.

Consider the following simple procedure

CREATE PROCEDURE dbo.TestProcedure @NameOfUser varchar(20)

AS
SELECT *
 FROM dbo.UserTable
 WHERE name = @NameOfUser

The following executions will all generate cache misses (even though it may insert entries into the cache for them)

Exec TestProcedure  @NameOfUser = 'Mike'

Exec dbo.testprocedure  @NameOfUser = 'Mike'
Exec dbo.TestProcedure  @nameofuser = 'Mike'
Exec dbo.TestProcedure  @Nameofuser = 'Mike'

Only an execution that specifies the correct owner and also has the correct case for the procedure name and parameters being passed will generate a cache hit (assuming that the plan exists already)

Exec dbo.TestProcedure @NameOfUser = 'Mike'

The important thing to bare in mind with this is that even if one of the

first procedures are cached it will exist with the correctly

specified owner and case, meaning a further call to the same

procedure will still result in a cache miss (SQL doing extra work for

nothing which is bad).

You might think that this isn’t very important in the grand scheme of things and you might well be right, however, if you have production systems with thousands, tens of thousands or even millions of stored procedure calls being made each day how much extra work do you think that SQL Server is doing searching it’s cache for something that the chances are it will never find, and how much resource is this taking from other areas that would benefit from it more?

To check the number of cache hits and misses on your system you will need to use SQL Profiler and follow these steps.

1. Launch SQL Profiler
2. Create a New Trace using a BLANK template
3. Click on the Events tab and select the following event classes

• SP:CacheHit
• SP:CacheMiss

4. Run the trace.

I would suggest leaving the trace running for around 5 minutes and then saving the results to a trace table which can be interrogated using the following SQL script.

select eventclass, count(*) as 'total'

 from
<Trace_Table_Name>
 where eventclass = 34 -- miss
 or eventclass = 38 -- hit
 group by  eventclass

If you are seeing a high proportion of misses as opposed to hits then you should consider going back and checking that all objects are being called with the correct owner and also that they are being passed in the same case as they were originally created.

This may or may not be a huge impact in performance for your server, since the time to recompile most queries is very short and unless your server is heavily loaded, you would never notice this. But it can help to ensure that you are as efficient as possible with a very small change. Include case in your coding standards and get all developers and DBAs to use the same case. This is one less thing that you have to worry about slowing your system down.