MS SQL Server - Excessive stored procedure [COMPILE] lock

Author

Excessive stored procedure [COMPILE] lock

Igor Marchenko

2005-05-16, 8:23 pm

Hello!

I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):

CREATE PROCEDURE [dbo].& #91;vsp_mail_select]

@user_id int,
@folder_id int,
@is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@start_index int = null, --unused for now, we return everything
@total_count int = null output, -- count of all mail in specified folder
@unread_count int = null output -- count of unread mail in specified folder

AS
SET NOCOUNT ON

select m1.* from mail m1(nolock) where m1.user_id=@user_id and
folder_id=@folder_id
and ((@is_read=0 and is_read=0) or (@is_read=1)) order
by date_sent desc
select @total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@user_id and folder_id=@folder_id
and ((is_read=0 and @is_read=0)
or (@is_read=1))
select @unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@user_id and folder_id=@folder_id
and is_read=0

GO

I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!

Any help on this matter would be greately appreciated!

Thanks,
igor

Sarav

2005-05-16, 8:23 pm

This looks like a Parameter Sniffing problem to me.

take a look at this link:
http://www.microsoft.com/technet/pr...005/recomp.mspx

regards,
sarav...

"Igor Marchenko" < igormarchenko@hotmai
l.com> wrote in message
news:OZRXncnWFHA.3464@TK2MSFTNGP10.phx.gbl...
> Hello!
>
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
>
> CREATE PROCEDURE [dbo].& #91;vsp_mail_select]

>
> @user_id int,
> @folder_id int,
> @is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @start_index int = null, --unused for now, we return everything
> @total_count int = null output, -- count of all mail in specified folder
> @unread_count int = null output -- count of unread mail in specified
> folder
>
> AS
> SET NOCOUNT ON
>
> select m1.* from mail m1(nolock) where m1.user_id=@user_id and
> folder_id=@folder_id
and ((@is_read=0 and is_read=0) or (@is_read=1))
> order by date_sent desc
> select @total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@user_id and folder_id=@folder_id
and ((is_read=0 and
> @is_read=0) or (@is_read=1))
> select @unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@user_id and folder_id=@folder_id
and is_read=0
>
> GO
>
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
>
> Any help on this matter would be greately appreciated!
>
> Thanks,
> igor
>

Igor Marchenko

2005-05-17, 1:23 pm

I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.

Igor

"Sarav" <sarav@sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
>
> take a look at this link:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
>
> regards,
> sarav...
>
> "Igor Marchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:OZRXncnWFHA.3464@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links