Why Object Qualification is important.

Question

Why Object Qualification is important.

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
March 9, 2008 at 9:36 pm

#179809

Comments posted to this topic are about the item Why Object Qualification is important.
-Roy

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Maximilian Haru Raditya SSC Enthusiast Points: 155 More actions · Answer 1

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!

--
Regards,

Maximilian Haru Raditya

origamimark SSC Journeyman Points: 98 More actions · Answer 2

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 ?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Good article with a clear, concise explanation and test.

Jack Corbett
Consultant - Straight Path Solutions
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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

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.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

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

-Roy

Drew-420209 SSC Enthusiast Points: 159 More actions · Answer 6

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.

SELECT *

FROM table

WHERE foo = 1

vs

SELECT *

FROM dbo.table

WHERE foo = 1

Would it perform locks to see if there is already an execution plan for that query?

Thanks.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

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.

-Roy

Drew-420209 SSC Enthusiast Points: 159 More actions · Answer 8

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.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

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.

-Roy

Drew-420209 SSC Enthusiast Points: 159 More actions · Answer 10

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 SSCrazy Points: 2794 More actions · Answer 11

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 SSC-Dedicated Points: 38780 More actions · Answer 12

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 SSCrazy Points: 2794 More actions · Answer 13

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 SSC Guru Points: 184394 More actions · Answer 14

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
Consultant - Straight Path Solutions
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