Sql Memory Leaks: memtoleave

Showing posts with label memtoleave. Show all posts

Wednesday, March 21, 2012

MemToLeave in SS2KEE with AWE

How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how to
see it by using win perfmon?
thanks
lzhu@.dba1.com
Hi
Please visit at http://www.sql-server-performance.com/awe_memory.asp
"lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how
to
> see it by using win perfmon?
> thanks
> lzhu@.dba1.com
|||Actually I believe the default is 256MB.
Andrew J. Kelly SQL MVP
"lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how
> to
> see it by using win perfmon?
> thanks
> lzhu@.dba1.com
|||256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
using AWE.
"Andrew J. Kelly" wrote:

> Actually I believe the default is 256MB.
> --
> Andrew J. Kelly SQL MVP
>
> "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>
>
|||I've been there many times ;). Nowhere mentioned MemtoLeave for AWE.
thanks
"Uri Dimant" wrote:

> Hi
> Please visit at http://www.sql-server-performance.com/awe_memory.asp
> "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> to
>
>
|||The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256MB for SQL Server 2000. This is documented in SQL Server BOL topic 'Using Startup Parameters':
Specifies the amount of virtual address space (in megabytes) SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. This is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).
The values are not modified when AWE is enabled. There are no counters in PerfMon to show this value.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
using AWE.
"Andrew J. Kelly" wrote:

> Actually I believe the default is 256MB.
> --
> Andrew J. Kelly SQL MVP
>
> "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>
>
|||Thanks for the reply. Interestingly there are different numbers ... Both Ken
Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
256mb for sql7.
"Ryan Stonecipher [MSFT]" wrote:

> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256MB for SQL Server 2000. This is documented in SQL Server BOL topic 'Using Startup Parameters':
> Specifies the amount of virtual address space (in megabytes) SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. This is the area used by SQL Server for loading items such as exte
nded procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).[vbcol=seagreen]
> The values are not modified when AWE is enabled. There are no counters in PerfMon to show this value.
> Thanks,
> Ryan Stonecipher
> SQL Server Storage Engine
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
> using AWE.
> "Andrew J. Kelly" wrote:
|||I have seen this stated both ways myself but I do believe as Ryan stated it
is 256MB for 2000.
Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:B782B0E4-C276-47A9-959F-64C47F3B1A1F@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. Interestingly there are different numbers ... Both
> Ken
> Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
> 256mb for sql7.
> "Ryan Stonecipher [MSFT]" wrote:
|||A follow up question: I'm using 8GB with AWE and have set sql max mem to 6GB.
Does this mean that MemtoLeave will come from 2GB which I left for OS and 6GB
is resvered sql BPOOL?
thanks
lzhu
"Ryan Stonecipher [MSFT]" wrote:

> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256MB for SQL Server 2000. This is documented in SQL Server BOL topic 'Using Startup Parameters':
> Specifies the amount of virtual address space (in megabytes) SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. This is the area used by SQL Server for loading items such as exte
nded procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).[vbcol=seagreen]
> The values are not modified when AWE is enabled. There are no counters in PerfMon to show this value.
> Thanks,
> Ryan Stonecipher
> SQL Server Storage Engine
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
> using AWE.
> "Andrew J. Kelly" wrote:
|||No the memtoleave is always taken from the 2GB of directly addressable
memory for SQL Server. The OS has it's own 2GB. AWE has no bearing on this
and the only thing within sql server that can use AWE memory (or memory
above 2 or 3GB depending on /3GB) is the data cache. Everything else must
come from the directly addressable memory.
Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:C32428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...[vbcol=seagreen]
>A follow up question: I'm using 8GB with AWE and have set sql max mem to
>6GB.
> Does this mean that MemtoLeave will come from 2GB which I left for OS and
> 6GB
> is resvered sql BPOOL?
> thanks
> lzhu
> "Ryan Stonecipher [MSFT]" wrote:

MemToLeave in SS2KEE with AWE

The default value in SQL Server =7.0 is 128 MB, and was adjusted up to 256MB for SQL Server 2000. This is =documented in SQL Server BOL topic 'Using Startup Parameters':
Specifies the amount of virtual address space (in megabytes) SQL =Server will leave available for memory allocations within the SQL Server =process, but outside the SQL Server memory pool. This is the area used by SQL Server =for loading items such as extended procedure .dll files, the OLE DB =providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).
The values are not modified =when AWE is enabled. There are no counters in PerfMon to show this =value.
Thanks,
Ryan Stonecipher
SQL Server Storage =Engine
"lzhu" wrote in message news:631=5334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when = using AWE."Andrew J. Kelly" wrote:> Actually I =believe the default is 256MB.> > -- > Andrew J. =Kelly SQL MVP> > > "lzhu@.dba1.com" wrote in message > news:34B=AFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...> > How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how > > to> > see it by using win = perfmon?> > thanks> > lzhu@.dba1.com > > >

