Using sp_ as prefix for user stored procedures in SQL server causing performance impact

Question

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:

https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Answer 1

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: