SQLAgentMail: cache

Showing posts with label cache. Show all posts

Friday, March 30, 2012

Question(s) about memory management

I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing tha
t
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.this is not bad, your queries are well parameterized and also your SPs,so th
ousands of querys are using the same plan, the problem is when you have a l
arge proc cache and a litle data cache.
--
Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message news:584554A5-
708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing tha
t
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.

Question(s) about memory management

I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing that
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.This is a multi-part message in MIME format.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
this is not bad, your queries are well parameterized and also your =SPs,so thousands of querys are using the same plan, the problem is when =you have a large proc cache and a litle data cache.
-- Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message =news:584554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm =noticing that the procedure cache is shrinking at an alarming rate and the buffer =cache is growing. This is surprising to me as this is a proc intensive server =and I know SQL Server dynamically manages the memory. My question is there =anyway to change the amount of memory allocated to the proc cache? I went =through books online, but I only saw things relating to setting the min/max =memory amounts but nothing directly related to the type of management that =I'm wanting to do.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

this is not bad, your queries are well parameterized and also your SPs,so thousands of querys are using the =same plan, the problem is when you have a large proc cache and a litle data cache.
-- MauroMCTS - SQL Server 2005
"Big Ern" wrote in message news:584=554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing =that the procedure cache is shrinking at an alarming rate and the =buffer cache is growing. This is surprising to me as this is a proc intensive =server and I know SQL Server dynamically manages the memory. My question =is there anyway to change the amount of memory allocated to the proc cache? =I went through books online, but I only saw things relating to setting =the min/max memory amounts but nothing directly related to the type of = management that I'm wanting to do.

--=_NextPart_000_02CE_01C76B0A.1C9DB110--sql

Wednesday, March 21, 2012

Question on SQL Server and the cache

I'm new to databases and I'm trying to understand how the memory cache
works with sql server (I think Oracle calls it the "Block Buffer")
I fire up sql server
I query a table (ie select * from mytable where myid = 10)
This query's plan ends up in cache?
Do the returned rows end up there too?
Does it matter if myid is an index or not for the cache to utilised?
If the query did not have a where clause, would the entire table end up in
memory?
Sorry for the dumb questions... just trying learn
TIA> This query's plan ends up in cache?
Maybe. SQL Server caches plans to avoid the compilation costs. However,
trivial plans (probably this one) are not cached since little work is needed
to compile.
> Do the returned rows end up there too?
Data are read into the buffer cache. Execution plans are kept in separate
plan cache.
> Does it matter if myid is an index or not for the cache to utilised?
No.
> If the query did not have a where clause, would the entire table end up in
> memory?
If there is sufficient buffer cache, yes. If not, cache is reused according
to (roughly) a LRU algorithm so that the most recent and frequently accessed
data stay in cache.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> I'm new to databases and I'm trying to understand how the memory cache
> works with sql server (I think Oracle calls it the "Block Buffer")
> I fire up sql server
> I query a table (ie select * from mytable where myid = 10)
> This query's plan ends up in cache?
> Do the returned rows end up there too?
> Does it matter if myid is an index or not for the cache to utilised?
> If the query did not have a where clause, would the entire table end up in
> memory?
> Sorry for the dumb questions... just trying learn
> TIA
>|||Some additional information inline...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> This query's plan ends up in cache?
> Maybe. SQL Server caches plans to avoid the compilation costs. However, trivial plans (probably
> this one) are not cached since little work is needed to compile.
Check the syscacheobjects table. Cotains one row for each plan. You can see how much a plan has been
reused, type of plan etc.
>> Do the returned rows end up there too?
> Data are read into the buffer cache. Execution plans are kept in separate plan cache.
By "Data", Dan is referring to data pages as well as index pages.
>> Does it matter if myid is an index or not for the cache to utilised?
> No.
>> If the query did not have a where clause, would the entire table end up in
>> memory?
> If there is sufficient buffer cache, yes. If not, cache is reused according to (roughly) a LRU
> algorithm so that the most recent and frequently accessed data stay in cache.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
>> I'm new to databases and I'm trying to understand how the memory cache
>> works with sql server (I think Oracle calls it the "Block Buffer")
>> I fire up sql server
>> I query a table (ie select * from mytable where myid = 10)
>> This query's plan ends up in cache?
>> Do the returned rows end up there too?
>> Does it matter if myid is an index or not for the cache to utilised?
>> If the query did not have a where clause, would the entire table end up in
>> memory?
>> Sorry for the dumb questions... just trying learn
>> TIA
>>
>|||Thanks guys
Some additional questions
What does the cache hit ratio relate to? The data or the index pages being
in memory? Or both?
By LRU algorithm, you mean the lazy writer process, correct? From my
reading, it seems that this sweeps through and ages out stale queries from
the query cache to make room for new queries. At the same time, does it age
out the data and index pages from their data and index caches that were
generated from the aged out query?
You guys are a great help. I struggle a lot because I'm more of a visual
learner (ie I need to see process flows rather than just read text). I
can't seem to find good material.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Some additional information inline...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
> >> This query's plan ends up in cache?
> >
> > Maybe. SQL Server caches plans to avoid the compilation costs.
However, trivial plans (probably
> > this one) are not cached since little work is needed to compile.
> Check the syscacheobjects table. Cotains one row for each plan. You can
see how much a plan has been
> reused, type of plan etc.
>
> >
> >> Do the returned rows end up there too?
> >
> > Data are read into the buffer cache. Execution plans are kept in
separate plan cache.
> By "Data", Dan is referring to data pages as well as index pages.
>
> >
> >> Does it matter if myid is an index or not for the cache to utilised?
> >
> > No.
> >
> >> If the query did not have a where clause, would the entire table end up
in
> >> memory?
> >
> > If there is sufficient buffer cache, yes. If not, cache is reused
according to (roughly) a LRU
> > algorithm so that the most recent and frequently accessed data stay in
cache.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> >> I'm new to databases and I'm trying to understand how the memory cache
> >> works with sql server (I think Oracle calls it the "Block Buffer")
> >>
> >> I fire up sql server
> >> I query a table (ie select * from mytable where myid = 10)
> >>
> >> This query's plan ends up in cache?
> >> Do the returned rows end up there too?
> >> Does it matter if myid is an index or not for the cache to utilised?
> >> If the query did not have a where clause, would the entire table end up
in
> >> memory?
> >>
> >> Sorry for the dumb questions... just trying learn
> >>
> >> TIA
> >>
> >>
> >
> >
>|||> What does the cache hit ratio relate to? The data or the index pages being
> in memory? Or both?
In perfmon, you have cache hit ration counters for data/index pages as well as for plan pages.
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries from
> the query cache to make room for new queries. At the same time, does it age
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
Yes. Each page has a cost associated. A bit simplified, we can say that each reference to a page
increases the cost by 1. Each time Lazywriter sweeps, the cost for each page decreases by 1. When
cost goes to 0, the page goes in the free list. The algorithm for plan pages is a bit different, but
the same principal applies. So apart for the costing algorithms, data/index pages are handled the
same way as plan pages.
> I
> can't seem to find good material.
I have a feeling that "Inside SQL Server" from MS Press, by Kalen Delaney has some good info on
this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:kqSdnZDRD4fItDnenZ2dnUVZ_vidnZ2d@.comcast.com...
> Thanks guys
> Some additional questions
> What does the cache hit ratio relate to? The data or the index pages being
> in memory? Or both?
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries from
> the query cache to make room for new queries. At the same time, does it age
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
> You guys are a great help. I struggle a lot because I'm more of a visual
> learner (ie I need to see process flows rather than just read text). I
> can't seem to find good material.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
>> Some additional information inline...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> >> This query's plan ends up in cache?
>> >
>> > Maybe. SQL Server caches plans to avoid the compilation costs.
> However, trivial plans (probably
>> > this one) are not cached since little work is needed to compile.
>> Check the syscacheobjects table. Cotains one row for each plan. You can
> see how much a plan has been
>> reused, type of plan etc.
>>
>> >
>> >> Do the returned rows end up there too?
>> >
>> > Data are read into the buffer cache. Execution plans are kept in
> separate plan cache.
>> By "Data", Dan is referring to data pages as well as index pages.
>>
>> >
>> >> Does it matter if myid is an index or not for the cache to utilised?
>> >
>> > No.
>> >
>> >> If the query did not have a where clause, would the entire table end up
> in
>> >> memory?
>> >
>> > If there is sufficient buffer cache, yes. If not, cache is reused
> according to (roughly) a LRU
>> > algorithm so that the most recent and frequently accessed data stay in
> cache.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Dodo Lurker" <none@.noemailplease> wrote in message
> news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
>> >> I'm new to databases and I'm trying to understand how the memory cache
>> >> works with sql server (I think Oracle calls it the "Block Buffer")
>> >>
>> >> I fire up sql server
>> >> I query a table (ie select * from mytable where myid = 10)
>> >>
>> >> This query's plan ends up in cache?
>> >> Do the returned rows end up there too?
>> >> Does it matter if myid is an index or not for the cache to utilised?
>> >> If the query did not have a where clause, would the entire table end up
> in
>> >> memory?
>> >>
>> >> Sorry for the dumb questions... just trying learn
>> >>
>> >> TIA
>> >>
>> >>
>> >
>> >
>

Question on SQL Server and the cache

Monday, March 12, 2012

Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss? Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!
Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss? Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>
|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>

Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is gener
ated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan,
I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you cal
l the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you
are about to do it,
just let us know. I wouldn't be surprised if cache handling is different wit
h RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss l
ike crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in s
ys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used...
.why would I still be
> getting CacheMiss' Some of these procs are only called from one specific
webservice the same way
> every time. The usage counts on some of these are over 20k so the plan do
esn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET
> application instead of as a paramterized query? We are trying to convert
everything to
> parameterized as quickly as we can. But if this is the cause, why is ther
e a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>

Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss' Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> I've got something awry in one of our servers. I'm getting SP:CacheMiss
>> like crazy in Profiler. The same procs repeatedly. The strange thing is
>> that these procs are in sys.syscacheobjects and the usagecount is growing
>> regularly so the cached plans are being used....why would I still be
>> getting CacheMiss' Some of these procs are only called from one
>> specific webservice the same way every time. The usage counts on some of
>> these are over 20k so the plan doesn't seem to be clearing it just
>> doesn't seem to be getting used every time...
>> Is it because the are being executed as "exec procname parm1, parm2,..."
>> from the .NET application instead of as a paramterized query? We are
>> trying to convert everything to parameterized as quickly as we can. But
>> if this is the cause, why is there a plan that *IS* getting used?
>> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
>> I don't believe should be happening.
>> Thanks in advance!!
>

Friday, March 30, 2012

Question(s) about memory management

Question(s) about memory management

Wednesday, March 21, 2012

Question on SQL Server and the cache

Question on SQL Server and the cache

Question on SQL Server and the cache

Monday, March 12, 2012

Question on Plan Cache

Question on Plan Cache

Question on Plan Cache

SQLAgentMail

Blog Archive

About Me