Why Object Qualification is important.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Roy Ernest » Why Object Qualification is important.

39 posts, Page 1 of 4

1 2 3 4 »»»

Why Object Qualification is important.

Rate Topic

Display Mode

Topic Options

Author

Message

Roy Ernest

Posted Sunday, March 09, 2008 9:36 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 3,280, Visits: 6,622

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

-Roy

Post #466463

Maximilian Haru Raditya

Posted Monday, March 10, 2008 12:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:27 AM
Points: 28, Visits: 82

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

Post #466500

origamimark

Posted Monday, March 10, 2008 4:26 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 02, 2011 2:54 AM
Points: 28, Visits: 17

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 ?

Post #466543

Jack Corbett

Posted Monday, March 10, 2008 6:16 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Good article with a clear, concise explanation and test.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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

Post #466608

Roy Ernest

Posted Monday, March 10, 2008 6:20 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 3,280, Visits: 6,622

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

Post #466610

Roy Ernest

Posted Monday, March 10, 2008 6:21 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 3,280, Visits: 6,622

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

-Roy

Post #466611

Drew-420209

Posted Monday, March 10, 2008 6:32 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 17, 2011 8:17 AM
Points: 15, Visits: 70

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

SELECT *
  FROM dbo.table
 WHERE foo = 1

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

Thanks.

Post #466620

Roy Ernest

Posted Monday, March 10, 2008 6:40 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 3,280, Visits: 6,622

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

Post #466628

Drew-420209

Posted Monday, March 10, 2008 7:12 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 17, 2011 8:17 AM
Points: 15, Visits: 70

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.

Post #466650

Roy Ernest

Posted Monday, March 10, 2008 7:38 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 3,280, Visits: 6,622

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

Post #466670

« Prev Topic | Next Topic »

39 posts, Page 1 of 4

1 2 3 4 »»»

Permissions