Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
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? Thanks
Post Follow-up to this message
Re: How to configure MemToLeave memory option?It 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/d...ev_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 >
Post Follow-up to this message RE: How to configure MemToLeave memory option?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, -g38 4 is max allowed for SQL2K.
Post Follow-up to this message Re: How to configure MemToLeave memory option?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@microso ft.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.
Post Follow-up to this message Re: How to configure MemToLeave memory option?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!
Post Follow-up to this message Re: How to configure MemToLeave memory option?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!
Post Follow-up to this message Re: How to configure MemToLeave memory option?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! >
Post Follow-up to this message Re: How to configure MemToLeave memory option?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?
Post Follow-up to this message Re: How to configure MemToLeave memory option?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?
Post Follow-up to this message Re: How to configure MemToLeave memory option?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? >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
