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

Bill Tür stands with Ukraine · Accepted Answer · 2022-02-21 10:42:14Z

10

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:

edited Feb 21, 2022 at 10:42

Bill Tür stands with Ukraine

3,54730 gold badges42 silver badges52 bronze badges

answered Mar 7, 2017 at 5:33

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related