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

Why Object Qualification is important. Expand / Collapse
Author
Message
Posted Sunday, March 9, 2008 9:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
Comments posted to this topic are about the item Why Object Qualification is important.

-Roy
Post #466463
Posted Monday, March 10, 2008 12:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:06 AM
Points: 28, Visits: 97
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
Posted Monday, March 10, 2008 4:26 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 2, 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
Posted Monday, March 10, 2008 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 11,238, Visits: 12,998
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
Posted Monday, March 10, 2008 6:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
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
Posted Monday, March 10, 2008 6:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
Thanks Jack. I am glad you found that it explains the point I am trying to make.

-Roy
Post #466611
Posted Monday, March 10, 2008 6:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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.
Post #466620
Posted Monday, March 10, 2008 6:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
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
Posted Monday, March 10, 2008 7:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, March 10, 2008 7:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse