SQL Server Performance Forum – Threads Archive

dbo. prefix within stored procedure

Perhaps I am being a bit dense as I try to search for this answer in the forums. In SQL 2000 the best practice is to prefix stored procedures with ‘dbo.’ for the most efficient performance. The question that was posed to me by developers is ‘is this best practice for objects (tables, views, stored procedures) used within that stored procedure’. We don’t use ad-hoc SQL from applications… 100% stored procedures. The login used by the application is something like ‘webuser’ and has execute privs on the dbo.p_storedprocedure. ‘webuser’ is not part of DBO. ‘webuser’ does not own any objects… everything is created using another account that is dbo. But I can’t think of a way of proving adding a ‘dbo.’ to prefix tables, views, etc. within the stored procedure helps performance and I don’t want to make false statements that it does. Also… is the use of dbo. going to hurt us when migrating to SQL 2005? We need the performance in a high end SQL environment now so I definitely am keeping it as a best practice to prefix all stored procedures with it… but I wanted to clarify if it should be a best practice at this time for other database objects such as tables, views, etc. Is there a concrete answer to this so I can say with authority that ‘this is the best practice’? Thanks,

Afaik, dbo prefix helps during compilation, so I guess when sp execution plan is cached, it doesn’t matter any more. I don’t know about 2005, I haven’t started learning it yet. I’ve heard there is partitial sp compilation in 2005, however I am not quite sure. If that’s true dbo in front on table or view names may have minor performance impact.

AFAIK the rule is that when there’s no owner prefix for the object, then SQL Server will first check if there is an object of that name of which the current login is the owner. If such an object does not exist, then it checks for an object of that name owned by dbo. If there is still no object, then the call fails with an error. Strictly speaking, execution plans cannot be re-used if the owner is not mentioned, because SQL Server cannot make assumptions about which object it will have to use, since it may depend on the login. Don’t forget that a database can contain different objects with the exact same name but with different owners. Anyway, just make it a standard that the owner name is referenced – well, except of course when the login must be implied as the owner (otherwise you’d have to have separate code for each owner and his objects).

Adriaan, the question was about db objects inside stored procedure. When stored procedure is not found in the cache new execution plan is generated and in that case owner resolution affects compile time and cpu. When stored procedure’s execution plan is already in the cache then only stored procedure call has to contain owner for cached plan to be reused.

If the cached plan doesn’t have the object owner listed, then does SQL Server use the plan? Would seem like the wrong thing to do: for instance two tables a.tbl1 and b.tbl1 – different data, different statistics, so may require different execution plans.

And what if the tables have different indexes? The plan might be unusable!

… but the same stored procedure is called. The object owner will not change between two execution unless stored procedure is changed. If stored procedure is changed execution plan would be removed from the cache anyway. That means stored procedure execution plan in cache will be reused if connection properties are the same and stored procedure name in calling code includes stored procedure owner.

I was talking about different logins a and b, where one login accesses a.tbl1 when the procedure refers to tbl1, and the other accesses b.tbl1.

Different logins will not use the same execution plan. If I remember well there will be two execution plans in the cache, one for each login. However, it is not the situation here. All the time it is the same login – ‘webuser’.

Yes, I agree that you better use the dbo prefix since the SQL check the cache if there is an existing plan (that is for the same object name under the same user context). Without the user context on the object, SQL has to recreate the execution plan by resolving the permission. That alone, resolving permission, is an overhead. May the Almighty God bless us all!
www.empoweredinformation.com

Mirko, I am curious – do you have a reference for execution plans being login-specific?

Does prefixing the owner apply to other objects as well? Madhivanan Failing to plan is Planning to fail

Adriaan, here is the link:http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586

quote:Best Practice
It is best to owner qualify stored procedure names when you execute a procedure. This allows for better clarity and easier reuse of the existing execution plan by the current user. For example, if a user who is not the database owner (dbo) executes a dbo-owned stored procedure (called myProc in this example) in the pubs database, use the following statement: exec dbo.myProc Instead of this: exec myProc This technique eliminates confusion about other possible versions of the procedure by different owners from a coding and maintenance standpoint, and also allows SQL Server to access the execution plan for the specific procedure more directly. By not qualifying the owner name, SQL Server enters the compile code and acquires a COMPILE lock on the procedure. However, eventually it determines that a new plan is not required (assuming no other reasons apply), so it does NOT recompile the plan at this point due to the lack of qualification. However, the extra step of getting a COMPILE lock on the procedure can cause blocking contention in severe situations. Refer to Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks for more details on this situation. If you owner qualify the procedure call with owner.procedure, you do not need to acquire the compile lock, so the contention is reduced.

Actually, the quoted part doesn’t prove the claim I made. I tried to find an answer on the web but I’ve failed so far. I can imagine two possible resolutions of no-owner-qualified names inside sp code: 1. All owner unqualified objects are considered to have the same owner, one that ownes the stored procedure (or dbo owned if former doesn’t exist). In that case no recompilation happens when different owner executes the procedure with execution plan in cache. 2. Objects are resolved as login owned objects if exist or dbo objects if not. In that case when different owner executes stored procedure, it has to be compiled again and probably two execution plan exist in the cache. This is interesting question I would like to find the answer, however the situation we started discussion about is that there is only one login used, so IMO specifying owner affects only first time execution when stored procedure is compiled. Frank, any quote from Kalen?

The first resolution you propose is not possible, I think, since for instance a.tbl1 may have a totally different structure than b.tbl1 – any assumption would be a priori incorrect. I would assume your second resolution is correct, in that the sproc WILL be recompiled if the owner bits are missing, even if the login is the same. Databaseninja – apologies for hijacking your thread. Thanks for an excellent question!

What I mean by first resolution is that by non-qualifying object names inside sp you implicitly specify that sp owner objects (or dbo) are going to be used, so when you call the sp by owner.sp you explicitly ask for sp from that owner and all his objects except for objects that were explicitly (fully) qualified. However I always worked with one or two application owners that referenced only dbo owned objects, so I really don’t have experience with that scenario.

Adriaan, do you mean if the owner bit is missing in the code that executes stored procedure or in the stored procedure code?

Mirko, As usual I learn something new when opening BOL. From the Remarks section for EXECUTE,

quote:If no qualified procedure name is provided, SQL Server searches for the procedure as if the owner name is dbo. To resolve the stored procedure name as a user-defined stored procedure with the same name as a system stored procedure, provide the fully qualified procedure name.

I then looked up "owner of database object" on the index of BOL. This brings up the "Database Object Owner" topic, where the Referencing database objects section says this,

quote:When users access an object created by another user, the object should be qualified with the name of the object owner; otherwise, MicrosoftÂ® SQL Serverâ„¢ may not know which object to use because there could be many objects of the same name owned by different users. If an object is not qualified with the object owner when it is referenced (for example, my_table instead of owner.my_table), SQL Server looks for an object in the database in the following order:
1. Owned by the current user.
2. Owned by dbo.
If the object is not found, an error is returned.

That’s what I was remembering, and unfortunately it is contradicting what the EXECUTE topix says. Perhaps sprocs are the exception to the rule?

I planed similar research, but right now I don’t have time for it. So thanks for looking into it. These quotes and some other topics/articles I read don’t provide the definite answer (42 [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]) to the question we are discussing now. Maybe, combining all of them we can come up to the conclusion and then test it somehow. However, as I already mentioned I will have to finish my current work first to be able to do the research. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

quote:Originally posted by mmarovic Adriaan, do you mean if the owner bit is missing in the code that executes stored procedure or in the stored procedure code?

I meant within the procedure itself, though it probably also applies to calling a procedure. Related question: if an uncompiled procedure runs a couple of queries, then would SQL look at separate execution plans available in cache for the individual statements?

Sorry guys for being late on this. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Quoting "InsideSQL Server 2000" <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Multiple Plans in Cache SQL Server will try to limit the number of plans for a query or a procedure. Because plans are reentrant, this is easy to accomplish. You should be aware of some situations that will cause multiple plans for the same procedure to be saved in cache. The most likely situation is a difference in certain SET options, database options, or configuration options. For example, a stored procedure that concatenates strings might compile the concatenation differently depending on whether the option CONCAT_NULL_YIELDS_NULL is on or off or whether the corresponding database option is true or false. If a user executes the procedure with the option on, that person will use a different plan than if the option is off. The column setopts in the syscacheobjects table is a bitmap indicating which SET options were in effect when the plan was placed in cache. If you start a different session with different SET options enabled, SQL Server will always generate a new plan. The options included in the setopts field include: … Two sessions that have set different languages or different dateformat values will also always generate separate plans. SQL Server must then also keep the langid value and dateformat value in syscacheobjects so that these values can be compared with future attempts to execute the same plan. One other connection issue can affect whether a plan can be reused. If an owner name must be resolved implicitly, a plan cannot be reused. For example, suppose user sue issues the following SELECT statement: SELECT * FROM mytable SQL Server will first try to resolve the object by looking for an object called mytable owned by sue, and if no such object can be found, it will look for an object called mytable owned by the DBO. If user dan executes the exact same query, the object can be resolved in a completely different way (to a table owned by dan), so sue and dan could not share the plan generated for this query. However, the situation is different if sue issues this command: SELECT * FROM dbo.mytable Now there’s no ambiguity. Anyone executing this exact query will always reference the same object. In the syscacheobjects table, the column uid indicates the user ID for the connection in which the plan was generated. Only another connection with the same user ID value can use the same plan. The one exception is if the user ID value is recorded as _2 in syscacheobjects, which indicates that the query submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. <hr height="1" noshade id="quote"></blockquote id="quote"> Hope this helps. Apart from this, I think it’s generally best practise to always be explicite. And I think it’s best practise that all objects should be owned by DBO. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

quote:
Related question: if an uncompiled procedure runs a couple of queries, then would SQL look at separate execution plans available in cache for the individual statements?

Adriaan, interesting question. Can you please open up a new thread for this.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Frank, Can’t see the point of different owners either. But then I’m not an enterprise type of developer. Finally, I think of the "daily WTF" site and remember I should be happy I’m not.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

]]>