Sql Memory Leaks: ram

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

Memory Utilization

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

To 'Prove' it, you need data. These articles will point you in the right direction to get some data.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

You can look at some easy PerfMon counters, such as Buffer Cache Hit Ratio, Page Life Expectancy, and Memory Grants Pending to get a better idea about whether you are seeing memory pressure. Memory pressure will also cause increased IO pressure.

Generally speaking, you can hardly go wrong by adding more RAM to SQL Server, especially if you only have`2GB of RAM installed.

Memory Utilization

Guys,
I have a server running MS-SQL SP3. The server configuration is a Dual-Xeon
2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
can use the whole memory for the SQL Server service...
Just as a 2GB server that I had before, monitoring with Task Manager I see
that the memory utilization don't grow up more than 1,84gb... Even with huge
processing and disk reading... So my question is, is there any kind of
special configuration that you must set to let server use the whole memory?
I know about AWE, but I thought AWE is just for servers who break the 4GB
boundary... Would like to know why the memory utilization don't go upper than
1,84gb, even setting this as a fixed value in Server configuration...
Thx,
Rafa
Hi,
What edition of SQL Server you are using? Only Enterprise or Developer
editions of SQL server supports more than 2 GB memory.
Sheck the version of SQL Server using
Select @.@.version
Thanks
Hari
SQL Server MVP
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa
|||According to Books Online
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\architec.chm::/8_ar_ts_8dbn.htm)
the Standard Edition of SQL Server will not use more than 2GB of RAM.
Keith
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa
|||Take a look at the following URL
http://support.microsoft.com/default...b;en-us;274750
There is a \3GB switch
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa
|||Hi,
Try /3gb switch in boot.ini
"Rafa?" wrote:

> Guys,
> I have a server running MS-SQL SP3. The server configuration is a Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa
|||Well, it's the Enterprise Edition...
What does /3gb in boot.ini?
"Wayne Snyder" wrote:

> Take a look at the following URL
> http://support.microsoft.com/default...b;en-us;274750
> There is a \3GB switch
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Dual-Xeon
> huge
> memory?
> than
>
>
|||Since you are running on Enterprise Edition you could try using /3GB and or
/PAE within boot.ini.
You will then probably want to specify the memory usage within SQL Server
Using AWE Memory on Windows 2000
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\architec.chm::/8_ar_sa_6b3k.htm
Managing AWE Memory
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adminsql.chm::/ad_1_server_1fnd.htm
Keith
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa
|||Keith,
Why would you use the /PAE and AWE switches if his server only has 4 GB RAM.
Those settings are for servers with more than 4GB.
"Keith Kratochvil" wrote:

> Since you are running on Enterprise Edition you could try using /3GB and or
> /PAE within boot.ini.
> You will then probably want to specify the memory usage within SQL Server
> Using AWE Memory on Windows 2000
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\architec.chm::/8_ar_sa_6b3k.htm
> Managing AWE Memory
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adminsql.chm::/ad_1_server_1fnd.htm
> --
> Keith
>
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
>
>

Memory Utilization

Guys,
I have a server running MS-SQL SP3. The server configuration is a Dual-Xeon
2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
can use the whole memory for the SQL Server service...
Just as a 2GB server that I had before, monitoring with Task Manager I see
that the memory utilization don't grow up more than 1,84gb... Even with huge
processing and disk reading... So my question is, is there any kind of
special configuration that you must set to let server use the whole memory?
I know about AWE, but I thought AWE is just for servers who break the 4GB
boundary... Would like to know why the memory utilization don't go upper tha
n
1,84gb, even setting this as a fixed value in Server configuration...
Thx,
RafaHi,
What edition of SQL Server you are using? Only Enterprise or Developer
editions of SQL server supports more than 2 GB memory.
Sheck the version of SQL Server using
Select @.@.version
Thanks
Hari
SQL Server MVP
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||According to Books Online
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar
chitec.chm::/8_ar_ts_8dbn.htm)
the Standard Edition of SQL Server will not use more than 2GB of RAM.
Keith
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Take a look at the following URL
http://support.microsoft.com/defaul...kb;en-us;274750
There is a \3GB switch
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Hi,
Try /3gb switch in boot.ini
"Rafa?" wrote:

> Guys,
> I have a server running MS-SQL SP3. The server configuration is a Dual-Xeo
n
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with hu
ge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole memory
?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper t
han
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Well, it's the Enterprise Edition...
What does /3gb in boot.ini?
"Wayne Snyder" wrote:

> Take a look at the following URL
> http://support.microsoft.com/defaul...kb;en-us;274750
> There is a \3GB switch
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Dual-Xeon
> huge
> memory?
> than
>
>|||Since you are running on Enterprise Edition you could try using /3GB and or
/PAE within boot.ini.
You will then probably want to specify the memory usage within SQL Server
Using AWE Memory on Windows 2000
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\arc
hitec.chm::/8_ar_sa_6b3k.htm
Managing AWE Memory
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_1_server_1fnd.htm
Keith
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Keith,
Why would you use the /PAE and AWE switches if his server only has 4 GB RAM.
Those settings are for servers with more than 4GB.
"Keith Kratochvil" wrote:

> Since you are running on Enterprise Edition you could try using /3GB and
or
> /PAE within boot.ini.
> You will then probably want to specify the memory usage within SQL Server
> Using AWE Memory on Windows 2000
> mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\a
rchitec.chm::/8_ar_sa_6b3k.htm
> Managing AWE Memory
> mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\a
dminsql.chm::/ad_1_server_1fnd.htm
> --
> Keith
>
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
>
>

Memory Utilization

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

To 'Prove' it, you need data. These articles will point you in the right direction to get some data.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

Generally speaking, you can hardly go wrong by adding more RAM to SQL Server, especially if you only have`2GB of RAM installed.

Memory Utilization

Guys,
I have a server running MS-SQL SP3. The server configuration is a Dual-Xeon
2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
can use the whole memory for the SQL Server service...
Just as a 2GB server that I had before, monitoring with Task Manager I see
that the memory utilization don't grow up more than 1,84gb... Even with huge
processing and disk reading... So my question is, is there any kind of
special configuration that you must set to let server use the whole memory?
I know about AWE, but I thought AWE is just for servers who break the 4GB
boundary... Would like to know why the memory utilization don't go upper than
1,84gb, even setting this as a fixed value in Server configuration...
Thx,
RafaHi,
What edition of SQL Server you are using? Only Enterprise or Developer
editions of SQL server supports more than 2 GB memory.
Sheck the version of SQL Server using
Select @.@.version
Thanks
Hari
SQL Server MVP
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Take a look at the following URL
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
There is a \3GB switch
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||According to Books Online
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\architec.chm::/8_ar_ts_8dbn.htm)
the Standard Edition of SQL Server will not use more than 2GB of RAM.
--
Keith
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Hi,
Try /3gb switch in boot.ini
"RafaÂ®" wrote:
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Well, it's the Enterprise Edition...
What does /3gb in boot.ini?
"Wayne Snyder" wrote:
> Take a look at the following URL
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
> There is a \3GB switch
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "RafaÂ®" <Rafa@.discussions.microsoft.com> wrote in message
> news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> > Guys,
> > I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> > 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> > can use the whole memory for the SQL Server service...
> >
> > Just as a 2GB server that I had before, monitoring with Task Manager I see
> > that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> > processing and disk reading... So my question is, is there any kind of
> > special configuration that you must set to let server use the whole
> memory?
> >
> > I know about AWE, but I thought AWE is just for servers who break the 4GB
> > boundary... Would like to know why the memory utilization don't go upper
> than
> > 1,84gb, even setting this as a fixed value in Server configuration...
> >
> > Thx,
> > Rafa
>
>|||Since you are running on Enterprise Edition you could try using /3GB and or
/PAE within boot.ini.
You will then probably want to specify the memory usage within SQL Server
Using AWE Memory on Windows 2000
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\architec.chm::/8_ar_sa_6b3k.htm
Managing AWE Memory
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_1_server_1fnd.htm
--
Keith
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> Guys,
> I have a server running MS-SQL SP3. The server configuration is a
> Dual-Xeon
> 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> can use the whole memory for the SQL Server service...
> Just as a 2GB server that I had before, monitoring with Task Manager I see
> that the memory utilization don't grow up more than 1,84gb... Even with
> huge
> processing and disk reading... So my question is, is there any kind of
> special configuration that you must set to let server use the whole
> memory?
> I know about AWE, but I thought AWE is just for servers who break the 4GB
> boundary... Would like to know why the memory utilization don't go upper
> than
> 1,84gb, even setting this as a fixed value in Server configuration...
> Thx,
> Rafa|||Keith,
Why would you use the /PAE and AWE switches if his server only has 4 GB RAM.
Those settings are for servers with more than 4GB.
"Keith Kratochvil" wrote:
> Since you are running on Enterprise Edition you could try using /3GB and or
> /PAE within boot.ini.
> You will then probably want to specify the memory usage within SQL Server
> Using AWE Memory on Windows 2000
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\architec.chm::/8_ar_sa_6b3k.htm
> Managing AWE Memory
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_1_server_1fnd.htm
> --
> Keith
>
> "RafaÂ®" <Rafa@.discussions.microsoft.com> wrote in message
> news:98A36C40-7BAF-4883-902D-D01BFE02FEFB@.microsoft.com...
> > Guys,
> > I have a server running MS-SQL SP3. The server configuration is a
> > Dual-Xeon
> > 2.4Ghz, with 4GB RAM. The server is reserved for running SQL Server, so he
> > can use the whole memory for the SQL Server service...
> >
> > Just as a 2GB server that I had before, monitoring with Task Manager I see
> > that the memory utilization don't grow up more than 1,84gb... Even with
> > huge
> > processing and disk reading... So my question is, is there any kind of
> > special configuration that you must set to let server use the whole
> > memory?
> >
> > I know about AWE, but I thought AWE is just for servers who break the 4GB
> > boundary... Would like to know why the memory utilization don't go upper
> > than
> > 1,84gb, even setting this as a fixed value in Server configuration...
> >
> > Thx,
> > Rafa
>
>

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.
Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Monday, March 19, 2012

memory usage??

Hello:
I have a server with almost 4 Gigs of RAM. Though SQL
Server's memory config option allows me to allocate all of
the available memory, I understand that Std. Ed. can only
address upto a max. of 2 GB.
So, I set this option to 2 GB and lo and behold, once the
application floodgates were opened, SQL's target and total
server memory consumption rose to and continues to hover
around the 1.7 GB mark.
At this time, I try to run a 'select' against a view and
after a minute of execution, I get error 701 (There is
insufficient system memory to run this query).
My understanding is that, while SQL will take as much
memory as it can and will probably hold on to it as well,
it will release chunks of memory back to the OS for other
processes. In this though, the process call was from
within SQL Server during a low peak application usage time.
To make my long story short, I'm not sure if SQL Server is
holding on to something, which is avoiding this query to
be executed successfully... OR, if the application has
some lingering processes against SQL, which coul be
causing it grief.
I hope this makes sense. And thank you in advance for all
your responses.to be more precise, SE allows 2GB of address space, which
can be mapped to physical memory, but not always.
by default, s2k allocates 256MB of address space to memory
structures other than the buffer cache. In many cases,
only a small amount of the 256M of address space for other
structures actually gets mapped to physical memory.
hence the common observation of 1.74GB of memory actually
being used by sql svr.
what is your query doing?
does it access a very large number of rows?
does it involve a large amount of intermediate data?
it could be your query requires a very large amount of the
memory structures other than the buffer cache.
does your query run immediately after SQL starts up,
before other users drive up memory usage (to the buffer
cache) ie, does this error message only happen after a
very large amount of memory is used by SQL.
if so, then your app would probably run best on a full 64-
bit OS/APP, the AWE is of no use
>--Original Message--
>Hello:
>I have a server with almost 4 Gigs of RAM. Though SQL
>Server's memory config option allows me to allocate all
of
>the available memory, I understand that Std. Ed. can only
>address upto a max. of 2 GB.
>So, I set this option to 2 GB and lo and behold, once the
>application floodgates were opened, SQL's target and
total
>server memory consumption rose to and continues to hover
>around the 1.7 GB mark.
>At this time, I try to run a 'select' against a view and
>after a minute of execution, I get error 701 (There is
>insufficient system memory to run this query).
>My understanding is that, while SQL will take as much
>memory as it can and will probably hold on to it as well,
>it will release chunks of memory back to the OS for other
>processes. In this though, the process call was from
>within SQL Server during a low peak application usage
time.
>To make my long story short, I'm not sure if SQL Server
is
>holding on to something, which is avoiding this query to
>be executed successfully... OR, if the application has
>some lingering processes against SQL, which coul be
>causing it grief.
>I hope this makes sense. And thank you in advance for all
>your responses.
>.
>

Memory Usage, SBS 2000 & SQL 2000

Hello,
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
ChrisI expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
--
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> Hello,
>>
>> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
>> processor box with 2GB of RAM) continues to grow over a few day period.
>> I
>> have double-checked to make sure that SP3a is installed and am trying to
>> double-check to make sure that MDAC 2.71 is installed (correctly).
>> First,
>> what's the best way to assure that each of these are installed without
>> errors or issues? Second, if those are then, what else could be causing
> the
>> memory to continue to grow over time?
>>
>> I know that I can limit the SQL configuration to 1GB (for example) but
>> that's not really the key problem here. As I write this the memory has
>> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
>> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
>> Adding RAM is obviously an option but that looks like it's only going to
> be
>> a band-aid to the problem because it will just continue to eat the
>> additional memory unless I lock the SQL RAM usage however I feel the key
>> issue to determine why the RAM is continuing to grow day-by-day.
>>
>> Thanks in advance!
>>
>> Chris
>>
>

Wednesday, March 21, 2012

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

Memory Utilization

Memory Utilization

Memory Utilization

Memory Utilization

Memory Utilization

Memory useage

Memory useage

Memory useage

Monday, March 19, 2012

memory usage??

Memory Usage, SBS 2000 & SQL 2000

Sql Memory Leaks

Blog Archive

About Me