Greetings, all
When starting SQLServer 2005, it is possible to set the "-g" option to
reserve a minimum amount of memory for the MemToLeave region. After
starting SQLServer, is there a way to verify that the number specified
in the -g option has been recognized and used properly?
I had originally expected the Target buffer count in the DBCC
MemoryStatus command to drop when specifying a larger-than-default
MemToLeave region, and also a lowered number in the VM Reserved (KB)
value in the Memory Manager table. In my case, neither of these values
changed.
Situation:
I am using a SQL Server 2005 Enterprise Edition installation on a 32-
bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
the standard configuration of a 2GB VAS, I expected that the Buffer
Pool for this SQL server would be approximately 1.6 GB, with the
default MemToLeave region of approximately 384MB, and the initial
numbers reported by DBCC MemoryStatus were consistent with those
expectations. I changed the -g setting to reserve 1GB of memory for
MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
accordingly, but as described, such is not the case. I now suspect
that the -g option is not being recognized.
I specified the "-g 1024" parameter in the SQL Configuration Manager
under "Startup Parameters," and restarted the server immediately
after adding the option. No errors were recorded during startup.
I would appreciate any assist on this someone could offer.
-DavidHi David, check SQL Server error log to see if if -g is being used or not (It
logs this during startup)
Also, are you heavily using OLE automation objects (ex: SP_OA) or any
extended stored procedures. (Hope the obejcts are being cleared/destroyed
after it's use using SP_OADestroy) *I've seen cases in the past where it
doesn't.
However, the default value for -g inboth SQL Server 2000 and 2005 is 256 MB
and if you wish to increment this value then you'll need to do this in the
increments of 128 MB. (Try specifying -g 384 and restart SQL Server services
to see if that helps)
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fdefault.aspx
"SoonerDEW@.gmail.com" wrote:
> Greetings, all
> When starting SQLServer 2005, it is possible to set the "-g" option to
> reserve a minimum amount of memory for the MemToLeave region. After
> starting SQLServer, is there a way to verify that the number specified
> in the -g option has been recognized and used properly?
> I had originally expected the Target buffer count in the DBCC
> MemoryStatus command to drop when specifying a larger-than-default
> MemToLeave region, and also a lowered number in the VM Reserved (KB)
> value in the Memory Manager table. In my case, neither of these values
> changed.
> Situation:
> I am using a SQL Server 2005 Enterprise Edition installation on a 32-
> bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
> the standard configuration of a 2GB VAS, I expected that the Buffer
> Pool for this SQL server would be approximately 1.6 GB, with the
> default MemToLeave region of approximately 384MB, and the initial
> numbers reported by DBCC MemoryStatus were consistent with those
> expectations. I changed the -g setting to reserve 1GB of memory for
> MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
> accordingly, but as described, such is not the case. I now suspect
> that the -g option is not being recognized.
> I specified the "-g 1024" parameter in the SQL Configuration Manager
> under "Startup Parameters," and restarted the server immediately
> after adding the option. No errors were recorded during startup.
> I would appreciate any assist on this someone could offer.
> -David
>|||Hi, Saleem
Thank you so much for your assistance.
I was able to review the logs and verify that the -g option is being
recognized.
That brings me to my problem. My intent here is to free up enough
MemToLeave area to allow one of several SQLCLR procedures to run.
These procedures will run fine on a local SQLEXPRESS box, but fail
with 701 (Insufficient Memory) errors when run on one of our 32-bit
SQL Server Enterprise servers running Win 2K3 Server EE.
My investigation led me to believe that my local SQLExpress box was
actually being given a greater chunk in the MemToLeave region for
these SQLCLR procedures by virtue of it's 1GB buffer pool allocation,
leaving 1GB in MemToLeave, whereas the server, with just over 3GB of
physical RAM (but no /3GB boot.ini switch nor are AWE enabled in SQL
Server), was being given only the default of 256MB (plus the thread
stack space). I theorized that forcing MemToLeave at some rather
ridiculous value, such as 1GB (1024), would have been ample for these
procedures, but the error persists. Hence, I suspected the -g
parameter was not being accepted. This is obviously not the case.
Should I not see a difference in the buffer status and related
allocations when the -g parameter is specified?
Again, many thanks for your assistance,
David
On Oct 17, 5:41 am, Saleem Hakani
<SaleemHak...@.discussions.microsoft.com> wrote:
> Hi David, check SQL Server error log to see if if -g is being used or not (It
> logs this during startup)
> Also, are you heavily using OLE automation objects (ex: SP_OA) or any
> extended stored procedures. (Hope the obejcts are being cleared/destroyed
> after it's use using SP_OADestroy) *I've seen cases in the past where it
> doesn't.
> However, the default value for -g inboth SQL Server 2000 and 2005 is 256 MB
> and if you wish to increment this value then you'll need to do this in the
> increments of 128 MB. (Try specifying -g 384 and restart SQL Server services
> to see if that helps)
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):http://sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?r...
>
> "Sooner...@.gmail.com" wrote:
> > Greetings, all
> > When starting SQLServer 2005, it is possible to set the "-g" option to
> > reserve a minimum amount of memory for the MemToLeave region. After
> > starting SQLServer, is there a way to verify that the number specified
> > in the -g option has been recognized and used properly?
> > I had originally expected the Target buffer count in the DBCC
> > MemoryStatus command to drop when specifying a larger-than-default
> > MemToLeave region, and also a lowered number in the VM Reserved (KB)
> > value in the Memory Manager table. In my case, neither of these values
> > changed.
> > Situation:
> > I am using a SQL Server 2005 Enterprise Edition installation on a 32-
> > bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
> > the standard configuration of a 2GB VAS, I expected that the Buffer
> > Pool for this SQL server would be approximately 1.6 GB, with the
> > default MemToLeave region of approximately 384MB, and the initial
> > numbers reported by DBCC MemoryStatus were consistent with those
> > expectations. I changed the -g setting to reserve 1GB of memory for
> > MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
> > accordingly, but as described, such is not the case. I now suspect
> > that the -g option is not being recognized.
> > I specified the "-g 1024" parameter in the SQL Configuration Manager
> > under "Startup Parameters," and restarted the server immediately
> > after adding the option. No errors were recorded during startup.
> > I would appreciate any assist on this someone could offer.
> > -David- Hide quoted text -
> - Show quoted text -|||Everyone,
I have resolved this problem, and thought I should share my findings.
It turns out that the "-g" option should, indeed, be reflected as a
decrease in the size of the reserved buffer pool in SQL Server, as I
had surmised. Our servers were not honoring the "-g" parameter at
startup merely be restarting SQLServer; it was honored only after the
server itself had been rebooted. DBCC MEMORYSTATUS correctly reflected
the proper/expected buffer pool reservation size *only* after a server
reboot.
Our SQLCLR procedure now runs happily.
Thanks to all!
-David
On Oct 17, 8:28 am, Sooner...@.gmail.com wrote:
> Hi, Saleem
> Thank you so much for your assistance.
> I was able to review the logs and verify that the -g option is being
> recognized.
> That brings me to my problem. My intent here is to free up enough
> MemToLeave area to allow one of several SQLCLR procedures to run.
> These procedures will run fine on a local SQLEXPRESS box, but fail
> with 701 (Insufficient Memory) errors when run on one of our 32-bit
> SQL Server Enterprise servers running Win 2K3 Server EE.
> My investigation led me to believe that my local SQLExpress box was
> actually being given a greater chunk in the MemToLeave region for
> these SQLCLR procedures by virtue of it's 1GB buffer pool allocation,
> leaving 1GB in MemToLeave, whereas the server, with just over 3GB of
> physical RAM (but no /3GB boot.ini switch nor are AWE enabled in SQL
> Server), was being given only the default of 256MB (plus the thread
> stack space). I theorized that forcing MemToLeave at some rather
> ridiculous value, such as 1GB (1024), would have been ample for these
> procedures, but the error persists. Hence, I suspected the -g
> parameter was not being accepted. This is obviously not the case.
> Should I not see a difference in the buffer status and related
> allocations when the -g parameter is specified?
> Again, many thanks for your assistance,
> David
> On Oct 17, 5:41 am, Saleem Hakani
>
> <SaleemHak...@.discussions.microsoft.com> wrote:
> > Hi David, check SQL Server error log to see if if -g is being used or not (It
> > logs this during startup)
> > Also, are you heavily using OLE automation objects (ex: SP_OA) or any
> > extended stored procedures. (Hope the obejcts are being cleared/destroyed
> > after it's use using SP_OADestroy) *I've seen cases in the past where it
> > doesn't.
> > However, the default value for -g inboth SQL Server 2000 and 2005 is 256 MB
> > and if you wish to increment this value then you'll need to do this in the
> > increments of 128 MB. (Try specifying -g 384 and restart SQL Server services
> > to see if that helps)
> > --
> > Thank you,
> > Saleem Hakani
> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> > Articles, SQL Clinic and a lot of SQL fun.
> > Register (Free):http://sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?r...
> > "Sooner...@.gmail.com" wrote:
> > > Greetings, all
> > > When starting SQLServer 2005, it is possible to set the "-g" option to
> > > reserve a minimum amount of memory for the MemToLeave region. After
> > > starting SQLServer, is there a way to verify that the number specified
> > > in the -g option has been recognized and used properly?
> > > I had originally expected the Target buffer count in the DBCC
> > > MemoryStatus command to drop when specifying a larger-than-default
> > > MemToLeave region, and also a lowered number in the VM Reserved (KB)
> > > value in the Memory Manager table. In my case, neither of these values
> > > changed.
> > > Situation:
> > > I am using a SQL Server 2005 Enterprise Edition installation on a 32-
> > > bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
> > > the standard configuration of a 2GB VAS, I expected that the Buffer
> > > Pool for this SQL server would be approximately 1.6 GB, with the
> > > default MemToLeave region of approximately 384MB, and the initial
> > > numbers reported by DBCC MemoryStatus were consistent with those
> > > expectations. I changed the -g setting to reserve 1GB of memory for
> > > MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
> > > accordingly, but as described, such is not the case. I now suspect
> > > that the -g option is not being recognized.
> > > I specified the "-g 1024" parameter in the SQL Configuration Manager
> > > under "Startup Parameters," and restarted the server immediately
> > > after adding the option. No errors were recorded during startup.
> > > I would appreciate any assist on this someone could offer.
> > > -David- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment