How to configure MemToLeave memory option?

I have been encountering errors on a couple of my SQL Servers and in
researching the problem I think that I may need to configure more
MemToLeave space. I have not been able to find any articles that
suggest how to go about calculating how much MemToLeave I need though.
I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
MemToLeave come from that 5.5 GB? How does one go about figuring out
how much MemToLeave to assign?
ThanksIt would help if you posted what those errors were so we could answer a
little more intelligently. How much memory do you have overall?
This should get you started.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1142979152.765275.3730@.e56g2000cwe.googlegroups.com...
>I have been encountering errors on a couple of my SQL Servers and in
> researching the problem I think that I may need to configure more
> MemToLeave space. I have not been able to find any articles that
> suggest how to go about calculating how much MemToLeave I need though.
> I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
> MemToLeave come from that 5.5 GB? How does one go about figuring out
> how much MemToLeave to assign?
> Thanks
>|||If you are sure it is the MemToLeave problem, you may use the -g384 server
parameter to increase from the 256M default (SQL2K). As far as I know, -g384
is max allowed for SQL2K.|||There is no limit as such that I know of.
--
Andrew J. Kelly SQL MVP
"sqlfan" <sqlfan@.discussions.microsoft.com> wrote in message
news:C68D4540-26D9-42F6-97C9-8B0CA436F75C@.microsoft.com...
> If you are sure it is the MemToLeave problem, you may use the -g384 server
> parameter to increase from the 256M default (SQL2K). As far as I
> know, -g384
> is max allowed for SQL2K.|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Some things that can contribute to this are extended stored procedures that
fragment the memtoleave Are you using any XP's? Using a lot of Blobs can
also tax the memtoleave.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143656215.037080.65060@.i40g2000cwc.googlegroups.com...
> Thanks for your reply. Sorry for the delay in responding.
> I get errors in the error log like the one below. I've researched this
> errror and some common causes are a network packet size that is too
> large or the use of the sql_variant datatype -- neither of which
> applies to us.
> I remember reading (can't find the article now) that another
> possibility is large query plans that can't fit in the regualar buffer
> pool. We do have some large query plans.
> The error I see is:
> 2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
> Free=6 Procedures=56173
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=> 334272
> 2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
> TotalPages=56173 InUsePages=23115
> 2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
> OS Reserved=5184
> OS Committed=5154
> OS In Use=3749
> Query Plan=56067 Optimizer=0
> General=6156
> Utilities=83 Connection=5036
> 2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3800 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
> 2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
> Waiting=0 Maximum=228540 Available=228540
> 2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
> Free=7088 Procedures=39982
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=> 334272
> 2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
> TotalPages=39982 InUsePages=14128
> 2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
> OS Reserved=4120
> OS Committed=4094
> OS In Use=2696
> Query Plan=48530 Optimizer=0
> General=5562
> Utilities=83 Connection=5032
> 2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3206 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
>
> Thanks!
>|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||What version of litespeed? Some of the earlier versions were know to
fragment the memtoleave and cause these issues. If you don't have the
latest version I suggest you get it and see if that helps.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143736360.517465.194290@.u72g2000cwu.googlegroups.com...
> The only XPs that I can think of would be 3rd party XPs for the SQL
> LiteSpeed backup compression utility. We take transaction log backups
> every 5 minutes for 3 different databases so that XP is called 3 times
> every 5 minutes. I don't think we use many BLOBs but I'll look in to
> that.
> Does increasing the memtoleave help with fragmentation?
>