EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:

> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:

> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
That's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:

> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David
|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

Excessive stored procedure [COMPILE] lock

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
This looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pro...05/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.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].[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
>
|||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/pro...05/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

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 somethin
g
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,
igorThis 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@.hotmail.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 qualifyin
g
> 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
>|||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@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

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,
IgorSee:
http://support.microsoft.com/defaul...B;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan

Excessive stored procedure [COMPILE] lock

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,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.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].[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
>|||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/prodtechnol/sql/2005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.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].[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
>

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2.How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.
Have a look at these:
http://support.microsoft.com/default...;en-us;Q243586
http://www.sql-server-performance.co...recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/defaul...b;en-us;Q243586
http://www.sql-server-performance.c..._recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive Latch Wait

Good evening,
Does anyone have advice on how to trim down excessive latch wait time?
One particular query (stored procedure) is to blame for almost all of it.
This query is joining 7 tables... 1 of which is joined twice... another of
which has several million rows in it.
The query has nolocks throughout...
Thanks in advance.
~lbIt's possible that parallelism is the problem here. Try adding OPTION
(MAXDOP 1) as the very last line in your SELECT.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Lonnye Blake Bower" <lbower@.excellerx.com> wrote in message
news:eq4zxN0KFHA.732@.TK2MSFTNGP12.phx.gbl...
Good evening,
Does anyone have advice on how to trim down excessive latch wait time?
One particular query (stored procedure) is to blame for almost all of it.
This query is joining 7 tables... 1 of which is joined twice... another of
which has several million rows in it.
The query has nolocks throughout...
Thanks in advance.
~lb

Excel Pop-up

Hi,

In a particular report, i've got the following pop-up when opening the excel export:

http://img515.imageshack.us/img515/9868/xlerror5ye.jpg

(translation from french: "Name already used - Name can't be identical to a predefined name").

It seems that there is a problem with names generated automatically for cells references.

When I suppress report's header, the pop-up doesn't appear.

Any explanation for this behaviour ?

Thanks in advance.

Grgory.

I have the same problem !!

Excel Pop-up

Hi,

In a particular report, i've got the following pop-up when opening the excel export:

http://img515.imageshack.us/img515/9868/xlerror5ye.jpg

(translation from french: "Name already used - Name can't be identical to a predefined name").

It seems that there is a problem with names generated automatically for cells references.

When I suppress report's header, the pop-up doesn't appear.

Any explanation for this behaviour ?

Thanks in advance.

Grgory.

I have the same problem !!