--=_NextPart_000_0087_01C4BB43.16A63210--|||Thanks for the reply. Interestingly there are different numbers ... Both Ken
Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
256mb for sql7.
"Ryan Stonecipher [MSFT]" wrote:
> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256MB for SQL Server 2000. This is documented in SQL Server BOL topic 'Using Startup Parameters':
> Specifies the amount of virtual address space (in megabytes) SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. This is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).
> The values are not modified when AWE is enabled. There are no counters in PerfMon to show this value.
> Thanks,
> Ryan Stonecipher
> SQL Server Storage Engine
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
> using AWE.
> "Andrew J. Kelly" wrote:
> > Actually I believe the default is 256MB.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how
> > > to
> > > see it by using win perfmon?
> > > thanks
> > > lzhu@.dba1.com
> >
> >
> >|||I have seen this stated both ways myself but I do believe as Ryan stated it
is 256MB for 2000.
--
Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:B782B0E4-C276-47A9-959F-64C47F3B1A1F@.microsoft.com...
> Thanks for the reply. Interestingly there are different numbers ... Both
> Ken
> Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
> 256mb for sql7.
> "Ryan Stonecipher [MSFT]" wrote:
>> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
>> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
>> 'Using Startup Parameters':
>> Specifies the amount of virtual address space (in megabytes) SQL Server
>> will leave available for memory allocations within the SQL Server
>> process, but outside the SQL Server memory pool. This is the area used by
>> SQL Server for loading items such as extended procedure .dll files, the
>> OLE DB providers referenced by distributed queries, and automation
>> objects referenced in Transact-SQL statements. The default is 256
>> megabytes (MB).
>> The values are not modified when AWE is enabled. There are no counters
>> in PerfMon to show this value.
>> Thanks,
>> Ryan Stonecipher
>> SQL Server Storage Engine
>> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
>> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
>> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
>> when
>> using AWE.
>> "Andrew J. Kelly" wrote:
>> > Actually I believe the default is 256MB.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
>> message
>> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB?
>> And how
>> > > to
>> > > see it by using win perfmon?
>> > > thanks
>> > > lzhu@.dba1.com
>> >
>> >
>> >|||A follow up question: I'm using 8GB with AWE and have set sql max mem to 6GB.
Does this mean that MemtoLeave will come from 2GB which I left for OS and 6GB
is resvered sql BPOOL?
thanks
lzhu
"Ryan Stonecipher [MSFT]" wrote:
> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256MB for SQL Server 2000. This is documented in SQL Server BOL topic 'Using Startup Parameters':
> Specifies the amount of virtual address space (in megabytes) SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. This is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).
> The values are not modified when AWE is enabled. There are no counters in PerfMon to show this value.
> Thanks,
> Ryan Stonecipher
> SQL Server Storage Engine
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
> using AWE.
> "Andrew J. Kelly" wrote:
> > Actually I believe the default is 256MB.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how
> > > to
> > > see it by using win perfmon?
> > > thanks
> > > lzhu@.dba1.com
> >
> >
> >|||No the memtoleave is always taken from the 2GB of directly addressable
memory for SQL Server. The OS has it's own 2GB. AWE has no bearing on this
and the only thing within sql server that can use AWE memory (or memory
above 2 or 3GB depending on /3GB) is the data cache. Everything else must
come from the directly addressable memory.
--
Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:C32428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...
>A follow up question: I'm using 8GB with AWE and have set sql max mem to
>6GB.
> Does this mean that MemtoLeave will come from 2GB which I left for OS and
> 6GB
> is resvered sql BPOOL?
> thanks
> lzhu
> "Ryan Stonecipher [MSFT]" wrote:
>> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
>> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
>> 'Using Startup Parameters':
>> Specifies the amount of virtual address space (in megabytes) SQL Server
>> will leave available for memory allocations within the SQL Server
>> process, but outside the SQL Server memory pool. This is the area used by
>> SQL Server for loading items such as extended procedure .dll files, the
>> OLE DB providers referenced by distributed queries, and automation
>> objects referenced in Transact-SQL statements. The default is 256
>> megabytes (MB).
>> The values are not modified when AWE is enabled. There are no counters
>> in PerfMon to show this value.
>> Thanks,
>> Ryan Stonecipher
>> SQL Server Storage Engine
>> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
>> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
>> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
>> when
>> using AWE.
>> "Andrew J. Kelly" wrote:
>> > Actually I believe the default is 256MB.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
>> message
>> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB?
>> And how
>> > > to
>> > > see it by using win perfmon?
>> > > thanks
>> > > lzhu@.dba1.com
>> >
>> >
>> >|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C4BB66.6640D640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I see the confusion: the default value of -g is 256MB in SQL2K. =However, there is an additional amount of space reserved for thread =stacks. By default, we reserve space for 255 worker threads at 512KB =per thread stack, leaving an additional 128MB of space in MTL. That's =the 384MB that Ken's book refers to. (It's the same in SQL 7, resulting =in 256MB of MTL space.)
Sorry for the confusion.
Thanks,
--R
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message =news:OvMj865uEHA.1404@.TK2MSFTNGP11.phx.gbl...
No the memtoleave is always taken from the 2GB of directly addressable =
memory for SQL Server. The OS has it's own 2GB. AWE has no bearing =on this and the only thing within sql server that can use AWE memory (or =memory above 2 or 3GB depending on /3GB) is the data cache. Everything else =must come from the directly addressable memory.
-- Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:C32428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...
>A follow up question: I'm using 8GB with AWE and have set sql max mem =to >6GB.
> Does this mean that MemtoLeave will come from 2GB which I left for =OS and > 6GB
> is resvered sql BPOOL?
> thanks
> lzhu
>
> "Ryan Stonecipher [MSFT]" wrote:
>
>> The default value in SQL Server 7.0 is 128 MB, and was adjusted up =to >> 256MB for SQL Server 2000. This is documented in SQL Server BOL =topic >> 'Using Startup Parameters':
>>
>> Specifies the amount of virtual address space (in megabytes) SQL =Server >> will leave available for memory allocations within the SQL Server >> process, but outside the SQL Server memory pool. This is the area =used by >> SQL Server for loading items such as extended procedure .dll files, =the >> OLE DB providers referenced by distributed queries, and automation >> objects referenced in Transact-SQL statements. The default is 256 >> megabytes (MB).
>>
>> The values are not modified when AWE is enabled. There are no =counters >> in PerfMon to show this value.
>>
>> Thanks,
>> Ryan Stonecipher
>> SQL Server Storage Engine
>> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message >> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
>> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's =the same >> when
>> using AWE.
>>
>> "Andrew J. Kelly" wrote:
>>
>> > Actually I believe the default is 256MB.
>> >
>> > -- >> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote =in >> message
>> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>> > > How much RAM does sql2K EE with AWE set by default? Is it =384MB? >> And how
>> > > to
>> > > see it by using win perfmon?
>> > > thanks
>> > > lzhu@.dba1.com
>> >
>> >
>> >
--=_NextPart_000_0008_01C4BB66.6640D640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I see the confusion: the default value of -g =is 256MB in SQL2K. However, there is an additional amount of space reserved =for thread stacks. By default, we reserve space for 255 worker threads at =512KB per thread stack, leaving an additional 128MB of space in MTL. That's =the 384MB that Ken's book refers to. (It's the same in SQL 7, =resulting in 256MB of MTL space.)
Sorry for the confusion.
Thanks,
--R
"Andrew J. Kelly" wrote in message news:OvMj865uEHA.1404=@.TK2MSFTNGP11.phx.gbl...No the memtoleave is always taken from the 2GB of directly addressable =memory for SQL Server. The OS has it's own 2GB. AWE has no =bearing on this and the only thing within sql server that can use AWE memory =(or memory above 2 or 3GB depending on /3GB) is the data cache. Everything else must come from the directly addressable =memory.-- Andrew J. Kelly SQL MVP"lzhu" wrote in message news:C32=428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...>A follow up question: I'm using 8GB with AWE and have set sql max mem to = >6GB.> Does this mean that MemtoLeave will come from 2GB =which I left for OS and > 6GB> is resvered sql BPOOL?> thanks> lzhu>> "Ryan Stonecipher [MSFT]" wrote:>> The default value in SQL Server 7.0 is 128 =MB, and was adjusted up to > 256MB for SQL Server 2000. This =is documented in SQL Server BOL topic > 'Using Startup Parameters':>> Specifies the amount of virtual =address space (in megabytes) SQL Server > will leave available for =memory allocations within the SQL Server > process, but outside =the SQL Server memory pool. This is the area used by > SQL Server =for loading items such as extended procedure .dll files, the > =OLE DB providers referenced by distributed queries, and automation => objects referenced in Transact-SQL statements. The default is 256 => megabytes (MB).>> The values are not modified =when AWE is enabled. There are no counters > in PerfMon to =show this value.>> Thanks,> Ryan Stonecipher> SQL Server Storage =Engine> "lzhu" wrote in message > news:631=5334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...> &=nbsp; 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the =same > when> using AWE.>> "Andrew J. Kelly" wrote:>> > Actually I believe the =default is 256MB.> >> > -- = > > Andrew J. Kelly SQL MVP> >> >> > "lzhu@.dba1.com" wrote in > message> > news:34B=AFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...> &=nbsp; > > How much RAM does sql2K EE with AWE set by default? Is =it 384MB? > And how> > > to> > > see it by using win perfmon?> > > =thanks> > > lzhu@.dba1.com> >> >> >

--=_NextPart_000_0008_01C4BB66.6640D640--|||The discrepancy is probably because there are two components to MemToLeave.,
Most people just think of MemToLeave as being for the special memory
allocations outside of the buffer pool, and that is 256MB in SQL 2000.
However, there is another component which is the worker thread stacks, which
is .5MB for each work thread. The default number of worker threads is 255,
so that would give us another 128MB, with a total of 384. Since the space
for worker threads is variable is you reconfigure, most people leave it out
of the discussion and only talk about the MemToLeave aread of large
allocations.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:B782B0E4-C276-47A9-959F-64C47F3B1A1F@.microsoft.com...
> Thanks for the reply. Interestingly there are different numbers ... Both
> Ken
> Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
> 256mb for sql7.
> "Ryan Stonecipher [MSFT]" wrote:
>> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
>> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
>> 'Using Startup Parameters':
>> Specifies the amount of virtual address space (in megabytes) SQL Server
>> will leave available for memory allocations within the SQL Server
>> process, but outside the SQL Server memory pool. This is the area used by
>> SQL Server for loading items such as extended procedure .dll files, the
>> OLE DB providers referenced by distributed queries, and automation
>> objects referenced in Transact-SQL statements. The default is 256
>> megabytes (MB).
>> The values are not modified when AWE is enabled. There are no counters
>> in PerfMon to show this value.
>> Thanks,
>> Ryan Stonecipher
>> SQL Server Storage Engine
>> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
>> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
>> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
>> when
>> using AWE.
>> "Andrew J. Kelly" wrote:
>> > Actually I believe the default is 256MB.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
>> message
>> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB?
>> And how
>> > > to
>> > > see it by using win perfmon?
>> > > thanks
>> > > lzhu@.dba1.com
>> >
>> >
>> >|||Very good point Kalen. Thanks for bringing that up.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23tFdBJ6uEHA.1264@.TK2MSFTNGP12.phx.gbl...
> The discrepancy is probably because there are two components to
> MemToLeave., Most people just think of MemToLeave as being for the special
> memory allocations outside of the buffer pool, and that is 256MB in SQL
> 2000. However, there is another component which is the worker thread
> stacks, which is .5MB for each work thread. The default number of worker
> threads is 255, so that would give us another 128MB, with a total of 384.
> Since the space for worker threads is variable is you reconfigure, most
> people leave it out of the discussion and only talk about the MemToLeave
> aread of large allocations.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
> news:B782B0E4-C276-47A9-959F-64C47F3B1A1F@.microsoft.com...
>> Thanks for the reply. Interestingly there are different numbers ... Both
>> Ken
>> Hendson's book and an artical on sqljunkies are saying 384mb for sql2k
>> and
>> 256mb for sql7.
>> "Ryan Stonecipher [MSFT]" wrote:
>> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
>> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
>> 'Using Startup Parameters':
>> Specifies the amount of virtual address space (in megabytes) SQL Server
>> will leave available for memory allocations within the SQL Server
>> process, but outside the SQL Server memory pool. This is the area used
>> by SQL Server for loading items such as extended procedure .dll files,
>> the OLE DB providers referenced by distributed queries, and automation
>> objects referenced in Transact-SQL statements. The default is 256
>> megabytes (MB).
>> The values are not modified when AWE is enabled. There are no counters
>> in PerfMon to show this value.
>> Thanks,
>> Ryan Stonecipher
>> SQL Server Storage Engine
>> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
>> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
>> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the
>> same when
>> using AWE.
>> "Andrew J. Kelly" wrote:
>> > Actually I believe the default is 256MB.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
>> message
>> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB?
>> And how
>> > > to
>> > > see it by using win perfmon?
>> > > thanks
>> > > lzhu@.dba1.com
>> >
>> >
>> >
>|||I understand Memtoleave has to be in the 2GB addressable mem. Given that I've
allocated 6GB (out 8GB) to sql via max mem setting, is this 6GB totally for
BPOOL and 128MB of Memtoleave (for work threads)? and the rest 256MB
memtoleave won't come from this 6GB. Am I right?
thanks
"Andrew J. Kelly" wrote:
> No the memtoleave is always taken from the 2GB of directly addressable
> memory for SQL Server. The OS has it's own 2GB. AWE has no bearing on this
> and the only thing within sql server that can use AWE memory (or memory
> above 2 or 3GB depending on /3GB) is the data cache. Everything else must
> come from the directly addressable memory.
> --
> Andrew J. Kelly SQL MVP
>
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
> news:C32428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...
> >A follow up question: I'm using 8GB with AWE and have set sql max mem to
> >6GB.
> > Does this mean that MemtoLeave will come from 2GB which I left for OS and
> > 6GB
> > is resvered sql BPOOL?
> > thanks
> > lzhu
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> >> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
> >> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
> >> 'Using Startup Parameters':
> >>
> >> Specifies the amount of virtual address space (in megabytes) SQL Server
> >> will leave available for memory allocations within the SQL Server
> >> process, but outside the SQL Server memory pool. This is the area used by
> >> SQL Server for loading items such as extended procedure .dll files, the
> >> OLE DB providers referenced by distributed queries, and automation
> >> objects referenced in Transact-SQL statements. The default is 256
> >> megabytes (MB).
> >>
> >> The values are not modified when AWE is enabled. There are no counters
> >> in PerfMon to show this value.
> >>
> >> Thanks,
> >> Ryan Stonecipher
> >> SQL Server Storage Engine
> >> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
> >> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> >> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
> >> when
> >> using AWE.
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> > Actually I believe the default is 256MB.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
> >> message
> >> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> >> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB?
> >> And how
> >> > > to
> >> > > see it by using win perfmon?
> >> > > thanks
> >> > > lzhu@.dba1.com
> >> >
> >> >
> >> >
>
>|||thanks for the clarification! Is 'Inside sql2005' coming soon?;)
"Kalen Delaney" wrote:
> The discrepancy is probably because there are two components to MemToLeave.,
> Most people just think of MemToLeave as being for the special memory
> allocations outside of the buffer pool, and that is 256MB in SQL 2000.
> However, there is another component which is the worker thread stacks, which
> is .5MB for each work thread. The default number of worker threads is 255,
> so that would give us another 128MB, with a total of 384. Since the space
> for worker threads is variable is you reconfigure, most people leave it out
> of the discussion and only talk about the MemToLeave aread of large
> allocations.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
> news:B782B0E4-C276-47A9-959F-64C47F3B1A1F@.microsoft.com...
> > Thanks for the reply. Interestingly there are different numbers ... Both
> > Ken
> > Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
> > 256mb for sql7.
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> >> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
> >> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
> >> 'Using Startup Parameters':
> >>
> >> Specifies the amount of virtual address space (in megabytes) SQL Server
> >> will leave available for memory allocations within the SQL Server
> >> process, but outside the SQL Server memory pool. This is the area used by
> >> SQL Server for loading items such as extended procedure .dll files, the
> >> OLE DB providers referenced by distributed queries, and automation
> >> objects referenced in Transact-SQL statements. The default is 256
> >> megabytes (MB).
> >>
> >> The values are not modified when AWE is enabled. There are no counters
> >> in PerfMon to show this value.
> >>
> >> Thanks,
> >> Ryan Stonecipher
> >> SQL Server Storage Engine
> >> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
> >> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> >> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
> >> when
> >> using AWE.
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> > Actually I believe the default is 256MB.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
> >> message
> >> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> >> > > How much RAM does sql2K EE with AWE set by default? Is it 384MB?
> >> And how
> >> > > to
> >> > > see it by using win perfmon?
> >> > > thanks
> >> > > lzhu@.dba1.com
> >> >
> >> >
> >> >
>
>|||Hi Izhu
This is not quite accurate. Ken Henderson's SQL Server Architecture book has
the most detailed description of SQL Server memory management. I learn
something new every time I read the memory chapter. Here is an exerpt from
it:
http://msdn.microsoft.com/SQL/sqlarchitecture/?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:0933B681-31AF-4180-971B-327EBFE0B5B6@.microsoft.com...
>I understand Memtoleave has to be in the 2GB addressable mem. Given that
>I've
> allocated 6GB (out 8GB) to sql via max mem setting, is this 6GB totally
> for
> BPOOL and 128MB of Memtoleave (for work threads)? and the rest 256MB
> memtoleave won't come from this 6GB. Am I right?
> thanks
>
> "Andrew J. Kelly" wrote:
>> No the memtoleave is always taken from the 2GB of directly addressable
>> memory for SQL Server. The OS has it's own 2GB. AWE has no bearing on
>> this
>> and the only thing within sql server that can use AWE memory (or memory
>> above 2 or 3GB depending on /3GB) is the data cache. Everything else
>> must
>> come from the directly addressable memory.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
>> news:C32428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...
>> >A follow up question: I'm using 8GB with AWE and have set sql max mem to
>> >6GB.
>> > Does this mean that MemtoLeave will come from 2GB which I left for OS
>> > and
>> > 6GB
>> > is resvered sql BPOOL?
>> > thanks
>> > lzhu
>> >
>> > "Ryan Stonecipher [MSFT]" wrote:
>> >
>> >> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to
>> >> 256MB for SQL Server 2000. This is documented in SQL Server BOL topic
>> >> 'Using Startup Parameters':
>> >>
>> >> Specifies the amount of virtual address space (in megabytes) SQL
>> >> Server
>> >> will leave available for memory allocations within the SQL Server
>> >> process, but outside the SQL Server memory pool. This is the area used
>> >> by
>> >> SQL Server for loading items such as extended procedure .dll files,
>> >> the
>> >> OLE DB providers referenced by distributed queries, and automation
>> >> objects referenced in Transact-SQL statements. The default is 256
>> >> megabytes (MB).
>> >>
>> >> The values are not modified when AWE is enabled. There are no
>> >> counters
>> >> in PerfMon to show this value.
>> >>
>> >> Thanks,
>> >> Ryan Stonecipher
>> >> SQL Server Storage Engine
>> >> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message
>> >> news:6315334A-491C-4B35-B1E8-6667E9A55B90@.microsoft.com...
>> >> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the
>> >> same
>> >> when
>> >> using AWE.
>> >>
>> >> "Andrew J. Kelly" wrote:
>> >>
>> >> > Actually I believe the default is 256MB.
>> >> >
>> >> > --
>> >> > Andrew J. Kelly SQL MVP
>> >> >
>> >> >
>> >> > "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in
>> >> message
>> >> > news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>> >> > > How much RAM does sql2K EE with AWE set by default? Is it
>> >> 384MB?
>> >> And how
>> >> > > to
>> >> > > see it by using win perfmon?
>> >> > > thanks
>> >> > > lzhu@.dba1.com
>> >> >
>> >> >
>> >> >
>>

MemToLeave in SS2KEE with AWE

How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how t
o
see it by using win perfmon?
thanks
lzhu@.dba1.comHi
Please visit at http://www.sql-server-performance.com/awe_memory.asp
"lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how
to
> see it by using win perfmon?
> thanks
> lzhu@.dba1.com|||Actually I believe the default is 256MB.
Andrew J. Kelly SQL MVP
"lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> How much RAM does sql2K EE with AWE set by default? Is it 384MB? And how
> to
> see it by using win perfmon?
> thanks
> lzhu@.dba1.com|||256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
using AWE.
"Andrew J. Kelly" wrote:

> Actually I believe the default is 256MB.
> --
> Andrew J. Kelly SQL MVP
>
> "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>
>|||I've been there many times ;). Nowhere mentioned MemtoLeave for AWE.
thanks
"Uri Dimant" wrote:

> Hi
> Please visit at http://www.sql-server-performance.com/awe_memory.asp
> "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
> to
>
>|||The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256MB
for SQL Server 2000. This is documented in SQL Server BOL topic 'Using Star
tup Parameters':
Specifies the amount of virtual address space (in megabytes) SQL Server will
leave available for memory allocations within the SQL Server process, but o
utside the SQL Server memory pool. This is the area used by SQL Server for l
oading items such as extended procedure .dll files, the OLE DB providers ref
erenced by distributed queries, and automation objects referenced in Transac
t-SQL statements. The default is 256 megabytes (MB).
The values are not modified when AWE is enabled. There are no counters in P
erfMon to show this value.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-491C-
4B35-B1E8-6667E9A55B90@.microsoft.com...
256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same when
using AWE.
"Andrew J. Kelly" wrote:

> Actually I believe the default is 256MB.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "lzhu@.dba1.com" <lzhu@.dba1.com@.discussions.microsoft.com> wrote in message
> news:34BAFBDC-BDDB-4509-9F02-972DBD30567F@.microsoft.com...
>
>
>|||Thanks for the reply. Interestingly there are different numbers ... Both Ken
Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
256mb for sql7.
"Ryan Stonecipher [MSFT]" wrote:

> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256M
B for SQL Server 2000. This is documented in SQL Server BOL topic 'Using St
artup Parameters':
> Specifies the amount of virtual address space (in megabytes) SQL Server will leave
available for memory allocations within the SQL Server process, but outside the SQL
Server memory pool. This is the area used by SQL Server for loading items such as e
xte
nded procedure .dll files, the OLE DB providers referenced by distributed queries, and autom
ation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).[vbco
l=seagreen]
> The values are not modified when AWE is enabled. There are no counters in
PerfMon to show this value.
> Thanks,
> Ryan Stonecipher
> SQL Server Storage Engine
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-4
91C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
when
> using AWE.
> "Andrew J. Kelly" wrote:
>|||I have seen this stated both ways myself but I do believe as Ryan stated it
is 256MB for 2000.
Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:B782B0E4-C276-47A9-959F-64C47F3B1A1F@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. Interestingly there are different numbers ... Both
> Ken
> Hendson's book and an artical on sqljunkies are saying 384mb for sql2k and
> 256mb for sql7.
> "Ryan Stonecipher [MSFT]" wrote:
>|||A follow up question: I'm using 8GB with AWE and have set sql max mem to 6GB
.
Does this mean that MemtoLeave will come from 2GB which I left for OS and 6G
B
is resvered sql BPOOL?
thanks
lzhu
"Ryan Stonecipher [MSFT]" wrote:

> The default value in SQL Server 7.0 is 128 MB, and was adjusted up to 256M
B for SQL Server 2000. This is documented in SQL Server BOL topic 'Using St
artup Parameters':
> Specifies the amount of virtual address space (in megabytes) SQL Server will leave
available for memory allocations within the SQL Server process, but outside the SQL
Server memory pool. This is the area used by SQL Server for loading items such as e
xte
nded procedure .dll files, the OLE DB providers referenced by distributed queries, and autom
ation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).[vbco
l=seagreen]
> The values are not modified when AWE is enabled. There are no counters in
PerfMon to show this value.
> Thanks,
> Ryan Stonecipher
> SQL Server Storage Engine
> "lzhu" <lzhu@.discussions.microsoft.com> wrote in message news:6315334A-4
91C-4B35-B1E8-6667E9A55B90@.microsoft.com...
> 256MB is sql7 and 384MB for sql2000. I'm just not sure if it's the same
when
> using AWE.
> "Andrew J. Kelly" wrote:
>|||No the memtoleave is always taken from the 2GB of directly addressable
memory for SQL Server. The OS has it's own 2GB. AWE has no bearing on this
and the only thing within sql server that can use AWE memory (or memory
above 2 or 3GB depending on /3GB) is the data cache. Everything else must
come from the directly addressable memory.
Andrew J. Kelly SQL MVP
"lzhu" <lzhu@.discussions.microsoft.com> wrote in message
news:C32428CD-5EE8-4B4F-B3C1-6761C5FECF26@.microsoft.com...[vbcol=seagreen]
>A follow up question: I'm using 8GB with AWE and have set sql max mem to
>6GB.
> Does this mean that MemtoLeave will come from 2GB which I left for OS and
> 6GB
> is resvered sql BPOOL?
> thanks
> lzhu
> "Ryan Stonecipher [MSFT]" wrote:
>sql

