Monday, September 21, 2009

Name begins with sp_

If your stored procedures names begin with the sp_ prefix and is not in the master database, you will see SP:CacheMiss before the cache hit for each execution even if the stored procedure call is owner qualified. The reason for this is that the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure. System stored procedures have different name resolution rules. With system stored procedures, SQL Server will look first in the master database if the call is not database qualified. Then will look in the current database. To avoid this additional work, do not use stored procedure names that begin with sp_

Few suggestions for stored procedure naming;

• You can use bsp_ prefix to denote business stored procedure
• You can use rsp_ prefix to denote report stored procedures
• You can use csp_ prefix to denote basic CRUD stored procedures
• You can use dba_ prefix to denote DBA own stored procedures

No comments:

Post a Comment

How to interpret Disk Latency

I was analyzing IO stats in one of our SQL Servers and noticed that IO latency (Read / Write) are very high. As a rule of thumb, we know tha...