Sql Memory Leaks: default

Showing posts with label default. Show all posts

Monday, March 26, 2012

merge agent not started - Start Synchronizing disabled

i'm running a SQL Server 2000 SP42000.8.00.2039, developer.
i'm trying to setup a merge replication between the default istance and a named one of the same machine, using the same domain account for both publisher and subscriber.
when it comes to "start synchronizing" (right click on the subscriber merge agent), i found it disabled, just like it is running, but it is not (the icon shows a stop sign, even refreshing.)
The state is "disactivated". When i stop sync and start it again things don't change.
If i setup a "pull" subscription, i get a sequence of a message like "interface not supported" (a translation from italian "Interfaccia non supportata"): after 10 attempts, it stops.
i cannot find this error searching in internet, could you address me?
thanks in advance!How did you set up replication, using scripts or Wizards?|||i used wizards.
i noticed a difference: while in this SQL Server installation the replication won't start at all, it works seamlessly on the SQL Server installed in the domain server.
Maybe it's a matter of DNS.sql

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 26, 2012

merge agent not started - Start Synchronizing disabled

Wednesday, March 21, 2012

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

MemToLeave in SS2KEE with AWE

Sql Memory Leaks

Blog Archive

About Me