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