cachemiss and locking

Hi,
I am troubleshotting the locking issue. And I did a profiling for this.
I am interested in cachemiss here. I found a lot of cachemiss for a single
stored procedure which is called frequently. I am not sure if this is the reason
causing the blocking. Is there anyway I can solve this problem?
Thanks,
daveIs the application, owner qualifying that stored procedure when calling? For
example, dbo.SpName, instead of just SpName? This is important, especially
when the procedure is not owned by the user calling it. For more
information: http://support.microsoft.com/default.aspx?scid=KB;en-us;q263889
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:83BA3BE7-D1E7-46E4-A9E5-2214CA78452C@.microsoft.com...
Hi,
I am troubleshotting the locking issue. And I did a profiling for this.
I am interested in cachemiss here. I found a lot of cachemiss for a single
stored procedure which is called frequently. I am not sure if this is the
reason
causing the blocking. Is there anyway I can solve this problem?
Thanks,
dave|||Thanks, Vyas. At beginning, I also thought it is dbo qualifying issue. But
Suprisingly the trace file indicates the dbo is used when the stored procedure
is called. I couldn't understand why. Are there any other issues which can
cause the [[compile]] blocking issue for a stored procedure? I am not sure if
the I can do something to increase the cache hit ratio such as increasing the
proc cache or pinning this stored procedure into cache. I searched the site
but didn't find a solution for this.
Dav
-- Narayana Vyas Kondreddi wrote: --
Is the application, owner qualifying that stored procedure when calling? Fo
example, dbo.SpName, instead of just SpName? This is important, especiall
when the procedure is not owned by the user calling it. For mor
information: http://support.microsoft.com/default.aspx?scid=KB;en-us;q26388
-
HTH
Vyas, MVP (SQL Server
http://vyaskn.tripod.com
Is .NET important for a database professional
http://vyaskn.tripod.com/poll.ht
"dave" <anonymous@.discussions.microsoft.com> wrote in messag
news:83BA3BE7-D1E7-46E4-A9E5-2214CA78452C@.microsoft.com..
Hi
I am troubleshotting the locking issue. And I did a profiling for this
I am interested in cachemiss here. I found a lot of cachemiss for a singl
stored procedure which is called frequently. I am not sure if this is th
reaso
causing the blocking. Is there anyway I can solve this problem
Thanks
dav|||I found from the trace file. The cachemiss part is in one rpc call
If I run the procedure only, exec dbo.sp_test p1 p2 in query analyzer
The procedure sp_test is hitted. But if I run
declare @.P1 in
set @.P1=declare @.P2 in
set @.P2=
exec sp_cursoropen @.P1 output, N'exec dbo.sp_test p1 p2', @.P2 outpu
The procedure is missed at beginning.
Anybody can answer why? And how to solve the problem like this
Thank
Dav