MemToLeave area! How to monitor?

Please, take a look at "Inside SQL Server 2000's Memory Management Facilities" by Ken Henderson:
http://msdn.microsoft.com/library/de...v_01262004.asp
"... None of the tools you typically use to inspect application memory use (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by individual processes. There's no indication of the amount of AWE memory used by each process, nor is this
memory included in the working set size reported for a given process ..."
Thanks,
-Ivan
--Original Message--
From: james
Posted At: Tuesday, February 28, 2006 6:52 AM
Posted To: microsoft.public.sqlserver.server
Conversation: MemToLeave area! How to monitor?
Subject: MemToLeave area! How to monitor?
Gurus,
Occasionally I get the error of "WARNING: Failed to reserve contiguous memory of Size= 65536."
What is the unit for this size, is it bytes or KB? Additionally,
I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up maximum for Sql server.
If I look at Perfmon counter of Process->Private bytes for sql server process it gives me 224 MB.
If I look at sqlserver.exe process on task manager it gives me 215MB.
If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
I would like to know, How can I calculate exactly how much memory is being assigned and how much in use from MemToLeave area by looking at above mentioned counters or any other perfmon or dbcc counters?
I really appreciate any input on this matter.
Thanks Ivan. I had already checked that article and also few other google
search but didn't get the answer.
<ivanpe@.online.microsoft.com> wrote in message
news:OenNh9IPGHA.3840@.TK2MSFTNGP14.phx.gbl...
> Please, take a look at "Inside SQL Server 2000's Memory Management
> Facilities" by Ken Henderson:
> http://msdn.microsoft.com/library/de...v_01262004.asp
> "... None of the tools you typically use to inspect application memory use
> (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by
> individual processes. There's no indication of the amount of AWE memory
> used by each process, nor is this memory included in the working set size
> reported for a given process ..."
> Thanks,
> -Ivan
> --Original Message--
> From: james
> Posted At: Tuesday, February 28, 2006 6:52 AM
> Posted To: microsoft.public.sqlserver.server
> Conversation: MemToLeave area! How to monitor?
> Subject: MemToLeave area! How to monitor?
>
> Gurus,
> Occasionally I get the error of "WARNING: Failed to reserve contiguous
> memory of Size= 65536."
> What is the unit for this size, is it bytes or KB? Additionally,
> I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on
> AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up
> maximum for Sql server.
> If I look at Perfmon counter of Process->Private bytes for sql server
> process it gives me 224 MB.
> If I look at sqlserver.exe process on task manager it gives me 215MB.
> If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
> I would like to know, How can I calculate exactly how much memory is being
> assigned and how much in use from MemToLeave area by looking at above
> mentioned counters or any other perfmon or dbcc counters?
> I really appreciate any input on this matter.
|||James,
The unit of measure in the message you list is bytes, not KB. Basically,
the memory mgr is failing to reserve 64KB of memory.
The numbers you report from the various monitoring tools don't sound
surprising -- they all measure different things. For example,
Process:Private Bytes is a measure of _committed_ virtual memory, not
reserved. -G controls the region set aside for MTL, not committed or
reserved -- free. That virtual memory is reserved and committed as needed
by the various memory consumers running inside the SQL Server process.
Also, on SS2K, AWE can't be used for anything except caching data and index
pages. Regular MTL allocations never come from AWE.
Keep in mind that the MTL region is really not a region at all but just
refers to the memory left over once the BPool takes what it needs. It's the
unused virtual memory in the process's virtual address space. -G can grow
or shrink this area, but it basically only amounts to unused memory within
the process.
Allocations by external consumers (COM objects, xprocs (usually), OLEDB
providers, etc.) come from MTL. Also, allocations by the server itself that
are >8KB are serviced from MTL rather than the BPool. This just means that,
at some level, they call VirtualAlloc to allocate VM directly from Windows
rather than using pages already allocated to the BPool.
Accompanying the error message you list should be the equivalent of DBCC
MEMORYSTATUS output. This is more relevant than running the command
yourself because it's taken at the exact moment the error occurred. If I
were you, I'd have a look at the various buckets listed in that report to
see if any of them seem high. Keep in mind that many of them are page
counts, not byte counts, so you need to multiply them by 8KB to get the
exact byte count in use.
Also keep in mind that this error can be caused by extreme fragmentation as
well as over-allocation of memory. IOW, you could have well more than 64KB
available within the process, but no single contiguous block of that size or
larger.
If, after you've worked through the above, you still can't figure out why
the reservation is failing, you might want to contact PSS to help you
troubleshoot it further. They deal with these all the time and should be
able to get you fixed up in no time.
HTH,
-kh
"james" <kush@.brandes.com> wrote in message
news:%23hTPNHLPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Thanks Ivan. I had already checked that article and also few other google
> search but didn't get the answer.
> <ivanpe@.online.microsoft.com> wrote in message
> news:OenNh9IPGHA.3840@.TK2MSFTNGP14.phx.gbl...
>

MemToLeave area! How to monitor?

Please, take a look at "Inside SQL Server 2000's Memory Management Facilitie
s" by Ken Henderson:
http://msdn.microsoft.com/library/d...ev_01262004.asp
"... None of the tools you typically use to inspect application memory use (
Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by in
dividual processes. There's no indication of the amount of AWE memory used b
y each process, nor is this
memory included in the working set size reported for a given process ..."
Thanks,
-Ivan
--Original Message--
From: james
Posted At: Tuesday, February 28, 2006 6:52 AM
Posted To: microsoft.public.sqlserver.server
Conversation: MemToLeave area! How to monitor?
Subject: MemToLeave area! How to monitor?
Gurus,
Occasionally I get the error of "WARNING: Failed to reserve contiguous memo
ry of Size= 65536."
What is the unit for this size, is it bytes or KB? Additionally,
I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on A
WE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up ma
ximum for Sql server.
If I look at Perfmon counter of Process->Private bytes for sql server proces
s it gives me 224 MB.
If I look at sqlserver.exe process on task manager it gives me 215MB.
If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
I would like to know, How can I calculate exactly how much memory is being a
ssigned and how much in use from MemToLeave area by looking at above mention
ed counters or any other perfmon or dbcc counters?
I really appreciate any input on this matter.Thanks Ivan. I had already checked that article and also few other google
search but didn't get the answer.
<ivanpe@.online.microsoft.com> wrote in message
news:OenNh9IPGHA.3840@.TK2MSFTNGP14.phx.gbl...
> Please, take a look at "Inside SQL Server 2000's Memory Management
> Facilities" by Ken Henderson:
> http://msdn.microsoft.com/library/d...ev_01262004.asp
> "... None of the tools you typically use to inspect application memory use
> (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by
> individual processes. There's no indication of the amount of AWE memory
> used by each process, nor is this memory included in the working set size
> reported for a given process ..."
> Thanks,
> -Ivan
> --Original Message--
> From: james
> Posted At: Tuesday, February 28, 2006 6:52 AM
> Posted To: microsoft.public.sqlserver.server
> Conversation: MemToLeave area! How to monitor?
> Subject: MemToLeave area! How to monitor?
>
> Gurus,
> Occasionally I get the error of "WARNING: Failed to reserve contiguous
> memory of Size= 65536."
> What is the unit for this size, is it bytes or KB? Additionally,
> I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on
> AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up
> maximum for Sql server.
> If I look at Perfmon counter of Process->Private bytes for sql server
> process it gives me 224 MB.
> If I look at sqlserver.exe process on task manager it gives me 215MB.
> If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
> I would like to know, How can I calculate exactly how much memory is being
> assigned and how much in use from MemToLeave area by looking at above
> mentioned counters or any other perfmon or dbcc counters?
> I really appreciate any input on this matter.|||James,
The unit of measure in the message you list is bytes, not KB. Basically,
the memory mgr is failing to reserve 64KB of memory.
The numbers you report from the various monitoring tools don't sound
surprising -- they all measure different things. For example,
Process:Private Bytes is a measure of _committed_ virtual memory, not
reserved. -G controls the region set aside for MTL, not committed or
reserved -- free. That virtual memory is reserved and committed as needed
by the various memory consumers running inside the SQL Server process.
Also, on SS2K, AWE can't be used for anything except caching data and index
pages. Regular MTL allocations never come from AWE.
Keep in mind that the MTL region is really not a region at all but just
refers to the memory left over once the BPool takes what it needs. It's the
unused virtual memory in the process's virtual address space. -G can grow
or shrink this area, but it basically only amounts to unused memory within
the process.
Allocations by external consumers (COM objects, xprocs (usually), OLEDB
providers, etc.) come from MTL. Also, allocations by the server itself that
are >8KB are serviced from MTL rather than the BPool. This just means that,
at some level, they call VirtualAlloc to allocate VM directly from Windows
rather than using pages already allocated to the BPool.
Accompanying the error message you list should be the equivalent of DBCC
MEMORYSTATUS output. This is more relevant than running the command
yourself because it's taken at the exact moment the error occurred. If I
were you, I'd have a look at the various buckets listed in that report to
see if any of them seem high. Keep in mind that many of them are page
counts, not byte counts, so you need to multiply them by 8KB to get the
exact byte count in use.
Also keep in mind that this error can be caused by extreme fragmentation as
well as over-allocation of memory. IOW, you could have well more than 64KB
available within the process, but no single contiguous block of that size or
larger.
If, after you've worked through the above, you still can't figure out why
the reservation is failing, you might want to contact PSS to help you
troubleshoot it further. They deal with these all the time and should be
able to get you fixed up in no time.
HTH,
-kh
"james" <kush@.brandes.com> wrote in message
news:%23hTPNHLPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Thanks Ivan. I had already checked that article and also few other google
> search but didn't get the answer.
> <ivanpe@.online.microsoft.com> wrote in message
> news:OenNh9IPGHA.3840@.TK2MSFTNGP14.phx.gbl...
>

MemToLeave area! How to monitor?

Gurus,
Occasionally I get the error of "WARNING: Failed to reserve contiguous
memory of Size= 65536."
What is the unit for this size, is it bytes or KB? Additionally,
I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on
AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up
maximum for Sql server.
If I look at Perfmon counter of Process->Private bytes for sql server
process it gives me 224 MB.
If I look at sqlserver.exe process on task manager it gives me 215MB.
If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
I would like to know, How can I calculate exactly how much memory is being
assigned and how much in use from MemToLeave area by looking at above
mentioned counters or any other perfmon or dbcc counters?
I really appreciate any input on this matter.Please, take a look at "Inside SQL Server 2000's Memory Management Facilities" by Ken Henderson:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
"... None of the tools you typically use to inspect application memory use (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by individual processes. There's no indication of the amount of AWE memory used by each process, nor is this memory included in the working set size reported for a given process ..."
Thanks,
-Ivan
--Original Message--
From: james
Posted At: Tuesday, February 28, 2006 6:52 AM
Posted To: microsoft.public.sqlserver.server
Conversation: MemToLeave area! How to monitor?
Subject: MemToLeave area! How to monitor?
Gurus,
Occasionally I get the error of "WARNING: Failed to reserve contiguous memory of Size= 65536."
What is the unit for this size, is it bytes or KB? Additionally,
I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up maximum for Sql server.
If I look at Perfmon counter of Process->Private bytes for sql server process it gives me 224 MB.
If I look at sqlserver.exe process on task manager it gives me 215MB.
If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
I would like to know, How can I calculate exactly how much memory is being assigned and how much in use from MemToLeave area by looking at above mentioned counters or any other perfmon or dbcc counters?
I really appreciate any input on this matter.|||Thanks Ivan. I had already checked that article and also few other google
search but didn't get the answer.
<ivanpe@.online.microsoft.com> wrote in message
news:OenNh9IPGHA.3840@.TK2MSFTNGP14.phx.gbl...
> Please, take a look at "Inside SQL Server 2000's Memory Management
> Facilities" by Ken Henderson:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
> "... None of the tools you typically use to inspect application memory use
> (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by
> individual processes. There's no indication of the amount of AWE memory
> used by each process, nor is this memory included in the working set size
> reported for a given process ..."
> Thanks,
> -Ivan
> --Original Message--
> From: james
> Posted At: Tuesday, February 28, 2006 6:52 AM
> Posted To: microsoft.public.sqlserver.server
> Conversation: MemToLeave area! How to monitor?
> Subject: MemToLeave area! How to monitor?
>
> Gurus,
> Occasionally I get the error of "WARNING: Failed to reserve contiguous
> memory of Size= 65536."
> What is the unit for this size, is it bytes or KB? Additionally,
> I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a on
> AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is set up
> maximum for Sql server.
> If I look at Perfmon counter of Process->Private bytes for sql server
> process it gives me 224 MB.
> If I look at sqlserver.exe process on task manager it gives me 215MB.
> If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
> I would like to know, How can I calculate exactly how much memory is being
> assigned and how much in use from MemToLeave area by looking at above
> mentioned counters or any other perfmon or dbcc counters?
> I really appreciate any input on this matter.|||James,
The unit of measure in the message you list is bytes, not KB. Basically,
the memory mgr is failing to reserve 64KB of memory.
The numbers you report from the various monitoring tools don't sound
surprising -- they all measure different things. For example,
Process:Private Bytes is a measure of _committed_ virtual memory, not
reserved. -G controls the region set aside for MTL, not committed or
reserved -- free. That virtual memory is reserved and committed as needed
by the various memory consumers running inside the SQL Server process.
Also, on SS2K, AWE can't be used for anything except caching data and index
pages. Regular MTL allocations never come from AWE.
Keep in mind that the MTL region is really not a region at all but just
refers to the memory left over once the BPool takes what it needs. It's the
unused virtual memory in the process's virtual address space. -G can grow
or shrink this area, but it basically only amounts to unused memory within
the process.
Allocations by external consumers (COM objects, xprocs (usually), OLEDB
providers, etc.) come from MTL. Also, allocations by the server itself that
are >8KB are serviced from MTL rather than the BPool. This just means that,
at some level, they call VirtualAlloc to allocate VM directly from Windows
rather than using pages already allocated to the BPool.
Accompanying the error message you list should be the equivalent of DBCC
MEMORYSTATUS output. This is more relevant than running the command
yourself because it's taken at the exact moment the error occurred. If I
were you, I'd have a look at the various buckets listed in that report to
see if any of them seem high. Keep in mind that many of them are page
counts, not byte counts, so you need to multiply them by 8KB to get the
exact byte count in use.
Also keep in mind that this error can be caused by extreme fragmentation as
well as over-allocation of memory. IOW, you could have well more than 64KB
available within the process, but no single contiguous block of that size or
larger.
If, after you've worked through the above, you still can't figure out why
the reservation is failing, you might want to contact PSS to help you
troubleshoot it further. They deal with these all the time and should be
able to get you fixed up in no time.
HTH,
-kh
"james" <kush@.brandes.com> wrote in message
news:%23hTPNHLPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Thanks Ivan. I had already checked that article and also few other google
> search but didn't get the answer.
> <ivanpe@.online.microsoft.com> wrote in message
> news:OenNh9IPGHA.3840@.TK2MSFTNGP14.phx.gbl...
>> Please, take a look at "Inside SQL Server 2000's Memory Management
>> Facilities" by Ken Henderson:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
>> "... None of the tools you typically use to inspect application memory
>> use (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory
>> used by individual processes. There's no indication of the amount of AWE
>> memory used by each process, nor is this memory included in the working
>> set size reported for a given process ..."
>> Thanks,
>> -Ivan
>> --Original Message--
>> From: james
>> Posted At: Tuesday, February 28, 2006 6:52 AM
>> Posted To: microsoft.public.sqlserver.server
>> Conversation: MemToLeave area! How to monitor?
>> Subject: MemToLeave area! How to monitor?
>>
>> Gurus,
>> Occasionally I get the error of "WARNING: Failed to reserve contiguous
>> memory of Size= 65536."
>> What is the unit for this size, is it bytes or KB? Additionally,
>> I have set up 511 MB for MemToLeave area by adding -g384 for Sql2k SP3a
>> on AWE enabled system. I have total of 16 GB of RAM, of which 14 GB is
>> set up maximum for Sql server.
>> If I look at Perfmon counter of Process->Private bytes for sql server
>> process it gives me 224 MB.
>> If I look at sqlserver.exe process on task manager it gives me 215MB.
>> If I look at DBCC memorystatus, OS in use, it gives me 12 MB.
>> I would like to know, How can I calculate exactly how much memory is
>> being assigned and how much in use from MemToLeave area by looking at
>> above mentioned counters or any other perfmon or dbcc counters?
>> I really appreciate any input on this matter.
>

MemToLeave and Thirdy Pary DLLs

Hi
I've been running into problems with a server that indicate that space in the
MemToLeave area is exhausted. The messages in the error log are along the
lines of 'Failed to reserve contigous memory', 'Clearing procedure cache to
free contigous memory' et cetera.
We do have a third party DLL in use and I've seen in a previous post a
recommendation to move third party DLLs out of the production server's
address space. The question is how to do this? My NT administrators don't
know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I can
do once a problem has occurred, but is there a way of ensuring the DLL is
always out of the server's address space?
If I unload the DLL what is to stop it meing reloaded, as it is used by
database monitoring software that runs every two minutes?
I'm also a little confused by the -g startup setting.
One post I've seen says to set it to 384 to increase the space available to
the MEmToLeave. Another post states that you need 128M for the worker threads
and using the default -g will add another 256M, bringing the default to 384M.
BOL is woolly on the subject, as normal.
Thanks
Mark
Message posted via http://www.droptable.com
Hi Mark
Some .dll's are designed to be run either in-process or ex-process & some
are simply designed to run in-process, so some can be forced to run
ex-process & some can't.
The type that typically *can* be run ex-process from SQL Server are COM type
..dlls, typically developed in VB6.0 (but can also be developed in Delphi,
C++ or J++0). you know you're dealing with one of these if the calling code
uses the sp_OACreate syntax. To make these guys run ex-process, you
configure them to run within COM+ or (or MTS on older WinNT) context, which
requires registering them in COM+.
The type that typcially *cannot* be run ex-process from SQL Server are
Extended Stored procedures, which are typically written in C and simply
aren't designed to be run ex-process. So if you're talking about a .dll
which is registered as an Extended Stored Proc, you probably won't be able
to make it run ex-process.
The post about the -g switch is correct. SQL Server's allocation of memory
during startup is best documented in Ken Henderson's Internals Guru's Guide
book. He's also written articles on SQL Memory Management, one of which is
here:
http://msdn.microsoft.com/data/defau...v_01262004.asp
But I dono't think this one specifically covers the -g switch in the detail
you're after. Try to get hold of the book, as it'd definitely covered in
detail in its Memory chapter.
HTH
Regards,
Greg Linwood
SQL Server MVP
"mpenfold via droptable.com" <u16092@.uwe> wrote in message
news:58090a9e28698@.uwe...
> Hi
> I've been running into problems with a server that indicate that space in
> the
> MemToLeave area is exhausted. The messages in the error log are along the
> lines of 'Failed to reserve contigous memory', 'Clearing procedure cache
> to
> free contigous memory' et cetera.
> We do have a third party DLL in use and I've seen in a previous post a
> recommendation to move third party DLLs out of the production server's
> address space. The question is how to do this? My NT administrators don't
> know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I
> can
> do once a problem has occurred, but is there a way of ensuring the DLL is
> always out of the server's address space?
> If I unload the DLL what is to stop it meing reloaded, as it is used by
> database monitoring software that runs every two minutes?
> I'm also a little confused by the -g startup setting.
> One post I've seen says to set it to 384 to increase the space available
> to
> the MEmToLeave. Another post states that you need 128M for the worker
> threads
> and using the default -g will add another 256M, bringing the default to
> 384M.
> BOL is woolly on the subject, as normal.
> Thanks
> Mark
> --
> Message posted via http://www.droptable.com

MemToLeave and Thirdy Pary DLLs

Hi
I've been running into problems with a server that indicate that space in the
MemToLeave area is exhausted. The messages in the error log are along the
lines of 'Failed to reserve contigous memory', 'Clearing procedure cache to
free contigous memory' et cetera.
We do have a third party DLL in use and I've seen in a previous post a
recommendation to move third party DLLs out of the production server's
address space. The question is how to do this? My NT administrators don't
know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I can
do once a problem has occurred, but is there a way of ensuring the DLL is
always out of the server's address space?
If I unload the DLL what is to stop it meing reloaded, as it is used by
database monitoring software that runs every two minutes?
I'm also a little confused by the -g startup setting.
One post I've seen says to set it to 384 to increase the space available to
the MEmToLeave. Another post states that you need 128M for the worker threads
and using the default -g will add another 256M, bringing the default to 384M.
BOL is woolly on the subject, as normal.
Thanks
Mark
--
Message posted via http://www.sqlmonster.comHi Mark
Some .dll's are designed to be run either in-process or ex-process & some
are simply designed to run in-process, so some can be forced to run
ex-process & some can't.
The type that typically *can* be run ex-process from SQL Server are COM type
.dlls, typically developed in VB6.0 (but can also be developed in Delphi,
C++ or J++0). you know you're dealing with one of these if the calling code
uses the sp_OACreate syntax. To make these guys run ex-process, you
configure them to run within COM+ or (or MTS on older WinNT) context, which
requires registering them in COM+.
The type that typcially *cannot* be run ex-process from SQL Server are
Extended Stored procedures, which are typically written in C and simply
aren't designed to be run ex-process. So if you're talking about a .dll
which is registered as an Extended Stored Proc, you probably won't be able
to make it run ex-process.
The post about the -g switch is correct. SQL Server's allocation of memory
during startup is best documented in Ken Henderson's Internals Guru's Guide
book. He's also written articles on SQL Memory Management, one of which is
here:
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
But I dono't think this one specifically covers the -g switch in the detail
you're after. Try to get hold of the book, as it'd definitely covered in
detail in its Memory chapter.
HTH
Regards,
Greg Linwood
SQL Server MVP
"mpenfold via SQLMonster.com" <u16092@.uwe> wrote in message
news:58090a9e28698@.uwe...
> Hi
> I've been running into problems with a server that indicate that space in
> the
> MemToLeave area is exhausted. The messages in the error log are along the
> lines of 'Failed to reserve contigous memory', 'Clearing procedure cache
> to
> free contigous memory' et cetera.
> We do have a third party DLL in use and I've seen in a previous post a
> recommendation to move third party DLLs out of the production server's
> address space. The question is how to do this? My NT administrators don't
> know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I
> can
> do once a problem has occurred, but is there a way of ensuring the DLL is
> always out of the server's address space?
> If I unload the DLL what is to stop it meing reloaded, as it is used by
> database monitoring software that runs every two minutes?
> I'm also a little confused by the -g startup setting.
> One post I've seen says to set it to 384 to increase the space available
> to
> the MEmToLeave. Another post states that you need 128M for the worker
> threads
> and using the default -g will add another 256M, bringing the default to
> 384M.
> BOL is woolly on the subject, as normal.
> Thanks
> Mark
> --
> Message posted via http://www.sqlmonster.comsql

MemToLeave and Thirdy Pary DLLs

Hi
I've been running into problems with a server that indicate that space in th
e
MemToLeave area is exhausted. The messages in the error log are along the
lines of 'Failed to reserve contigous memory', 'Clearing procedure cache to
free contigous memory' et cetera.
We do have a third party DLL in use and I've seen in a previous post a
recommendation to move third party DLLs out of the production server's
address space. The question is how to do this? My NT administrators don't
know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I can
do once a problem has occurred, but is there a way of ensuring the DLL is
always out of the server's address space?
If I unload the DLL what is to stop it meing reloaded, as it is used by
database monitoring software that runs every two minutes?
I'm also a little confused by the -g startup setting.
One post I've seen says to set it to 384 to increase the space available to
the MEmToLeave. Another post states that you need 128M for the worker thread
s
and using the default -g will add another 256M, bringing the default to 384M
.
BOL is woolly on the subject, as normal.
Thanks
Mark
Message posted via http://www.droptable.comHi Mark
Some .dll's are designed to be run either in-process or ex-process & some
are simply designed to run in-process, so some can be forced to run
ex-process & some can't.
The type that typically *can* be run ex-process from SQL Server are COM type
.dlls, typically developed in VB6.0 (but can also be developed in Delphi,
C++ or J++0). you know you're dealing with one of these if the calling code
uses the sp_OACreate syntax. To make these guys run ex-process, you
configure them to run within COM+ or (or MTS on older WinNT) context, which
requires registering them in COM+.
The type that typcially *cannot* be run ex-process from SQL Server are
Extended Stored procedures, which are typically written in C and simply
aren't designed to be run ex-process. So if you're talking about a .dll
which is registered as an Extended Stored Proc, you probably won't be able
to make it run ex-process.
The post about the -g switch is correct. SQL Server's allocation of memory
during startup is best documented in Ken Henderson's Internals Guru's Guide
book. He's also written articles on SQL Memory Management, one of which is
here:
http://msdn.microsoft.com/data/defa...ev_01262004.asp
But I dono't think this one specifically covers the -g switch in the detail
you're after. Try to get hold of the book, as it'd definitely covered in
detail in its Memory chapter.
HTH
Regards,
Greg Linwood
SQL Server MVP
"mpenfold via droptable.com" <u16092@.uwe> wrote in message
news:58090a9e28698@.uwe...
> Hi
> I've been running into problems with a server that indicate that space in
> the
> MemToLeave area is exhausted. The messages in the error log are along the
> lines of 'Failed to reserve contigous memory', 'Clearing procedure cache
> to
> free contigous memory' et cetera.
> We do have a third party DLL in use and I've seen in a previous post a
> recommendation to move third party DLLs out of the production server's
> address space. The question is how to do this? My NT administrators don't
> know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I
> can
> do once a problem has occurred, but is there a way of ensuring the DLL is
> always out of the server's address space?
> If I unload the DLL what is to stop it meing reloaded, as it is used by
> database monitoring software that runs every two minutes?
> I'm also a little confused by the -g startup setting.
> One post I've seen says to set it to 384 to increase the space available
> to
> the MEmToLeave. Another post states that you need 128M for the worker
> threads
> and using the default -g will add another 256M, bringing the default to
> 384M.
> BOL is woolly on the subject, as normal.
> Thanks
> Mark
> --
> Message posted via http://www.droptable.com

MemToLeave and ODBC

We are running SQL Server 2005, SP2, Standard Edition, 64bit.
We are considering an ODBC solution for client reporting, and have been
reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
I was wondering if the queries sent via this ODBC connection, also utilize
MemToLeave, or if those queries use the Buffer Pool?
And then a separate question, not involving ODBC...does an adhoc query
executed from Management Studio utilize Buffer Pool memory, if it needs less
than 8K contiguous memory, or do all adhoc queries in general go directly
against MemToLeave?
As a side note, I have read some from Ken Hendersons blog, threads in this
discussion group, and other posts on the web. I cannot seem to find the
answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still seeking for answers. Please help.
cbrichards wrote:
>We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>We are considering an ODBC solution for client reporting, and have been
>reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>I was wondering if the queries sent via this ODBC connection, also utilize
>MemToLeave, or if those queries use the Buffer Pool?
>And then a separate question, not involving ODBC...does an adhoc query
>executed from Management Studio utilize Buffer Pool memory, if it needs less
>than 8K contiguous memory, or do all adhoc queries in general go directly
>against MemToLeave?
>As a side note, I have read some from Ken Hendersons blog, threads in this
>discussion group, and other posts on the web. I cannot seem to find the
>answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||On a 64bit SQL instance, you should have little to worry about MemToLeave
because you have a huge virtual address space to go about. I might be wrong,
but my understanding is that whether MemToLeave is used depends on factors
such as memory allocation size, not on what database API is being used on the
client side.
Linchi
"cbrichards via SQLMonster.com" wrote:
> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> We are considering an ODBC solution for client reporting, and have been
> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
> I was wondering if the queries sent via this ODBC connection, also utilize
> MemToLeave, or if those queries use the Buffer Pool?
> And then a separate question, not involving ODBC...does an adhoc query
> executed from Management Studio utilize Buffer Pool memory, if it needs less
> than 8K contiguous memory, or do all adhoc queries in general go directly
> against MemToLeave?
> As a side note, I have read some from Ken Hendersons blog, threads in this
> discussion group, and other posts on the web. I cannot seem to find the
> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
RAM (14GB set as Max Server Memory), are you saying that just because I am
running 64bit, that I have a huge virtual address space? Where does this huge
virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
Linchi Shea wrote:
>On a 64bit SQL instance, you should have little to worry about MemToLeave
>because you have a huge virtual address space to go about. I might be wrong,
>but my understanding is that whether MemToLeave is used depends on factors
>such as memory allocation size, not on what database API is being used on the
>client side.
>Linchi
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>[quoted text clipped - 11 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via http://www.sqlmonster.com|||Yes, SQL Server isn't even aware of what API is used by the client application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:76F3D546-8C00-4E8A-81A6-CDA22E1E0193@.microsoft.com...
> On a 64bit SQL instance, you should have little to worry about MemToLeave
> because you have a huge virtual address space to go about. I might be wrong,
> but my understanding is that whether MemToLeave is used depends on factors
> such as memory allocation size, not on what database API is being used on the
> client side.
> Linchi
> "cbrichards via SQLMonster.com" wrote:
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>> We are considering an ODBC solution for client reporting, and have been
>> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>> I was wondering if the queries sent via this ODBC connection, also utilize
>> MemToLeave, or if those queries use the Buffer Pool?
>> And then a separate question, not involving ODBC...does an adhoc query
>> executed from Management Studio utilize Buffer Pool memory, if it needs less
>> than 8K contiguous memory, or do all adhoc queries in general go directly
>> against MemToLeave?
>> As a side note, I have read some from Ken Hendersons blog, threads in this
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>>|||MemToLeave is a virtual memory address space concept. It has nothing to do
with how mch physical memory you have.
Let's forget about performance and physical memory for now. When you write a
program in a high-level programming language, you may feel like you can just
keep allocating data structures. But eventually you'll run into out of memory
error because the compiler/OS knows that there is a limit to the number of
unique virtual memory addresses for a process. If those addresses are all
used up in the process, you are not allowed to allocate any more and it's the
OS' job to keep track of the usage of the virtual memeory addresses.
On a 32-bit system, the number of virtual memory addresses is limited.
Basically there are 2GB for a program to allocate its data structures (the
other 2GB are taken by the kernel). Since the SQL process' virtual address
space may get fragmented after it has been allocating data structures for a
while and fragmentation can get so bad so that it may not be able to find
contiguious virtual addrsses for larger data structures (i.e. larger than
8K), the strategy that SQL Server uses is to pre-allocate a chunk of virutal
addresses when it starts so that this chunk is available regardless of it
snormal allocation activities. This pre-allocated chunk of virtual memory
addresses is MemToLeave.
Now with a 64-bit system, the virutal address space is so large that there
is little to no danger that a SQL instance (primarily its buffer pool) will
ever use up so much of virtual addresses that its process will have no
contiguious virtual addresses left for larger data structures.
Again, this is all related virtual memory addresses, and has nothing to do
with how much physical memory you may have on a particular system. In
reality, of course if you don't have enough physical memory, you risk
swapping things to paging files and your performance will suffer.
Linchi
"cbrichards via SQLMonster.com" wrote:
> So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
> RAM (14GB set as Max Server Memory), are you saying that just because I am
> running 64bit, that I have a huge virtual address space? Where does this huge
> virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
>
> Linchi Shea wrote:
> >On a 64bit SQL instance, you should have little to worry about MemToLeave
> >because you have a huge virtual address space to go about. I might be wrong,
> >but my understanding is that whether MemToLeave is used depends on factors
> >such as memory allocation size, not on what database API is being used on the
> >client side.
> >
> >Linchi
> >
> >> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> >>
> >[quoted text clipped - 11 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via http://www.sqlmonster.com
>|||I am really perplexed at this whole virtual address space with 64bit and I do
not seem to be getting a straight answer. It is probably the way I am
phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
my disks, then if the 14GB is being used by SQL Server, and then another
query comes along, and the 14GB is consumed, is the virtual address space the
2GB left for the operating system, plus the 100GB carved out on the SAN?
Tibor Karaszi wrote:
>Yes, SQL Server isn't even aware of what API is used by the client application.
>> On a 64bit SQL instance, you should have little to worry about MemToLeave
>> because you have a huge virtual address space to go about. I might be wrong,
>[quoted text clipped - 19 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||First of all, virtual address space of a process has nothing to do with how
much physial memory or disk storage you may have on a system. On 32-bit
Windows, the user-mode virtual address space is 2GB (or 3GB with /3GB switch
in the boot.ini). On 64-bit Windows, the virtual address space of a process
is 8TB. These numbers don't change regardless how much RAM or disk storage
you have.
Secondly, the size of the virtual address space is a function of the width
of the address or the number of unique pointer values. So in theory, on
32-bit system, the virtual address space is 2^32, and if each address points
to a byte, that's 4GB. Also in theory, on a 64-bit system, the virtual
address space is 2^64, and if each points to a byte, that's more than 16
exabytes. But actual implementations often impose additional restriction. For
instance, 32-bit Windows gives 2GB to the kernel and leave 2GB to your app to
use. And since 16 exabytes are excessive, currently beyond reach anyway, and
costly to support, only 43 bits are actually used for user-mode virtual
addresses on current x64, and that's ~8TB.
Now, if you really end using this much virtual address space, they (i.e.
virtual memory allocated to represent your data structures) have to be
backed/supported with real stores, which can be physical memory or paging
files.
Linchi
"cbrichards via SQLMonster.com" wrote:
> I am really perplexed at this whole virtual address space with 64bit and I do
> not seem to be getting a straight answer. It is probably the way I am
> phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
> is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
> my disks, then if the 14GB is being used by SQL Server, and then another
> query comes along, and the 14GB is consumed, is the virtual address space the
> 2GB left for the operating system, plus the 100GB carved out on the SAN?
> Tibor Karaszi wrote:
> >Yes, SQL Server isn't even aware of what API is used by the client application.
> >
> >> On a 64bit SQL instance, you should have little to worry about MemToLeave
> >> because you have a huge virtual address space to go about. I might be wrong,
> >[quoted text clipped - 19 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||On Thu, 04 Oct 2007 01:49:25 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I have 16GB RAM, of which 14GB
>is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
>my disks, then if the 14GB is being used by SQL Server, and then another
>query comes along, and the 14GB is consumed, is the virtual address space the
>2GB left for the operating system, plus the 100GB carved out on the SAN?
There are, or so I heard at some time way in the past, computers with
the architecture where memory and disk are mapped with a common
address space, as if each was an extension of the other. That sounds
like what you are asking about. Microsoft SQL Server does not run on
any such architecture.
To oversimplify a bit... Most of SQL Server's memory is used for
caching database pages. If SQL Server needs a page that is already in
cache it is just referenced from that memory location. If it needs a
page that is not in cache it is read into cache. When there is no
room in memory for a new page a page that hasn't been used recently is
overwritten. When a page is updated it will be written to disk -
eventually. (Logs are handled a bit differently and always written
immediately.)
Sorry if I have misunderstood your statement.
Roy Harvey
Beacon Falls, CT

Wednesday, March 21, 2012

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

MemToLeave area! How to monitor?

MemToLeave area! How to monitor?

MemToLeave area! How to monitor?

MemToLeave and Thirdy Pary DLLs

MemToLeave and Thirdy Pary DLLs

MemToLeave and Thirdy Pary DLLs

MemToLeave and ODBC

Sql Memory Leaks

Blog Archive

About Me