Cache miss

Question

Cache miss

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
November 6, 2002 at 11:06 am

#63965

SP:CacheMiss
- is there any difference if a stored procedure starts with sp_ or SP_
will both of these register a Cache Miss?
(For 7.0 and 2000 databases?)

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

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

I Don't think it makes a difference in a case insensative environment, but I am not sure about a case sensative environment.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 2

so in a case-insensitive environment both should register a cache miss hey.

Is this the same in 7.0 and 2000?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

I have been looking at the cache (syscahceobjects) and it is sometimes stored case-sensitive. I think this is with adhoc SQL, but not stored procedures.

Remember also that the first call will always result in a miss.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

This should be the same in both SQL Server 7 and 2000. It's because of the way SQL Server looks for stored procedures that start with sp_... first in the master, then the 3 part naming scheme (if you've used it), then the current database.

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
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 5

It is always better for performance to use the 3 part name (fully qualified name) even when not prefixed with sp... . Better to not use sp unless you have it in the master DB for a specific reason.

Edited by - antares686 on 11/06/2002 4:48:40 PM

Edited by - antares686 on 11/06/2002 6:57:52 PM

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 6

What is a "3 part name"?

Also, what is the order of search that SQL Server does for stored procedures?

(For sp_ and all other naming conventions.)

Antares686 SSC Guru Points: 125444 More actions · Answer 7

First I edited my last statement as it only applies to sp_ items and does not matter it you use the 3 part name. When prefixed with sp_ it will check the master db for existance then the local, if same name in master the local will never execute as masters will take precedence.

dbname.ownername.object

3 part name (aka fully qualified name).

From BOL

quote:
System Stored Procedures
Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.
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.
Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Edited by - antares686 on 11/06/2002 7:12:31 PM

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

3-part naming convention:

Database.owner.object

Such as Northwind.dbo.Customers

If you don't specify an owner or a database plus owner, it looks in the current database first for an object owned by the user and failing that, an object owned by dbo. As a result, if you follow the rule of having all objects owned by dbo, refer to the objects as dbo.object.

sp_ is checked for first in master, then based on a naming convention such as the 2-part (owner.object) or 3-part (database.owner.object), then finally the local database with ownership by dbo. The 3-part will come into play before local if a stored procedure doesn't exist in the master database with the same object name.

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
@kbriankelley