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 123»»»

Worst Practice - Not Qualifying Objects With The Owner Expand / Collapse
Author
Message
Posted Friday, March 5, 2004 5:56 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
Comments posted to this topic are about the content posted at http://www.sqlservercentral.



--
Chris Hedgate
http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #104780
Posted Tuesday, March 9, 2004 11:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
Ad it to the comments on http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=32&messageid=11677 

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #105281
Posted Wednesday, March 10, 2004 6:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 302, Visits: 793

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....




Post #105309
Posted Wednesday, March 10, 2004 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 24, 2004 10:31 AM
Points: 1, Visits: 1

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.

Post #105319
Posted Wednesday, March 10, 2004 7:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1

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




Post #105323
Posted Wednesday, March 10, 2004 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 25, 2006 9:53 AM
Points: 8, Visits: 1

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.




Post #105325
Posted Wednesday, March 10, 2004 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:00 PM
Points: 39, Visits: 98

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?




Post #105328
Posted Wednesday, March 10, 2004 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:48 AM
Points: 47, Visits: 24

(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?

 




Post #105344
Posted Wednesday, March 10, 2004 8:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:48 AM
Points: 47, Visits: 24

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...

 




Post #105347
Posted Wednesday, March 10, 2004 10:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, January 4, 2014 11:05 PM
Points: 968, Visits: 67

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 @params =
N'@empid int'
SET @intvar = 5
EXEC sp_executesql @sql, @params, @empid = @intvar

-- This will generate SP:ExecContextHit in SQL Profiler:
SET @intvar=6
EXEC sp_executesql @sql, @params, @empid = @intvar

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




Post #105371
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse