We are using SQL Server 2014 SP2 CU4. What is the performance impact of having the user defined stored procedures prefixed with sp_ instead of usp_? How can it be quantified, since we are unable to measure the impact using the sql profiling tools.
I read on this link, that it does have an impact:
The sp_ prefix is reserved for system stored procedures. It should not be used for user stored procedures. Even though you have a local stored proc with sp_ prefix, SQL Server will check master database first.
This has been covered in depth by Aaron Bertrand here: Is the sp_ prefix still a no-no?
Quote from article on impact:
The performance issue comes from the fact that master might be checked for an equivalent stored procedure, depending on whether there is a local version of the procedure, and whether there is in fact an equivalent object in master. This can lead to extra metadata overhead as well as an additional SP:CacheMiss event.
He also did some tests, below are the results:
