Excessive stored procedure [COMPILE] lock

[imarchenko]

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].[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=@use r_id and
folder_id=@fold er_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=@use r_id and folder_id=@fold er_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=@use r_id and folder_id=@fold er_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

[Razvan Socol]

See:
http://support.microsoft.com/default...;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan