Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Microsoft: "Don´t use the prefix sp_" Expand / Collapse
Author
Message
Posted Friday, November 8, 2002 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 7:18 AM
Points: 40, Visits: 7
A few days ago I visited one of Microsoft's "The Architect Blueprint" event in Sweden. Suddenly the "Architect Evangelist" said

"Do not preface your stored procedure names with sp_, because doing so reduces performance. 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."

And today I found the statement in MSDN, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp?frame=true (search for "sp_").

Can this be true? I tested this :

use master
go
create procedure sp_mysp as
select 'I am the master'
go
use mydb
go
exec mydb..sp_mysp
go
create procedure sp_mysp as
select 'I am NOT the master'
go
exec mydb..sp_mysp

Result:

---------------
I am the master

-------------------
I am NOT the master


Why should SQL Server scan the master database when it descides to executes the sp in mydb? I have logged everything in SQL Profiler and I can't find anything strange.

/ Fredrik Hellebro





Post #8041
Posted Friday, November 8, 2002 8:37 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
The sp_ behavior is "by design" and is hardcoded into SQL Server. As a result, there's no getting around it. I've written about why this would cause recompiles:

http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #46094
Posted Friday, November 8, 2002 9:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
Not sure if this is a huge problem. Could lead to some confusion and Brian has good points. In general, if you do not need a stored procedure executed from master (regardless of in which db you need it run), don't use sp_. I use spXXXXXXX for mine. Some people use usp_, mysp_, etc.

It's a personal opinion, but I avoid sp_

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #46095
Posted Monday, November 11, 2002 11:47 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 8, 2009 10:17 AM
Points: 727, Visits: 19
I use a different prefix simply to differentiate my sp's from the microsoft supplied sp's. If they all start with my special "af" prefix, they're easy to identify as mine.



Steve Phelps
SQL Server DBA
American Fidelity Group



Post #46096
Posted Monday, November 11, 2002 1:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:11 AM
Points: 3,244, Visits: 529
I use my initials on stored procedures and require that anyone else who creates stored procedures use their initials. It's an easy way to keep track of who made what.

-Bill




Post #46097
Posted Tuesday, November 12, 2002 6:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9
I use spA_ append, spD_ delete, spU_ update. I have required that submittal forms be sent in when changes are made or objects created.

Also at the top of every SP i have started comments blocks:

CREATE PROCEDURE spA_tblTest

/*
Created: 11/10/2002
Created By: AJ
Purpose(s): Append data into tblTest
--
Modified: 11/12/2002
Modified By: AJ
Purpose(s): Added additional functionality....
*/

AS

this way I can see whodunit and why.

AJ Ahrens
SQL DBA
Custom Billing Team AT&T Labs





Good Hunting!

AJ Ahrens


webmaster@kritter.net
Post #46098
Posted Tuesday, November 12, 2002 7:03 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
I do believ the point is the fact that if you create a user defined sp with sp_ in master and execute it from any DB it works right.
But if you create a sp with sp_ with the same name in a user db as the name given to the one in master then it will execute the local one and not the one in master.

Which based on SQL BOL
quote:
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.


This should not bee the case, the one in master should be executed.

However, if you create an item in your database named sp_help the one in master will be executed no matter what you do.

The key is in the next statment in BOL

quote:
If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.


In other words if the name is the same as a system defined stored procedure in master and not a user one then masters copy will override the code.

Now, if you run

sp_MS_marksystemobject sp_mysp

against the Procedure you created in the master database and then execute mydb..sp_mysp it will still run the local db copy even thou the master copy does state type is system.

Here is the trick, restart the server now and when comes back up you will find running mydb..sp_mysp will now execute the master copy instead.

Ultimately I believe when the server starts it creates a list in memory of all the system procedures in master, when you call a procedure of that name the list will trigger the master copy to be executed instead of any local copy, even if you fully qualify.

If the item is not in the list tht was create at startup then the local copy is applied. The advatange is that an system SP in master can be accessed quickly without needing to qualify.

Furthermore, if you test further you will find that any other first 3 characters as the procedure name will not work, even if you name the procedure spmysp and make a system object then reboot it will still run the local copy.

So at load the server must only load procedures prefixed sp_ which does not affect performance except at load time since it will have to compare the procedures to find those. I also believe that unless the query manager does a left 3 on the running sp everytime (which it may) then your procedure will be checked against the list everytime to make sure not in the list.

Either way it would be (IMHO) safe to assume that the performance hit is the same either way, except you don't have to worry if your object name is stepped on by something in master.

In sumation I think the search order is actually more like this.

1) Is SP_ item matching name in master list of system procedures created at time of SQL Server Start.
2) Is SP_ procedure in fully qualified path.
3) If SP is not called via the fully qualified path does SP_ exist in current database.
4) If not found in any of the previous does it exist in the master DB as a user type or as system type (newly changed to system type after SQL Server startup).

I tested under SQL 7 SP4 and SQL 2000 SP2.

Note: I choose to prefix ip_ (which is short individual procedure, picked this up from someone else), unless I am creating an SP in master for code reusability.

Edited by - antares686 on 11/12/2002 07:04:24 AM



Post #46099
Posted Tuesday, November 12, 2002 8:25 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Antares is right about how it'll go about executing. If you don't mark it as a system stored procedure, it will get around to executing the one in the local database. However, you will take a performance hit. Here's why...

SQL Server will initially look for the execution plan in the master database. This will result in a cache miss event. SQL Server will then put an exclusive [COMPILE] lock on said stored procedure. Exclusive means exclusive... only one process can use it as a time. SQL Server will then do a second, more comprehensive search to match up the stored procedure with an execution plan. It it finds it (and it should after you've run it once), it will then register an ExecContextHit and attempt to reuse the cached execution plan. Only then will it release that [COMPILE] lock.

This will happen every time the stored procedure is executed. Meaning if you've got several processes that could be calling the stored procedure, they will queue up. Remember, that even though one process has the stored procedure running and has released the [COMPILE] lock, the next process can't do anything until the first process is done. This is because the [COMPILE] lock is exclusive. As a result, you'll serialize the stored procedure where only one process can use it as a time.

HTH.

K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #46100
Posted Tuesday, November 12, 2002 8:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9
I 100% agree with the NEVER use sp_ as the first 3 characters. I use the spA, spD, spU etc... I was lucky enough to not fall into that trap. The Lead DBA I worked for kept our team up-to-date with issues like this and how to use performance tools etc....

I have known developers that have done this (used sp_) and have had to re-write because the code being fired wasn't correct and due to the performance hits.

:D

Thanks,

AJ

AJ Ahrens
SQL DBA
Custom Billing AT&T Labs




Good Hunting!

AJ Ahrens


webmaster@kritter.net
Post #46101
Posted Wednesday, November 13, 2002 10:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 7, 2014 2:47 PM
Points: 134, Visits: 186
I have 150+ DBs, and want certain procs to be used from anywhere. The only way I've found to do this is to put them in master and prefix them with "sp__" (I use two underscores to tell them apart). Is there any OTHER way to have SQL search a common "library" to find the proc? (I have 7.0 now, and 2000 soon.)




Post #46102
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse