SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Roy Ernest / Why Object Qualification is important. / Latest Posts

RE: Why Object Qualification is important.

badkow — Fri, 08 Jan 2010 07:30:04 GMT

Just wanted to ask; These are the list of SQL Server editions that the original KB article applied to:Microsoft SQL Server 7.0 Standard EditionMicrosoft SQL Server 2000 Standard EditionMicrosoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Express EditionMicrosoft SQL Server 2005 Workgroup EditionDoes this mean it don't apply to the SQL Server 2000 Enterprise Edition?I know it's a dumb question but it just popped up and its got me thinking!

RE: Why Object Qualification is important.

Bill Marriott-486259 — Wed, 30 Apr 2008 09:16:13 GMT

I will check Refactor.Find and replace is a really hard way to do it since I would have to 'find' each table name in my db - about 40.Bill

RE: Why Object Qualification is important.

Jack Corbett — Wed, 30 Apr 2008 08:51:20 GMT

[quote][b]Bill Marriott (4/30/2008)[/b][hr]Nice article but now I am wondering how to 'fix' my db!Are there any utilities out there that will bulk 2-part name my table references in all my stp's? - ie replace sometablename with dbo.sometablenameThanksBill[/quote]Not to pimp the owners of this site, but RedGate does offer SQL Refactor which claims to be able to do this at least on an object at a time.You could always script all your sp's as alter and the run find and replace.

RE: Why Object Qualification is important.

Bill Marriott-486259 — Wed, 30 Apr 2008 08:23:15 GMT

Nice article but now I am wondering how to 'fix' my db!Are there any utilities out there that will bulk 2-part name my table references in all my stp's? - ie replace sometablename with dbo.sometablenameThanksBill

RE: Why Object Qualification is important.

Ian Yates — Sat, 15 Mar 2008 19:47:10 GMT

Great article. Good to see some research into this oft-argued topic (particularly the sp_ prefix!).Following on from a few questions back, I'm still not sure if the answer was yes to multiple tables in the same database within different schemas, or if the answer was yes to same table within multiple databases. If the former, then do what works for you. If the latter then the next question would be...You have databases A & B. Are they completely separate - Does A access B's tables or vice-versa? If so then you need three part naming, otherwise stick with two part naming.Somewhere on this site there's a "worst practices" series of articles. One of them was having objects not owned by dbo. Direct flames in that article's forum if you don't agree :D

RE: Why Object Qualification is important.

Roy Ernest — Tue, 11 Mar 2008 07:03:55 GMT

Thank You all for the feedback. I appreciate it very much.

RE: Why Object Qualification is important.

ChiragNS — Mon, 10 Mar 2008 23:15:23 GMT

Nice article and easy to understand.

RE: Why Object Qualification is important.

timothyawiseman — Mon, 10 Mar 2008 23:05:25 GMT

This was a beautiful article. Thank you for both testing and quantifying what is normally assumed.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 13:50:44 GMT

I agree with sreinster. If there are really no compelling reason for this, then they should be completely qualified. In your case 3 part qualification. Especially if your DBs are quite busy. Then every bit of performance you can gain counts.Also keep in mind that if by any chance it does recompile, there is no guarantee that it will take the right execution plan. It might take a bad execution plan. That is something that you really need to avoid. I have seen stored procs that take bad query plan increase the CPU use of the SQL Server by 10%.

RE: Why Object Qualification is important.

srienstr — Mon, 10 Mar 2008 12:23:36 GMT

[quote][b]sjsubscribe (3/10/2008)[/b][hr][quote]Do you have difference lookup tables in the same database with different owners, or are they owned by dbo but in different databases/on different servers? [/quote]Yes.[/quote]Are you saying that both apply, or was I unclear in phrasing my question?[quote][quote]he best thing to do is to make sure you qualify all objects with the Schema...[/quote]No. It's not the best thing.[/quote]While basically everything falls into "It depends", it is still [i]often[/i], if not quite universally, advisable. 32ms of compile time may be insignificant, and a few ms on each run may be acceptable, but unless there is a specific need to choose a less efficient method, efficient is preferred.

RE: Why Object Qualification is important.

sjsubscribe — Mon, 10 Mar 2008 12:10:54 GMT

[quote]Do you have difference lookup tables in the same database with different owners, or are they owned by dbo but in different databases/on different servers? [/quote]Yes.[quote]he best thing to do is to make sure you qualify all objects with the Schema...[/quote]No. It's not the best thing.

RE: Why Object Qualification is important.

srienstr — Mon, 10 Mar 2008 11:28:46 GMT

[quote][b]sjsubscribe (3/10/2008)[/b][hr]Well, good advice if you're dealing with one server and single schemas or need to optimize for in specific instances. But if you're writing procedures that need to run across many schemas, servers, and automatically pick the right tables without undue hard coding makes sense. For example, I have several lookup tables with the same names but with different data on different servers, schemas, and so on. But my stored procedures are the same across all machines. By not using dbo, I let the appropriate table get picked up at execution time. Price I pay is minimal compared to managing customized code for each server. To save compute cycles, fully qualified names could make a difference in certain circumstances, but to save programmer cycles, then the substitution model could be your friend.[/quote]Do you have difference lookup tables in the same database with different owners, or are they owned by dbo but in different databases/on different servers? If you need different results [i]within the same database[/i] then the substitution method makes sense, but using the two-part name wouldn't impact the functionality you're looking for if the relevant tables are all owned by dbo in different databases.Three part naming is obviously right out in your situation, but my understanding is that three part naming has no performance advantage to the more flexible two part naming.

RE: Why Object Qualification is important.

sjsubscribe — Mon, 10 Mar 2008 11:17:08 GMT

Good article, but perhaps a bit overreaching in its conclusions: "The best thing to do is to make sure you qualify all objects with the Schema (Also known as Owner)"Well, good advice if you're dealing with one server and single schemas or need to optimize for in specific instances. But if you're writing procedures that need to run across many schemas, servers, and automatically pick the right tables without undue hard coding makes sense. For example, I have several lookup tables with the same names but with different data on different servers, schemas, and so on. But my stored procedures are the same across all machines. By not using dbo, I let the appropriate table get picked up at execution time. Price I pay is minimal compared to managing customized code for each server. To save compute cycles, fully qualified names could make a difference in certain circumstances, but to save programmer cycles, then the substitution model could be your friend.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 10:19:47 GMT

Sideout, Yes it is always better to call objects with their qualifier.

RE: Why Object Qualification is important.

Jason-262847 — Mon, 10 Mar 2008 10:16:16 GMT

To recap...is the consensus that using non-fully qualified object names, including for tables, stored procedures, and other objects, will have a performance hit for both SQL 2000 and SQL 2005?

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 09:42:32 GMT

Hey Jack, Thanks for the excel file. Thats a pretty good testing.. :)

RE: Why Object Qualification is important.

Jack Corbett — Mon, 10 Mar 2008 09:33:30 GMT

I did a quick test kind of following Gregory's suggestion except I did not use profiler I user Set Statistics Time On. The first thing I did was create a user who did not "own" the dbo schema nor have dbo as it's default schema, but did have select permissions on the dbo schema. I wanted to make sure I was crossing schemas. I also ran DBCC DropCleanBuffers and DBCC FreeProcCache between each execution. Then I ran it only executing the DBCC statements before the first run of each query. The query did use a covering non-clustered index as well. I was trying to stick it in here, but decided the formatting wasn't appropriate so I attached an Excel file instead. A quick summary is that, if the plan is in cache they took about the same time. When the plan was not in cache, the non-qualified query took about 33ms to parse and compile and the qualified query to 1ms to parse and compile. This is a very big difference in performance. I also ran it with the users default schema being DBO, results not in the Excel File, and the Parse and Compile times were the same after running the DBCC statements. This would lead me to believe that in SQL 2005, if you have only 1 schema, that they both perform the same way. I am not going to make any definitive statements based on this limited testing though.I think the biggest argument for using fully qualified names in ad-hoc SQL is consistency so that you only get 1 plan in cache for each query vs. 1 for non-qualified queries and 1 for qualified queries. This allows you to have more distinct plans in the cache.Paul,I guess I was slightly confused in terminology between compiled and cached. I know that ad-hoc queries plans are cached and, after looking it up in BOL, realize that this is what compilation really is. Thanks for the prompt.

