SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why Object Qualification is important.


Why Object Qualification is important.

Author
Message
Drew-420209
Drew-420209
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 70
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.
GregoryAJackson
GregoryAJackson
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 506
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?

Gregory A Jackson MBA, CSM
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8741 Visits: 6891
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.

-Roy
GregoryAJackson
GregoryAJackson
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 506
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 durations

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

Gregory A Jackson MBA, CSM
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45099 Visits: 14925
Drew (3/10/2008)
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.


Based on this quote from Microsoft in article about locking:

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


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.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8741 Visits: 6891
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.

-Roy
PaulBarbin
PaulBarbin
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 15
Good article Roy.

Jack, ad hoc queries are 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



srienstr
srienstr
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 459
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...


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8741 Visits: 6891
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.

-Roy
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8741 Visits: 6891
Hi Paul,

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

-Roy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search