User Stored Procedure Starting Name with sp_

Question

Post reply

User Stored Procedure Starting Name with sp_

Paresh Randeria

Ten Centuries

Points: 1150
More actions
August 14, 2008 at 6:39 am

#192481

This is regarding Stored Procedure name sp_ in the User Database.
I understand that it is advisable to keep User Stored Procedure name not starting with sp_. It will differentiate User Stored Procedure with System Procedure.
Regarding Performance:
Let me clarify one argument I read on various web site regarding User Stored Procedure starting with name with sp_.
"When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name."
This is not TRUE. It will always check on the Current USE database first and then on Master Database.
My Question: I would like to know:
Is there any performance Impact if we keep User Stored Procedure Name starting with name sp_.
IF YES THEN What is the Performance Impact if we keep Stored Procedure name sp_.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

I don't think there is a performance impact, just bad practice.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Actually, the thing about sp_ causing SQL to look in Master first, is true, per Microsoft. If you have some source that knows more about the internal workings of SQL Server than the people who build it, please let me know about them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Paresh Randeria (8/14/2008)
"When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name."
This is not TRUE. It will always check on the Current USE database first and then on Master Database.

It is true, and it's very easy to demonstrate.

Run the following in any user database

CREATE PROCEDURE sp_who AS

select 'Version in user database' AS WhatWasRun

GO

EXEC sp_who

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paresh Randeria Ten Centuries Points: 1150 More actions · Answer 4

Gail,

Try Following:

USE MASTER

GO

CREATE PROCEDURE sp_ABC AS

print 'IS FROM Master DB'

GO

USE pubs

GO

CREATE PROCEDURE sp_ABC AS

print 'IS FROM Pubs DB'

GO

USE master

go

EXEC sp_ABC

go

USE pubs

go

EXEC sp_ABC

go

See the Output running from pubs database. My question is regarding User Stored Procedure starting name sp_

It is: 'IS FROM Pubs DB'

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

You created it in the wrong Master schema. It has to be in the SYS schema.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ok, clarification time. The first check going to Master was true for 2000 and below. In 2005, it's a little more complex, but the same general idea.

When you execute a stored proc with the prefix sp_, SQL first goes to the system resource database (database ID 32767) and checks to see if there is a system stored proc with that name (in the sys schema, may be necessary to be marked as a system object too). If it does not find a proc matching that, then it goes to the user DB and looks for the proc in the user's default schema.

It's not hard to demonstrate. Create your example proc in pubs, then run profiler. Capture the Lock:acquired event. Make sure you log the database id and object id columns. Start the trace then go back to management studio and run your proc.

I'll attach a screen shot of the profiler output on my machine for anyone interested. Note the two highlighted rows.

Edit: That's why I use sp_who to demonstrate that the sp_ prefix causes a lookup to another DB first. Because it's already in the sys schema in the resource DB and is marked as a system object.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paresh Randeria Ten Centuries Points: 1150 More actions · Answer 7

RBarryYoung and Gail,

I got it 100 percent..... It's clear now.....:hehe:

Thanks