what is compile wait resource and how to resolve it

Question

Post reply

what is compile wait resource and how to resolve it

farax_x

Hall of Fame

Points: 3579
More actions
November 15, 2016 at 3:04 am

#337301

Hi,
I use sys.dm_exec_requests to find wait resources and the result is :
wait_type---------------------- wait_resource
LCK_M_X ----------------------OBJECT: 10:1209665115:0 [COMPILE]
this object id is an stored procedure. What is it about ? and how can I resolve it ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The procedure is being compiled by the query optimiser to generate plans.

Is this a common wait? Are the wait times long? What is the session that's waiting trying to do?

Just because it shows up in exec_requests doesn't mean it's a problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

farax_x Hall of Fame Points: 3579 More actions · Answer 2

Is this a common wait?

yes, Every time I query sys.dm_exec_requests I saw that

Are the wait times long?

less than a second (about 8 miliseconds)

What is the session that's waiting trying to do?

The applications(8 apps) which consume this sp

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

farax_x (11/15/2016)
Are the wait times long?
less than a second (about 8 miliseconds)

That's pretty low. Is this wait causing a problem?

What is the session that's waiting trying to do?

The applications(8 apps) which consume this sp[/quote]

What, exactly, is the session that's waiting trying to do? What command, what statement?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

farax_x Hall of Fame Points: 3579 More actions · Answer 4

GilaMonster (11/15/2016)
farax_x (11/15/2016)
Are the wait times long?
less than a second (about 8 miliseconds)

That's pretty low. Is this wait causing a problem?

What is the session that's waiting trying to do?

The applications(8 apps) which consume this sp[/quote]

What, exactly, is the session that's waiting trying to do? What command, what statement?[/quote]

That's pretty low. Is this wait causing a problem?

yes, applications block each other when executing the sp !

ALTER PROCEDURE [crw].[uspCheckMessageExistence]

(

@id BIGINT ,

@toId BIGINT ,

@editDate INT ,

@status TINYINT OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @L_editDate INT = 0;

DECLARE @L_MessageId BIGINT= 0;

SELECT TOP ( 1 )

@L_MessageId = MessageId ,

@L_editDate = editDate

FROM data2.[Message] WITH ( NOLOCK )

WHERE id = @id

AND toId = @toId;

IF ( ISNULL(@L_MessageId, 0) > 0 )

BEGIN

SET @status = 2;

IF ( @editDate IS NOT NULL

AND @L_editDate <> @editDate

)

SET @status = 1;

END;

ELSE

BEGIN

SET @status = 0;

END;

(@P1 bigint,@P2 int,@P3 int,@P4 int OUTPUT)EXEC crw.uspCheckMessageExistence @P1, @P2, @P3, @P4 OUTPUT;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You're going to have to investigate why that procedure is compiling frequently. There's nothing in the code that would cause compiles or recompiles, so start by tracking down which is happening and why.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

farax_x Hall of Fame Points: 3579 More actions · Answer 6

GilaMonster (11/15/2016)
You're going to have to investigate why that procedure is compiling frequently. There's nothing in the code that would cause compiles or recompiles, so start by tracking down which is happening and why.

how can i thrack this ?

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

farax_x (11/15/2016)
GilaMonster (11/15/2016)
You're going to have to investigate why that procedure is compiling frequently. There's nothing in the code that would cause compiles or recompiles, so start by tracking down which is happening and why.
how can i thrack this ?

Are you getting different execution plans?

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

farax_x (11/15/2016)
GilaMonster (11/15/2016)
You're going to have to investigate why that procedure is compiling frequently. There's nothing in the code that would cause compiles or recompiles, so start by tracking down which is happening and why.
how can i thrack this ?

There's a bunch of ways, for SQL 2014 best is Extended Events, look up (and read up in advance) on the cache hit, cache insert, cache miss, cache remove events and the recompile events (and please do the reading up so that you know what you're looking at)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL ADMIN 79 SSCommitted Points: 1993 More actions · Answer 9

Gail,

I am seeing high "Compile: waits on a stored procedure causing other processed not to execute the same stored procedure. This is leading to blocking on the database and is slowing down the application. Can you please guide me on how to triage this issue?

Thanks

Prakash B