I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe DThis is a multi-part message in MIME format.
--020902000504060103040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
perfmon is a good place to get info about the current memory breakdown
(particularly the Buffer Manager counters). You can get info about
what's in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of
info about the memory breakdown and what's in the cache by querying the
memory & cache related dynamic management views & functions (eg.
sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of
this is undocumented - it's all spelled out fairly clearly in BOL I think.
As for managing the split between data cache and procedure cache, you
can't. It's managed internally and you have no control over it (from
memory you could configure the split in SQL Server 4.21 but not since).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Joe D wrote:
>Hello,
> I was wondering if there is any 'un-documented' infomation on managing
>SQL's memory cache, such as buffer and procedure?
>TIA
>Joe D
>
>
--020902000504060103040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>perfmon is a good place to get info about the current memory
breakdown (particularly the Buffer Manager counters). You can get info
about what's in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit
of info about the memory breakdown and what's in the cache by querying
the memory & cache related dynamic management views & functions
(eg. sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of
this is undocumented - it's all spelled out fairly clearly in BOL I
think.<br>
<br>
As for managing the split between data cache and procedure cache, you
can't. It's managed internally and you have no control over it (from
memory you could configure the split in SQL Server 4.21 but not since).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Joe D wrote:
<blockquote cite="mide1e8vo$11v3$1@.sxnews1.qg.com" type="cite">
<pre wrap="">Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe D
</pre>
</blockquote>
</body>
</html>
--020902000504060103040400--|||This is a multi-part message in MIME format.
--=_NextPart_000_04FB_01C65CC5.B7FEA730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Mike
You could actually configure the split between data and proc cache up =through 6.5. The big re-architecture of the product happened in version =7.
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OyO3WvPXGHA.1192@.TK2MSFTNGP03.phx.gbl...
perfmon is a good place to get info about the current memory breakdown =(particularly the Buffer Manager counters). You can get info about =what's in the procedure cache in SQL 2000 by querying the =master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of =info about the memory breakdown and what's in the cache by querying the =memory & cache related dynamic management views & functions (eg. =sys.dm_os_memory_clerks, sys.dm_exec_cached_plans, =sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of =this is undocumented - it's all spelled out fairly clearly in BOL I =think.
As for managing the split between data cache and procedure cache, you =can't. It's managed internally and you have no control over it (from =memory you could configure the split in SQL Server 4.21 but not since).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Joe D wrote: Hello,
I was wondering if there is any 'un-documented' infomation on =managing SQL's memory cache, such as buffer and procedure?
TIA
Joe D
--=_NextPart_000_04FB_01C65CC5.B7FEA730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Mike
You could actually configure the split =between data and proc cache up through 6.5. The big re-architecture of the product =happened in version 7.
-- HTHKalen Delaney, SQL Server MVPhttp://www.solidqualitylearning.com">www.solidqualitylearning.com=
"Mike Hodgson"
--mike =hodgsonhttp://sqlnerd.blogspot.com Joe D wrote: Hello,
I was wondering if there is any 'un-documented' infomation on =managing SQL's memory cache, such as buffer and procedure?
TIA
Joe D
--=_NextPart_000_04FB_01C65CC5.B7FEA730--|||Thank you both for verifying what I had thought was the case to be. I have a
client that I'm working with to help solve some issues and I needed a 2nd
opinion/verification of how memory was used by SQL.
Have a good day.
JD
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eIg1vBQXGHA.3684@.TK2MSFTNGP05.phx.gbl...
Hi Mike
You could actually configure the split between data and proc cache up
through 6.5. The big re-architecture of the product happened in version 7.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:OyO3WvPXGHA.1192@.TK2MSFTNGP03.phx.gbl...
perfmon is a good place to get info about the current memory breakdown
(particularly the Buffer Manager counters). You can get info about what's
in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of
info about the memory breakdown and what's in the cache by querying the
memory & cache related dynamic management views & functions (eg.
sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of this is
undocumented - it's all spelled out fairly clearly in BOL I think.
As for managing the split between data cache and procedure cache, you can't.
It's managed internally and you have no control over it (from memory you
could configure the split in SQL Server 4.21 but not since).
--
mike hodgson
http://sqlnerd.blogspot.com
Joe D wrote:
Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe D|||Join the Club JD.
I've had this customer who knew just enough information to be dangerous
but not enough to really know what they where talking about. It seems
no matter what I have to tell them or show them, they think they know a
better way. Even though that way goes against all the data & info that
I show them.
Billy
No comments:
Post a Comment