Here are some articles that describes the need to reference the owner (schema) of SQL Server stored procedure when called by applications. Also, making sure that the calls are sensitive to the case of the schema.ProcName.
- Reducing blocking by compile locks
- Reducing Stored Procedure recompiles
- Algorithm for Auto-Update stats and recompile determination)
Following these suggestion can reduce CPU activity, needless recompiles, and prevent extra procedure cache examinations, which reduces the profiler SP:CacheMiss activity and subsequent contention.
The first article is where the infamous Note is that describes calling procedures without adhering to case-sensitive names. Here is the excerpt (bold added for emphasis):
Note 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.