I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size= 65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
HillaireThere are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818095
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
Showing posts with label buffer. Show all posts
Showing posts with label buffer. Show all posts
Wednesday, March 21, 2012
Memory Warning
Memory Warning
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
There are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=
> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire
|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/default...b;en-us;818095
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
There are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=
> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire
|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/default...b;en-us;818095
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
Labels:
buffer,
contiguous,
database,
distribution,
entries,
error,
failed,
following,
logwarning,
memory,
microsoft,
mysql,
oracle,
reserve,
server,
size65536,
sql,
stolen4294940950,
warning
Memory Warning
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
HillaireThere are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=
> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/defaul...kb;en-us;818095
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
HillaireThere are certain operations that can only happen in a contiguous block of
memory and this is done in what is called the MemToLeave area. By default
you only have about 256MB allocated for this area and you must have run low.
If this repeats you might considering adding the -g parameter to SQL Server
upon startup and reserve more than 256MB. Have a look at "startup options"
in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
> I have the following entries in my sql error log:
> WARNING: Failed to reserve contiguous memory of Size=
> 65536.
> Buffer Distribution: Stolen=4294940950 Free=112
> Procedures=109559
> Inram=0 Dirty=1167 Kept=0
> I/O=0, Latched=206, Other=123974
> Buffer Counts: Commited=208672 Target=208672
> Hashed=125347
> InternalReservation=346 ExternalReservation=58 Min
> Free=128
> Procedure Cache: TotalProcs=10484 TotalPages=109559
> InUsePages=14527
> Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
> OS Committed=36671
> OS In Use=29121
> Query Plan=108697 Optimizer=0
> General=3420
> Utilities=6 Connection=68
> Global Memory Objects: Resource=1808 Locks=77
> SQLCache=653 Replication=2
> LockBytes=2 ServerGlobal=46
> Xact=18
> Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
> Available=165320
> Can anyone help diagnose the problem?
> Thanks,
> Hillaire|||What service pack are on for SQL Server? See if this applies to you:
http://support.microsoft.com/defaul...kb;en-us;818095
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c46e65$82f1e660$a601280a@.phx.gbl...
I have the following entries in my sql error log:
WARNING: Failed to reserve contiguous memory of Size=
65536.
Buffer Distribution: Stolen=4294940950 Free=112
Procedures=109559
Inram=0 Dirty=1167 Kept=0
I/O=0, Latched=206, Other=123974
Buffer Counts: Commited=208672 Target=208672
Hashed=125347
InternalReservation=346 ExternalReservation=58 Min
Free=128
Procedure Cache: TotalProcs=10484 TotalPages=109559
InUsePages=14527
Dynamic Memory Manager: Stolen=83213 OS Reserved=40456
OS Committed=36671
OS In Use=29121
Query Plan=108697 Optimizer=0
General=3420
Utilities=6 Connection=68
Global Memory Objects: Resource=1808 Locks=77
SQLCache=653 Replication=2
LockBytes=2 ServerGlobal=46
Xact=18
Query Memory Manager: Grants=1 Waiting=0 Maximum=165378
Available=165320
Can anyone help diagnose the problem?
Thanks,
Hillaire
Labels:
buffer,
contiguous,
database,
distribution,
entries,
error,
failed,
following,
logwarning,
memory,
microsoft,
mysql,
oracle,
reserve,
server,
size65536,
sql,
stolen4294940950,
warning
Monday, February 20, 2012
Memory managment question
Hello,
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" 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 =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
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
Subscribe to:
Posts (Atom)