SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compilation Locks


Compilation Locks

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18283 Visits: 12426
sjimmo (5/12/2010)
Unfortunately, many think that a hint is law, and have spent many hours debating hints, as well as showing hints that work and then not work because the optimizeer deemed that it knew better.

Well, actually hints are law. The term "optimizer hint" is highly misleading; "optimizer directive" would have been better. If you use an index hint, the optimizer WILL use that index for the query. If you specify to use hash joins, you WILL get hash joins. No matter how high the cost and how much cheaper an unhinted plan would have been.

If you have any evidence to the contrary, please let me know - it's quite likely to be a bug.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4718 Visits: 2907
Hugo,

Mine has been more in the rowlock, nolock and tablock hints. I have had cases where the optimizer determined that the tablock was more effective than a rowlock and escalated it as such. Possibly a bug, won't wswear to it as my understanding has been that this is the way that it should be.

Thanks

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18283 Visits: 12426
sjimmo (5/12/2010)
Hugo,

Mine has been more in the rowlock, nolock and tablock hints. I have had cases where the optimizer determined that the tablock was more effective than a rowlock and escalated it as such. Possibly a bug, won't wswear to it as my understanding has been that this is the way that it should be.

Thanks

Ah, that's what you meant.

This is not a bug. Rowlock and pagelock are mutually exclusive, and both state to use either row locks or page locks as long as escalation to table locks is not required. The pagelock hint tells SQL Server to simply forego the stage of locking individual pages or individual rows and lock the entire table right at once.

EDIT: Corrected a very stupid eror - thanks, Adi, for pointing it out!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4718 Visits: 2907
This is not a bug. Rowlock and pagelock are mutually exclusive, and both state to use either row locks or page locks as long as escalation to page locks is not required. The pagelock hint tells SQL Server to simply forego the stage of locking individual pages or individual rows and lock the entire table right at once.


Exactly. As for the index hints, I knew that one. But thanks for the confirmation.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Adi Cohn
Adi Cohn
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7741 Visits: 6594
This is not a bug. Rowlock and pagelock are mutually exclusive, and both state to use either row locks or page locks as long as escalation to page locks is not required.


I think that lock escalation can't escalate to page lock. If I'm not mistaken until SQL Server 2008 lock escalation could escalate directly to table lock and since SQL Server 2008 if the table is partitioned, it can escalate to lock a partition in the table (or few partitions if needed).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18283 Visits: 12426
Adi Cohn-120898 (5/12/2010)
This is not a bug. Rowlock and pagelock are mutually exclusive, and both state to use either row locks or page locks as long as escalation to page locks is not required.


I think that lock escalation can't escalate to page lock. If I'm not mistaken until SQL Server 2008 lock escalation could escalate directly to table lock and since SQL Server 2008 if the table is partitioned, it can escalate to lock a partition in the table (or few partitions if needed).

Adi


Oops. Stupid mistake. My mind said table lock, my fingers typed something else.

Thanks for pointing it out; I'll see if I can edit the post.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33832 Visits: 11359
Well I selected both the answers required to get the green tick, but I am pretty sure the 'answer' is incorrect.

Sure, not schema-qualifying the procedure name leads to compilation locks, but not being the owner of the procedure is not sufficient to do so.

"The user that runs the stored procedure is not the owner of the procedure." should not be a correct answer here.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33832 Visits: 11359
Hugo Kornelis (5/12/2010)
Well, actually hints are law. The term "optimizer hint" is highly misleading; "optimizer directive" would have been better. If you use an index hint, the optimizer WILL use that index for the query. If you specify to use hash joins, you WILL get hash joins. No matter how high the cost and how much cheaper an unhinted plan would have been. If you have any evidence to the contrary, please let me know - it's quite likely to be a bug.

Even "optimiser directive" doesn't quite capture it, since not all 'hints' apply to the optimiser. Some hints (like NOWAIT, REPEATABLEREAD, IGNORE_TRIGGERS) are only used by the execution engine. Other hints (like KEEPIDENTITY) materially change the effect of the query.

I prefer to think of the true optimiser hints as 'implementation rule switches', since they affect which physical implementations are available to the optimiser as it explores the plan space.

Given the wide range of effects on various stages of query execution, I think 'hint' is as good a term as any, though undoubtedly a flawed one.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search