A common mistake database developers make in SQL Server is naming their stored procedures with the “sp_” prefix. Organizations sometimes even adopt this as a standard convention (along with the “tbl_” prefix for tables!).
So, why is this considered a bad practice?
It’s inefficient.
The “sp_” prefix is used by SQL Server to designate internal system procedures. So, whenever SQL Server sees “sp_” at the beginning of a stored procedure, it looks in the master database first.
So, using the “sp_” prefix for a stored procedure is like giving SQL Server bad directions and then wondering what’s taking it so long.
It causes conflicts with system procedures with the same name.
If you accidentally named your stored procedure the same thing as one of those system procedures, SQL Server will execute the system procedure instead of your database procedure.
It means taking the long way around every time.
This what SQL Server is triggered to do every time an “sp_” stored procedure is called:
Stop processing in your database.
Jump over to the master database.
Scan every stored procedure.
Come back to your database.
Find and execute the procedure you were looking for in the first place.
All of those steps take time. Multiplying that time by the number of stored procedures, then multiplying that by the number of times those procedures are called per day, you can end up with significantly slower performance.
The simple solution
When you avoid the “sp_” prefix, you ensure that SQL Server immediately looks in your database. This makes the procedure faster to find and execute. What’s not to like about that?
One exception: utility procedures
If you’re creating a utility procedure, like sp_whoisactive, that you intend to run everywhere, then using the “sp_” prefix can be useful. Do so sparingly and only when creating a procedure in the master database that you want to have accessible from all other databases.
Want to work with us?
Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a call with us to get started.
