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 re
ason
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/defaul...B;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 procedu
re
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 th
e
proc cache or pinning this stored procedure into cache. I searched the site
but didn't find a solution for this.
Dave
-- Narayana Vyas Kondreddi wrote: --
Is 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/defaul...B;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|||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 int
set @.P1=0
declare @.P2 int
set @.P2=8
exec sp_cursoropen @.P1 output, N'exec dbo.sp_test p1 p2', @.P2 output
The procedure is missed at beginning.
Anybody can answer why? And how to solve the problem like this?
Thanks
Dave