Worst Practice - Not Qualifying Objects With The Owner
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.
-
Ad it to the comments on http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=32&messageid=11677
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I have accepted this recommendation due to the performance impact, and don't argue that.
But I find that this has killed for me the utility of object-ownership in general. Has anyone made good use of object ownership since following this "always owner-qualify object references" guideline? I haven't. Just an extra "dbo." that I have to add everywhere--more typing with no benefit.
But back in the day, before this advice was proffered, we used to be able to use the ownership mechanism to implement some pretty cool workarounds. Applications & procs had to reference objects with ".." instead of explicit owner name for this to work. When/if we created non-dbo-owned objects, a subset of users would access these instead, getting whatever different behavior was desired. I guess this could get out of hand if used recklessly, but when used carefully this was a great solution to some problems. Can't use this anymore because all of our production code has "dbo."
Why, yes, I would like some cheese with that whine....
-
I'm relatively new to SQL Server and have been using it for a web based intranet application. Even though I have not encountered any problems, yet, from failing to qualify objects with the owner, this stills seems like good advice to me.
Thanks for the article.
-
This is great advice.
I would be curious to know however how many environments allow people to create objects with other than DBO as the owner. In both environments where I have been a dba now neither of them has used user ojects, all objects are dbo. I think this is a great bit of functionality but I wonder how many people actually really use it.
Any thoughts?
Ross
-
Good advice as long as you are not trying to use the ownership rules to your advantage.
I would add that "Qualify object name by its owner" on the Script tab of the Options dialog in QA should be checked. The script generator in EM qualifies objects with the owner by default.
-
Curious.. if you we're aliased to dbo and all objects referenced where owned by dbo. would fully qualifying dbo. owned objects still be a benefit?
-
(dwebb) - That really is the question. As (bozo7) put it, it seems as though most people do not take advantage of multiple ownership of objects, which, based on my experience, I would have to agree... it seems to be a level of "complexity" that many administrators oftentimes don't take the time to investigate, and admittedly, in many environments may not be the appropriate way to architect the system anyway.
So, that truly does beg the question: Is this the appropriate "worst practice" in an environment that ONLY utilizes dbo owned objects? As the article pointed out, if you ever STARTED to take advantage of user-based ownership of objects in the database, then the "dbo" qualification can work to your advantage in applications that have been constructed in that manner.
But, as (dwebb) asked, is there a true performance difference in an environment that aliases everybody to dbo? If you NEVER qualify any of your objects, and always assume dbo, are we really gaining anything? I.e., will we wind up with a cache hit or not?
If so, then from a performance standpoint, this is a non-issue.
If not, then why? If all of the objects are owned by the exact same user, and that user is dbo, and this configuration is utilized by the majority of SQL environments (ok, big assumption there, but...), woudn't it only make sense to assume dbo first, always, and forever, amen, REGARDLESS of whether we tell the system that this is the object we mean?
-
Actually, this topic got me thinking of another really bad practice along the lines of "object qualification". Perhaps this is not the thread for this discussion, but I'd be curious to see how everyone responds.
The bad practice I'm referring to is SERVER.DBNAME qualification (in "user" databases of course... don't give me a hard time for master, msdb, et al). The problem in this case is not performance; it's code reusability.
Once you stick that SERVER or DBNAME in your qualification, you're done. Actually, I've only seen the DBNAME ever really hardcoded, as most people know better than to stick a SERVER qualification, unless you're using a linked server, in which case you kind of have to.
But, back to the point... Of course the problem with dbname() qualification is the inability to move the SQL to another database of a different name. The only time (that I can think of) that you would want/need to do this is for cross-database SQL. Then, in that case, for architecture reasons, I've found it better to take a hit on performance (by utilizing dynamic SQL), rather than hardcoding a database name into SQL.
ok, go ahead, shoot me down or ignore me...
-
This article is very misleading on one important point. The article comes very close to stating, or at least implying, that you must specify owner names for cache reuse to work. Although the author didn't say that explicitly, I think that unless a reader already understood cache reuse (before reading the article), then they would walk away from this article thinking that you must supply owner names or you will not get cach reuse. This is simply not true.
What is true is that cache reuse is based on the text string being executed, after adjusting for parameters. In other words, if SQL has seen your parameterized string before (even if SQL had to auto-parameterize it), it will obtain a cache hit (assuming for the moment a stable set of session settings for both invocations).
If you look at the way the author's script is layed out, all it proves is that if you compile two different SQL strings (adjusted for parameterization), you get two different cache inserts. This is true whether or not user names are explicitly used. The simple script below shows that you can obtain cache hits using default object names. Trace the following script and you will see a cache insert for the first sp_executesql call followed by a cache hit for the second call, even though the script's SQL string does not specify an object owner name.
USE NORTHWIND
DECLARE @sql nvarchar(200)
DECLARE @params nvarchar(100)
DECLARE @intvar int
-- This will generate SP:CacheInsert in SQL Profiler:
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE EmployeeID = @empid'
SET @intvar = 5
EXEC sp_executesql @sql, @params, @empid = @intvar
-- This will generate SP:ExecContextHit in SQL Profiler:
SET @intvar=6
There is enough misunderstanding about this topic out in the wild that we should really be careful how we talk about it here, where lots of people come looking for SQL expertise. Otherwise, we may wind up reading an article some day entitled (and I hope the other Chris takes this in good humor) Worst Practices: Potentially Misleading Worst-Practice Articles.
I didn't have time to read through all the other posts, so I apologize if anybody else has covered this.
Cheers,
Chris
-
In regards to the context of stored procedure execution, this article contains a contradiction and some confusion.
It is my understanding that statements executed within a SP default to the permissions level of the owner of the SP. If a SP was created as dbo.whatever then all statements executed within that SP would be in the context of dbo, meaning that any objects not owner-qualified would be accessed with dbo context first. IE you will never get a dbo.procedure1 with unqualified object names accessing those objects as user1.object. It will always access them as dbo.object.
The article seems to reiterate exactly this: "objects that are not qualified with owner name and referenced in SELECT, INSERT, UPDATE and DELETE statements will default to the owner of the stored procedure". But then it later says something different when explaining that John.Orders would be truncated when John executes a SP owned by Jane. According to the above explanation it would always truncate Jane.Orders since the table name is not qualified in Jane's SP and Jane owns the SP.
So which way is it really?
-
I had never really thought about this subject when I read the article and I didn't get that impression. I may have mis-read the article (and I'm posting without re-reading it) but I IMHO the warning about cache misses was clear enough to not lead to a misunderstanding.
-
Jeb58,
I agree that the author's intent appears to be to indicate that defaults assumed in object name resolution aren't going to get you a cache hit, but there are all kinds of changes you can make to a query string that will lose the cache hit. In fact, if I change anything in the query string, I lose the cache hit. The presence or absence of an explicitly-named owner is just one way to mess this up, and since applications generate code the same way, I can get very, very good cache reuse from an application that never explicitly qualifies the owner. This is the point that I think is - potentially - easily lost in translation in this article.
But you have your nickel, and I've only got my two cents. If you understand cache reuse well, that's great. But I'm suggesting this could be misleading to a newbie. I certainly have my hands full trying to get some people in my organization to understand object ownership at all!
Cheers,
Chris
-
Chris,
Your thoughts about my intent of the example are correct. I never even thought about the possibility to misinterpret it the way you fear 'newbies' might. But I do understand your point. The key line I think is this one:
"...SQL Server will not reuse the execution plan for statements specifying differently qualified object names"
I see now that I might have been able to write that in a more explicit (no pun intended) way. What I of course meant was something like "...even if the only difference between query A and query B is the qualification of owner names, when executing query B SQL Server will not reuse the execution plan of query A". Maybe I could have also explicitly (again [
]) stated that also the execution plan for query B will be reused if it is executed again, just as the one for query B. It would just have been an extra execution in the sample script so that would probably have been a good idea. But I must admit that I have no intention of writing articles intended for absolute beginners, I think it is more interesting and appealing to a larger audience to raise the level a bit. Therefore I hope that most readers will understand the part regarding defaults and how these will be used for cache reuse. Finally, the main intent of the article was not at all to point at the performance gains you can get by qualifying objects with owner name, but rather to show that there are several good reasons to do it and none other than laziness not to do it.In any way, I do not see how this advice can be wrong for anyone, newbie or seasoned veteran. Yes, there are some arguments to be made regarding code reusabilty (as stated in earlier messages in this thread), but I think there are other ways to handle those situations. And no matter how much a worst practice, there are of course always some kind of exception to anything. But as I said in the article, the only reason (except for the above) to not qualify objects with owner names is laziness. Or if you turn it around, if you do not have a reason not to qualify them, why should you then not do it?
-
slider,
It is both ways. If you create and test the scenario I describe you will see that it is John.Orders that is truncated. Normally SQL Server will default to the name of the creator of the procedure when resolving the object names (at run-time), but for these statements it will use the name of the user executing the procedure.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply