Why Object Qualification is important.

  • Roy Ernest

    SSC-Dedicated

    Points: 38778

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

    -Roy

  • Maximilian Haru Raditya

    SSC Enthusiast

    Points: 145

    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

    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: 184381

    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: 38778

    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: 38778

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

    -Roy

  • Drew-420209

    SSC Enthusiast

    Points: 159

    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: 38778

    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

    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: 38778

    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

    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

    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: 38778

    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

    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: 184381

    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

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

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