Sql Memory Leaks: awe

Showing posts with label awe. 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

Monday, March 12, 2012

Memory Usage - PAE / AWE question

I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032BOOT.INI is a hidden system file in C:\. Add the /PAE switch and reboot.
You need to run:
sp_configure 'aew', 1
reconfigure
... and restart SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1181256247.731355.184430@.p47g2000hsd.googlegroups.com...
I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032|||Go to the Windows Explorer menu, Tools | Folder Options ..., and the View
tab, then uncheck Hide Protected operating system files. You'll then see
boot.ini in the system root directory (typically C:\).
Linchi
"bmwbase-newsgroup@.yahoo.com" wrote:
> I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
> Enterprise Edition SP4) which has 16Gb memory.
> I'm not 100% sure the server has been correctly configured to use the
> memory up to 12Gb as defined in the SQL Server properties.
> Based on the KB article at http://support.microsoft.com/kb/274750 I
> have two questions:
> Firstly, I cannot find the file boot.ini to include the /PAE switch.
> Where is this file supposed to be located? Do I need to create a new
> one?
> Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
> sample output below from sp_confiure:
> name minimum maximum
> config_value run_value
> -- -- --
> -- --
> show advanced options 0 1
> 1 1
> awe enabled 0 1
> 0 0
> max server memory (MB) 4 2147483647
> 12032 12032
>

Memory Usage - PAE / AWE question

I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032
BOOT.INI is a hidden system file in C:\. Add the /PAE switch and reboot.
You need to run:
sp_configure 'aew', 1
reconfigure
... and restart SQL Server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1181256247.731355.184430@.p47g2000hsd.googlegr oups.com...
I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032
|||Go to the Windows Explorer menu, Tools | Folder Options ..., and the View
tab, then uncheck Hide Protected operating system files. You'll then see
boot.ini in the system root directory (typically C:\).
Linchi
"bmwbase-newsgroup@.yahoo.com" wrote:

> I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
> Enterprise Edition SP4) which has 16Gb memory.
> I'm not 100% sure the server has been correctly configured to use the
> memory up to 12Gb as defined in the SQL Server properties.
> Based on the KB article at http://support.microsoft.com/kb/274750 I
> have two questions:
> Firstly, I cannot find the file boot.ini to include the /PAE switch.
> Where is this file supposed to be located? Do I need to create a new
> one?
> Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
> sample output below from sp_confiure:
> name minimum maximum
> config_value run_value
> -- -- --
> -- --
> show advanced options 0 1
> 1 1
> awe enabled 0 1
> 0 0
> max server memory (MB) 4 2147483647
> 12032 12032
>

Memory Usage - PAE / AWE question

I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032BOOT.INI is a hidden system file in C:\. Add the /PAE switch and reboot.
You need to run:
sp_configure 'aew', 1
reconfigure
... and restart SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1181256247.731355.184430@.p47g2000hsd.googlegroups.com...
I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032|||Go to the Windows Explorer menu, Tools | Folder Options ..., and the View
tab, then uncheck Hide Protected operating system files. You'll then see
boot.ini in the system root directory (typically C:\).
Linchi
"bmwbase-newsgroup@.yahoo.com" wrote:

> I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
> Enterprise Edition SP4) which has 16Gb memory.
> I'm not 100% sure the server has been correctly configured to use the
> memory up to 12Gb as defined in the SQL Server properties.
> Based on the KB article at http://support.microsoft.com/kb/274750 I
> have two questions:
> Firstly, I cannot find the file boot.ini to include the /PAE switch.
> Where is this file supposed to be located? Do I need to create a new
> one?
> Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
> sample output below from sp_confiure:
> name minimum maximum
> config_value run_value
> -- -- --
> -- --
> show advanced options 0 1
> 1 1
> awe enabled 0 1
> 0 0
> max server memory (MB) 4 2147483647
> 12032 12032
>

Friday, March 9, 2012

Memory Stress Test in SQL Server 2005

I have a Windows 2003 Server running SQL 2005. The server has 32 GB of memory and I have enabled AWE in SQL. I have also configured the min and max SQL memory as 1 GB and 28 GB, respectively. However, this server currently has very low activity so I'm not sure whether my AWE-related changes worked. SQLSERVR.EXE process takes up about 100 MB of memory. Is there any tool or scripts that I can use to memory stress SQL to confirm that AWE is really in effect ?

The are several ways. The easiest is to check the errrolog and look for a message like "Address Windowing Extensions is enabled. This is an informational message only; no user action is required.". This entry in MSDN is also relevant: http://msdn2.microsoft.com/en-us/library/ms190673.aspx

The following links may help you understand SQL Server memory usage:

http://msdn2.microsoft.com/en-us/library/ms176018.aspx (SQL Server 2005 monitoring memory usage)

http://support.microsoft.com/kb/907877 (DBCC memorystatus)

http://blogs.msdn.com/slavao/archive/tags/SQL+Server+Memory+Management/default.aspx (Slava's blog)

Thanks, Ron D.

memory setting in SQL 2005 in Windows 2003

We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:

http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage

What I need to do?

Thanks

Sounds like you have installed the 32 bit version of SQL Server, not the 64bit version of SQL Server.

Can you verify which version of SQL Server was installed?

|||

If I recall, AWE has no effect in 64 bit OS, and extended memory usage is not properly captured with Perf Monitor.

Please refer to this article:

Configuration -Memory, 32 bit SQL 2005 on 64 Bit Windows 2003
http://msdn2.microsoft.com/en-us/library/ms187499.aspx

And for reference on Memory, this is good starting point:

http://msdn2.microsoft.com/en-us/library/ms187499.aspx

|||

Hi Sandlu,

AWE will have no effect on 64 bit servers.

Yes you still need "LOCK PAGES IN MEMORY".

To check exactly how much memory is being used by SQL Server use Perfmon to check for SQL ServerMemoryManager:

Total Server Memory (KB)

Also check if you have capped the memory by using Max Server Memory under sp_configure.

With regards to slow performance, it could be any problem like disks, memory or no/out of date stats or Index defragmentation.

Jag

|||Thank you, everyone, will try.

Wednesday, March 7, 2012

memory probelm

Hi,
I have a production SQL server with 4-cpu, 8GB runs under SQL Server
Enterprise Edition & Windows 2000 Advance server.
AWE is enabled.
Max memory set to 7GB.
When I look at the Task Manager the 'Mem usage' is only 110,632 k.
Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
I am thinking there may be memory leak in the server.
My question is,
1. How can I find out if it is memory leak?
2. How to identify the process causing it?
Thanks for the help.
-sarav
Task manager is not the correct tool for monitoring memory usage with sql
server. Use perfmon counters instead. But when you use AWE memory Sql
Server will grab all 7GB right off the bat and will no longer dynamically
manage it.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:%23XFFSaAAFHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a production SQL server with 4-cpu, 8GB runs under SQL Server
> Enterprise Edition & Windows 2000 Advance server.
> AWE is enabled.
> Max memory set to 7GB.
> When I look at the Task Manager the 'Mem usage' is only 110,632 k.
> Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
> I am thinking there may be memory leak in the server.
> My question is,
> 1. How can I find out if it is memory leak?
> 2. How to identify the process causing it?
> Thanks for the help.
> -sarav
>
>
>
>
>
|||Moreover, make sure you set BOTH the /3GB and /PAE switches in the BOOT.ini
file.
The /PAE switch is required by the OS in order to manage memory allocations
above 4GB. It does this by replacing the normal 32-bit address register
with a make-shift 36-bit register; thus, allowing applications written on
the extended API calls to address up to 64 GB of memory.
The /3GB switch forces the OS to reserve only 1GB for private (kernel)
addresses and 3GB for user addresses. Doing this will give you up to a 3GB
Buffer/Proc Cache instead of the default 2GB. This will give you an AWE
address space of only 4GB instead of 5GB, which would be better optimized.
Sincerely,
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoL31iAAFHA.1400@.TK2MSFTNGP11.phx.gbl...
Task manager is not the correct tool for monitoring memory usage with sql
server. Use perfmon counters instead. But when you use AWE memory Sql
Server will grab all 7GB right off the bat and will no longer dynamically
manage it.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:%23XFFSaAAFHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a production SQL server with 4-cpu, 8GB runs under SQL Server
> Enterprise Edition & Windows 2000 Advance server.
> AWE is enabled.
> Max memory set to 7GB.
> When I look at the Task Manager the 'Mem usage' is only 110,632 k.
> Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
> I am thinking there may be memory leak in the server.
> My question is,
> 1. How can I find out if it is memory leak?
> 2. How to identify the process causing it?
> Thanks for the help.
> -sarav
>
>
>
>
>

memory probelm

Hi,
I have a production SQL server with 4-cpu, 8GB runs under SQL Server
Enterprise Edition & Windows 2000 Advance server.
AWE is enabled.
Max memory set to 7GB.
When I look at the Task Manager the 'Mem usage' is only 110,632 k.
Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
I am thinking there may be memory leak in the server.
My question is,
1. How can I find out if it is memory leak?
2. How to identify the process causing it?
Thanks for the help.
-saravTask manager is not the correct tool for monitoring memory usage with sql
server. Use perfmon counters instead. But when you use AWE memory Sql
Server will grab all 7GB right off the bat and will no longer dynamically
manage it.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:%23XFFSaAAFHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a production SQL server with 4-cpu, 8GB runs under SQL Server
> Enterprise Edition & Windows 2000 Advance server.
> AWE is enabled.
> Max memory set to 7GB.
> When I look at the Task Manager the 'Mem usage' is only 110,632 k.
> Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
> I am thinking there may be memory leak in the server.
> My question is,
> 1. How can I find out if it is memory leak?
> 2. How to identify the process causing it?
> Thanks for the help.
> -sarav
>
>
>
>
>|||Moreover, make sure you set BOTH the /3GB and /PAE switches in the BOOT.ini
file.
The /PAE switch is required by the OS in order to manage memory allocations
above 4GB. It does this by replacing the normal 32-bit address register
with a make-shift 36-bit register; thus, allowing applications written on
the extended API calls to address up to 64 GB of memory.
The /3GB switch forces the OS to reserve only 1GB for private (kernel)
addresses and 3GB for user addresses. Doing this will give you up to a 3GB
Buffer/Proc Cache instead of the default 2GB. This will give you an AWE
address space of only 4GB instead of 5GB, which would be better optimized.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoL31iAAFHA.1400@.TK2MSFTNGP11.phx.gbl...
Task manager is not the correct tool for monitoring memory usage with sql
server. Use perfmon counters instead. But when you use AWE memory Sql
Server will grab all 7GB right off the bat and will no longer dynamically
manage it.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:%23XFFSaAAFHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a production SQL server with 4-cpu, 8GB runs under SQL Server
> Enterprise Edition & Windows 2000 Advance server.
> AWE is enabled.
> Max memory set to 7GB.
> When I look at the Task Manager the 'Mem usage' is only 110,632 k.
> Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
> I am thinking there may be memory leak in the server.
> My question is,
> 1. How can I find out if it is memory leak?
> 2. How to identify the process causing it?
> Thanks for the help.
> -sarav
>
>
>
>
>

memory probelm

Hi,
I have a production SQL server with 4-cpu, 8GB runs under SQL Server
Enterprise Edition & Windows 2000 Advance server.
AWE is enabled.
Max memory set to 7GB.
When I look at the Task Manager the 'Mem usage' is only 110,632 k.
Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
I am thinking there may be memory leak in the server.
My question is,
1. How can I find out if it is memory leak?
2. How to identify the process causing it?
Thanks for the help.
-saravTask manager is not the correct tool for monitoring memory usage with sql
server. Use perfmon counters instead. But when you use AWE memory Sql
Server will grab all 7GB right off the bat and will no longer dynamically
manage it.
--
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:%23XFFSaAAFHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a production SQL server with 4-cpu, 8GB runs under SQL Server
> Enterprise Edition & Windows 2000 Advance server.
> AWE is enabled.
> Max memory set to 7GB.
> When I look at the Task Manager the 'Mem usage' is only 110,632 k.
> Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
> I am thinking there may be memory leak in the server.
> My question is,
> 1. How can I find out if it is memory leak?
> 2. How to identify the process causing it?
> Thanks for the help.
> -sarav
>
>
>
>
>|||Moreover, make sure you set BOTH the /3GB and /PAE switches in the BOOT.ini
file.
The /PAE switch is required by the OS in order to manage memory allocations
above 4GB. It does this by replacing the normal 32-bit address register
with a make-shift 36-bit register; thus, allowing applications written on
the extended API calls to address up to 64 GB of memory.
The /3GB switch forces the OS to reserve only 1GB for private (kernel)
addresses and 3GB for user addresses. Doing this will give you up to a 3GB
Buffer/Proc Cache instead of the default 2GB. This will give you an AWE
address space of only 4GB instead of 5GB, which would be better optimized.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoL31iAAFHA.1400@.TK2MSFTNGP11.phx.gbl...
Task manager is not the correct tool for monitoring memory usage with sql
server. Use perfmon counters instead. But when you use AWE memory Sql
Server will grab all 7GB right off the bat and will no longer dynamically
manage it.
--
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:%23XFFSaAAFHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a production SQL server with 4-cpu, 8GB runs under SQL Server
> Enterprise Edition & Windows 2000 Advance server.
> AWE is enabled.
> Max memory set to 7GB.
> When I look at the Task Manager the 'Mem usage' is only 110,632 k.
> Some of the SQL jobs run time went up from 1:30 min. to 6Hrs.
> I am thinking there may be memory leak in the server.
> My question is,
> 1. How can I find out if it is memory leak?
> 2. How to identify the process causing it?
> Thanks for the help.
> -sarav
>
>
>
>
>

Friday, February 24, 2012

Memory Pressure Question

"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>
|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"Patricia" wrote:
> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.

Memory not being used by SQL 2005 EE 64 bit instance

If anyone has a idea on what could be wrong please let me know. I changed the minimum to 2 GB but it still only shows it is using 124 MB.

See my post "Does anyone have sql2005 EE running on 64 bit Itanium server?"

Memory not being used by 2005 instance

We have an I64 server with 12 GB of Ram. On this server we have an instance
of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on from
SSMS. This instance seems to be running fine but is always using all the 6
GB of memory according to task manager (if I increase the max memory it will
use it). We also have an instance of SQL 2005 Enterprise with max memory set
to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on AWE
on 64 bit servers but I did just in case. I hope this is enough server info
so I'll get on with the problem. The 2005 instance doesn't seem to be
running very well and from the task manager I've never seen it use more then
124 MB of memory. It seems to have way too much I\O writes and between the
two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
Model 2 Stepping 1 1596 MHz CPUs.
I talked to someone at Microsoft and they said to ignore Task Manager reading
(nice). You can tell SQL memory usage by using Performance Monitor object
SQL Memory Management counters "Target Server Memory" and "Total Server
Memory". So, unless you are a DBA and know about this, Task Manager misleads
you.
"Kenny" wrote:

> We have an I64 server with 12 GB of Ram. On this server we have an instance
> of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on from
> SSMS. This instance seems to be running fine but is always using all the 6
> GB of memory according to task manager (if I increase the max memory it will
> use it). We also have an instance of SQL 2005 Enterprise with max memory set
> to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on AWE
> on 64 bit servers but I did just in case. I hope this is enough server info
> so I'll get on with the problem. The 2005 instance doesn't seem to be
> running very well and from the task manager I've never seen it use more then
> 124 MB of memory. It seems to have way too much I\O writes and between the
> two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
> Model 2 Stepping 1 1596 MHz CPUs.
>

Memory not being used by 2005 instance

We have an I64 server with 12 GB of Ram. On this server we have an instance
of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on from
SSMS. This instance seems to be running fine but is always using all the 6
GB of memory according to task manager (if I increase the max memory it will
use it). We also have an instance of SQL 2005 Enterprise with max memory se
t
to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on AW
E
on 64 bit servers but I did just in case. I hope this is enough server info
so I'll get on with the problem. The 2005 instance doesn't seem to be
running very well and from the task manager I've never seen it use more then
124 MB of memory. It seems to have way too much I\O writes and between the
two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
Model 2 Stepping 1 1596 MHz CPUs.I talked to someone at Microsoft and they said to ignore Task Manager readin
g
(nice). You can tell SQL memory usage by using Performance Monitor object
SQL Memory Management counters "Target Server Memory" and "Total Server
Memory". So, unless you are a DBA and know about this, Task Manager mislead
s
you.
"Kenny" wrote:

> We have an I64 server with 12 GB of Ram. On this server we have an instan
ce
> of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on fr
om
> SSMS. This instance seems to be running fine but is always using all the
6
> GB of memory according to task manager (if I increase the max memory it wi
ll
> use it). We also have an instance of SQL 2005 Enterprise with max memory
set
> to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on
AWE
> on 64 bit servers but I did just in case. I hope this is enough server in
fo
> so I'll get on with the problem. The 2005 instance doesn't seem to be
> running very well and from the task manager I've never seen it use more th
en
> 124 MB of memory. It seems to have way too much I\O writes and between th
e
> two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
> Model 2 Stepping 1 1596 MHz CPUs.
>

Memory not being used by 2005 instance

We have an I64 server with 12 GB of Ram. On this server we have an instance
of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on from
SSMS. This instance seems to be running fine but is always using all the 6
GB of memory according to task manager (if I increase the max memory it will
use it). We also have an instance of SQL 2005 Enterprise with max memory set
to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on AWE
on 64 bit servers but I did just in case. I hope this is enough server info
so I'll get on with the problem. The 2005 instance doesn't seem to be
running very well and from the task manager I've never seen it use more then
124 MB of memory. It seems to have way too much I\O writes and between the
two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
Model 2 Stepping 1 1596 MHz CPUs.I talked to someone at Microsoft and they said to ignore Task Manager reading
(nice). You can tell SQL memory usage by using Performance Monitor object
SQL Memory Management counters "Target Server Memory" and "Total Server
Memory". So, unless you are a DBA and know about this, Task Manager misleads
you.
"Kenny" wrote:
> We have an I64 server with 12 GB of Ram. On this server we have an instance
> of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on from
> SSMS. This instance seems to be running fine but is always using all the 6
> GB of memory according to task manager (if I increase the max memory it will
> use it). We also have an instance of SQL 2005 Enterprise with max memory set
> to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on AWE
> on 64 bit servers but I did just in case. I hope this is enough server info
> so I'll get on with the problem. The 2005 instance doesn't seem to be
> running very well and from the task manager I've never seen it use more then
> 124 MB of memory. It seems to have way too much I\O writes and between the
> two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
> Model 2 Stepping 1 1596 MHz CPUs.
>

Monday, February 20, 2012

Memory management (fixed memory, AWE)

Hi,

I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.

I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).

Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?

How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)

Thank

I forgot to write that SQL server is 2000 Standard Ed:

On Windows Server 2003, max memory is 2 or 4 GB

And in the case of SQL Server 2K5?

Thank

|||

I am a bit confused by your questions but I can tell you that SQL 2005 Std. edition UNLIKE 2000's max memory is only bound by the OS it runs on. Thus I have deployed sql 2005 std on 2003 server ent. with 16GBs of memory in them.

Derek

|||fasttrack, did this solve your problems? Can you update thread?|||

Thank,

how to solve:

/PAE for win server 2003 Enterprise edition.

If SQL server 2000 Std, not enable AW, because max memory is 4 gb.

If SQL server 2000 Ent AWE enable to take advantage of all memory available. Setted max memory for other applications.

If SQL server 2005, both std and Ent AWE is enable. Max memory setted for others applications

Memory management (fixed memory, AWE)

Hi,

I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.

I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).

Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?

How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)

Thank

I forgot to write that SQL server is 2000 Standard Ed:

On Windows Server 2003, max memory is 2 or 4 GB

And in the case of SQL Server 2K5?

Thank

|||

Derek

|||fasttrack, did this solve your problems? Can you update thread?|||

Thank,

how to solve:

/PAE for win server 2003 Enterprise edition.

If SQL server 2000 Std, not enable AW, because max memory is 4 gb.

If SQL server 2000 Ent AWE enable to take advantage of all memory available. Setted max memory for other applications.

If SQL server 2005, both std and Ent AWE is enable. Max memory setted for others applications

Wednesday, March 21, 2012

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

Monday, March 12, 2012

Memory Usage - PAE / AWE question

Memory Usage - PAE / AWE question

Memory Usage - PAE / AWE question

Friday, March 9, 2012

Memory Stress Test in SQL Server 2005

memory setting in SQL 2005 in Windows 2003

Wednesday, March 7, 2012

memory probelm

memory probelm

memory probelm

Friday, February 24, 2012

Memory Pressure Question

Memory Pressure Question

Memory Pressure Question

Memory not being used by SQL 2005 EE 64 bit instance

Memory not being used by 2005 instance

Memory not being used by 2005 instance

Memory not being used by 2005 instance

Monday, February 20, 2012

Memory management (fixed memory, AWE)

Memory management (fixed memory, AWE)

Sql Memory Leaks

Blog Archive

About Me