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=@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
