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 «««123

Compilation Locks Expand / Collapse
Author
Message
Posted Wednesday, May 12, 2010 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,096, Visits: 8,362
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
Post #920541
Posted Wednesday, May 12, 2010 9:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:13 AM
Points: 2,917, Visits: 2,536
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
Post #920595
Posted Wednesday, May 12, 2010 9:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,096, Visits: 8,362
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
Post #920635
Posted Wednesday, May 12, 2010 10:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:13 AM
Points: 2,917, Visits: 2,536
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
Post #920645
Posted Wednesday, May 12, 2010 10:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 2,127, Visits: 5,550
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/
Post #920652
Posted Wednesday, May 12, 2010 10:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,096, Visits: 8,362
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
Post #920693
Posted Friday, May 14, 2010 11:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #922421
Posted Friday, May 14, 2010 11:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #922425
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse