Compile Lock Contention | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Compile Lock Contention

Hi guys…. Just came accros this compile Lock contention… I have temp table (#t1) in a procedure (SP1) and I fill it up with data. Later in the same procedure i call another SP (SP2) which uses the Temp Table (#t1) polpuated earlier… When Multiple clients use the SP1 there is compile lock on the proc SP2 (which uses the Temp table from SP1). The proc (SP1) is called from a batch and executed in a loop multiple times for a client. The problem arises when multiple client run the batch. Any way to redesign(work around) this. This is in prod and the procedures are complex to recode. As I understand this recompile cannot be avoided unless redesign. Thanks,
Shinoj R
Hi Dunno if it will help but i create the temp table with a GUID generated in the sp to the # table name, that way each connection gets a unique temp table. Cheers
Maybe keep plan option can help, I am not sure about that. I guess replacing temp table with table variable is not too much work. I hope there was no need to index that temp table except for primary key.
You might consider using a permanent staging table, instead of a temp table. The only session id you need is @@SPID, so include a column for that to clean up before and after. You should be able to do a find-and-replace in QA to change the references from #t1 to the permanent table. You’d need to add filtering on the session id column, that would be more work – but still no major code make-over.
Thanks for the reply guys…. Adriaan, this is the suggestion I have currently given, but the impact is huge. SP2 is used in many places and as each client goes live and the parallelism increases this issue was identified. Keep Plan also will not work. Thanks again.

Refer to KBA for information on compiling issues. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.