RE: Why Object Qualification is important.

JJ B — Mon, 10 Mar 2008 09:25:11 GMT

Fun article and great discussion. Thanks.

RE: Why Object Qualification is important.

srienstr — Mon, 10 Mar 2008 09:12:05 GMT

Bother. At least I have them running separately through the Job Agent rather than calling each other so I'll only have to change them in one place. Thanks for letting me know Roy.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 09:09:21 GMT

Hi Paul,Yes, you are right. I had both 2005 and 2000 installed in my local box and ran the tests under 2000 instance.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 09:07:48 GMT

Hi srienstr,No, Even if you provide the DB and the object qualifier, it will still go through all the hassles of Acquiring a Lock and then releasing it.

RE: Why Object Qualification is important.

srienstr — Mon, 10 Mar 2008 08:59:25 GMT

Out of curiosity, does specifying the 3-part name for a stored procedure (e.g. srienstr.dbo.sp_diabetes) prevent the check of master for procedures with a prefix of 'sp_'?Not that I've been foolish enough to make 17 such stored procedures...

RE: Why Object Qualification is important.

PaulBarbin — Mon, 10 Mar 2008 08:55:46 GMT

Good article Roy.Jack, ad hoc queries [b]are[/b] compiled. I believe this started in 2000, if not, then definitely 2005. Also, that brings up another point, I guess this article was written for 2000 because 2005 Profiler no longer has the event ExecContextHit (that I see). It does have one under Stored Procedures called "Deprecated", maybe that is it?Btw, when I ran my profile on 2005, I get way more cache misses in both cases than the article says. However, bottom line is that I get twice as many lines in Profiler when I don't qualify as when I do. So it seems like a good idea to do so.Paul

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 08:18:20 GMT

Hi Gregory, Thats worth a try. I will try to do that and see if I can find anything. Jack, Thanks for answering that. I was not sure about it to say for certain that it wont have an issue.

RE: Why Object Qualification is important.

Jack Corbett — Mon, 10 Mar 2008 08:14:38 GMT

[quote][b]Drew (3/10/2008)[/b][hr]Thank you for the quick reply, I don't think I was specific enough with my question however.Let's assume that those queries are not contained in a stored procedure and are executed from our program.As I understand, frequently run queries will be stored and optimized over time. Could I expect to have these locks occur with these "ad-hoc" queries?Thanks again.[/quote]Based on this quote from Microsoft in article about locking:[quote]"If user "dbo" owns object dbo.mystoredproc, and another user "Harry" runs this stored procedure with the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists, so SQL cannot be sure that the cached plan for dbo.mystoredproc is the right one to execute.) SQL Server then acquires an exclusive compile lock on the procedure and makes preparations to compile the procedure, including resolving the object name to an object ID. Before it compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and is able to locate a previously compiled plan even without the owner qualification.[/quote]I would venture to say that locking would not be an issue in the case of ad-hoc sql because the ad-hoc SQL is not compiled like the sp is. You will have the same issues with having to check the ownership and then permissions so it will still be a better practice to qualify the object names anyway. Another thing to note is that if you sometimes qualify the object and sometimes don't you get 2 plans in the cache and in that case you are reducing the actual number of queries that can be cached. Best practices are to always use at least 2 part naming.

RE: Why Object Qualification is important.

GregoryAJackson — Mon, 10 Mar 2008 08:13:50 GMT

I would just execute the statement in question 5-10 times while profiling it for duration.run it 5-10 times without qualified naming and then 5-10 times with qualified names.average the durationsprobably run dbcc freeproccache and dbcc dropcleanbuffers between each execution to standardize the results.it wont be a perfect test statistically, but you should see some measurable differences....

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 08:09:27 GMT

Thanks Gregory for the positive feedback. Unfortunately I do not have hard numbers to match what I have said. The main reason is that the profiler does not show any details for cachemiss and Locks Acquired. I have been researching on how to get the numbers but so far I have not succeeded. If I do get any idea regarding this matter, I will surely let you know.

RE: Why Object Qualification is important.

GregoryAJackson — Mon, 10 Mar 2008 07:48:32 GMT

Roy,Nice Work !This is a well written, clear article.I've always known this to be an issue, and in many places I've worked, we've adamantly forced the qualified naming conventions.In the relatively new job I have now, we dont enforce the qualified conventions. I'd be very interested to see some quantified measurements of the performance impact that is being introduced here.Do you have any numbers at all to add to your research?

RE: Why Object Qualification is important.

Drew-420209 — Mon, 10 Mar 2008 07:48:27 GMT

Thanks,luckily the nature of and the environment that our system runs in makes security more or less a non-issue. I'll take the example code from the article and see if I can apply the theory to embedded SQL.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 07:38:34 GMT

Hi Drew,Thats an interesting question. I am not sure I know the answer to that without testing for it. I have never been able to test for it since we in our company do not allow embedded SQLs, Period. Basic reason for that is the security concern. I am sure you know that security concern of having embedded SQLs. But to answer your question, I am not sure. Maybe one of the Gurus here can answer that.

RE: Why Object Qualification is important.

Drew-420209 — Mon, 10 Mar 2008 07:12:49 GMT

Thank you for the quick reply, I don't think I was specific enough with my question however.Let's assume that those queries are not contained in a stored procedure and are executed from our program.As I understand, frequently run queries will be stored and optimized over time. Could I expect to have these locks occur with these "ad-hoc" queries?Thanks again.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 06:40:03 GMT

Hi Drew,Yes, there will be a performance hit in certain scenarios. Let us say the schema qualifier is DBO (Owner) and you are executing the stored proc as the user test who is not the owner. If you do not qualify, Query engine is first going to see if the object is owned by the user test. When it find it is not the owner or the object is not under the user Test, it tries to find the object and then checks if it has the necessary permissions. The additional look up it has to do is the performance hit in this case.

RE: Why Object Qualification is important.

Drew-420209 — Mon, 10 Mar 2008 06:32:27 GMT

Great article.Would we expect to see the same kinds of performance issues in frequently run queries that do not qualify the owner of the table?E.g.[code]SELECT * FROM table WHERE foo = 1[/code]vs[code]SELECT * FROM dbo.table WHERE foo = 1[/code]Would it perform locks to see if there is already an execution plan for that query?Thanks.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 06:21:41 GMT

Thanks Jack. I am glad you found that it explains the point I am trying to make.

RE: Why Object Qualification is important.

Roy Ernest — Mon, 10 Mar 2008 06:20:51 GMT

Thanks for reading the article. When you use sp without an underscore, there are no issues. This happens only when it is sp_. The basic reason is because the system generated stored procs all start with sp_. Mark, Yes, it is designed by SQL server to take an exclusive lock. But it is actually a good design. When there is no execution plan, Only one SPID needs to compile it. Others will use this compiled cache plan. So when there is no Object Qualifier and the Querry engine does not see a Cached query plan, it automatically takes an exclusive lock so that no other process can try to compile it while this is compiling. I would say the design is not faulty. The design is good if we follow the recommendations.

RE: Why Object Qualification is important.

Jack Corbett — Mon, 10 Mar 2008 06:16:45 GMT

Good article with a clear, concise explanation and test.

RE: Why Object Qualification is important.

origamimark — Mon, 10 Mar 2008 04:26:18 GMT

Surely this locking business is a design fault in SQL Server. Why is it taking out an exclusive lock on the object before it needs to ?

RE: Why Object Qualification is important.

Maximilian Haru Raditya — Mon, 10 Mar 2008 00:46:06 GMT

So you have mentioned stored procedures prefixed with "sp_". How about if they are prefixed with "sp" only (without underscore)? Are they affected too?Is there any recommended solution? I mean not only scoped due to this constraint, but for a kind of standards, maintainability, etc?Thanks!

Why Object Qualification is important.

Roy Ernest — Sun, 09 Mar 2008 21:36:04 GMT

Comments posted to this topic are about the item [B]Why Object Qualification is important.[/B]