Memory managment question

Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:430150
Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe Dperfmon 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
>
>|||Hi Mike
You could actually configure the split between data and proc cache up throug
h 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 (part
icularly the Buffer Manager counters). You can get info about what's in the
procedure cache in SQL 2000 by querying the master.dbo.syscacheobjects tabl
e. In SQL 2005 you can get a fair bit of info about the memory breakdown an
d what's in the cache by querying the memory & cache related dynamic managem
ent 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 c
ould 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|||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