Compilation Locks

Question

Post reply

Compilation Locks

sjimmo

SSChampion

Points: 11139
More actions
May 10, 2010 at 10:09 pm

#224402

Comments posted to this topic are about the item Compilation Locks
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
May 11, 2010 at 12:10 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

sharath.chalamgari

SSCertifiable

Points: 5680

May 11, 2010 at 1:26 am

#1165371

Good question, i learned some thing out of it.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

Good question. Had to read up on it and then even got it right 🙂

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

While I agree that it is a good question, I think that there is a mistake in the answers. Since the question states that it is for SQL Server 2005, the fully qualified name of the object has nothing to do with the object’s owner. Instead it has to do with the object’s schema.

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 SSC Guru Points: 64790 More actions · Answer 4

Thanks, Steve. Nice question to remind of us one more reason (hah! as if we'd need one!) to always fully qualify our object names.

Small mistake in the explanation - is I read the referenced KB article, the compile lock occurs when both conditions are met, i.e. the SP is run by a non-owner, AND without using the fully qualified name. The explanation usees "or" instead of "and".

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 5

I did a fair amount of background reading on this question and it led me to several light bulb moments about my current coding practices. Thanks for a great question and a little bit of education first thing in the AM.

bradfield_eric Valued Member Points: 56 More actions · Answer 6

Thank you for the great QOD! I really do appreciate the reminder to fully qualify names and reduce the work required by SQL Server. In this case, the lack of name qualification (laziness) causes SQL Server to place a compile lock on a potentially heavily used stored pocedure and substantially increasing runtimes.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 736172

May 11, 2010 at 9:49 am

#1165643

Explanation is corrected.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

This is a very good question, thank you Steve.

Fully qualified objects names is a must, not subjected to any second guessing. This is a very pathetic scenario when due to someone's ignorance the database engine is forced to acquire compile lock thus potentially causing block contention just to determine that recompilation is not necessarily required.

Oleg

rVadim Hall of Fame Points: 3969 More actions · Answer 9

I'm a bit confused. Several of you suggested using "fully qualified names". But this usually refers to using all 4 parts: Server.Database.Schema.Object.

Is that what you suggest we should use everywhere in code?

--Vadim R.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

rVadim (5/11/2010)
I'm a bit confused. Several of you suggested using "fully qualified names". But this usually refers to using all 4 parts: Server.Database.Schema.Object.
Is that what you suggest we should use everywhere in code?

That was a bit unclear of "several of you" (of which I am one).

In this context, "fully qualified" should be read as "schema-qualified". My apologies.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

KevinC. SSCommitted Points: 1570 More actions · Answer 11

Great question. I learned something new, which is always great!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Great question. I learned something good today.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ziangij SSCrazy Eights Points: 8066 More actions · Answer 13

ziangij

SSCrazy Eights

Points: 8066

May 11, 2010 at 10:11 pm

#1165938

thanks..:-) good question