Sql Memory Leaks: sp4

Showing posts with label sp4. Show all posts

Wednesday, March 21, 2012

Memtoleave and -g-switch

hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
? Any suggestions for measuring the actual running value of this pool -
memtoleave ?

best regards /ti

The units for -g are MB. The default is 256.

Is this a 32 or 64 bit system?

How much memory is on the system?

Are you running SQL Server with AWE enabled?

|||

Could you run the following query (when you are having this problem) and gives us the results.

Code Snippet

SELECTtype, multi_pages_kb FROMsys.dm_os_memory_clerksWHERE multi_pages_kb > 0

ORDERBY multi_pages_kb DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

SQL 2000 does not have DMVs. There's no easy way to profile memory usage in SQL 2000. See http://msdn2.microsoft.com/en-US/library/aa175282(sql.80).aspx for more information

Thanks, Ron D.

|||Oops, I missed the 2000 part :-)

Memtoleave and -g-switch

best regards /ti

The units for -g are MB. The default is 256.

Is this a 32 or 64 bit system?

How much memory is on the system?

Are you running SQL Server with AWE enabled?

|||

Could you run the following query (when you are having this problem) and gives us the results.

Code Snippet

SELECT type, multi_pages_kb FROM sys.dm_os_memory_clerks WHERE multi_pages_kb > 0

ORDER BY multi_pages_kb DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

SQL 2000 does not have DMVs. There's no easy way to profile memory usage in SQL 2000. See http://msdn2.microsoft.com/en-US/library/aa175282(sql.80).aspx for more information

Thanks, Ron D.

|||Oops, I missed the 2000 part :-)

Monday, March 19, 2012

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong?
Tnx in advance!
/Magnus
Hi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:

> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong?
> Tnx in advance!
> /Magnus
>
>
|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:

>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong?
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/defaul...b;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:

> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage i
n
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doin
g
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:

>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory usage\Performance problem

I am experiencing the following problem;

I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;

select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC

There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong??

Tnx in advance!

/MagnusMagnus sterberg (magnus.osterberg@.abo.fi) writes:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
> in Task manager. It is ca 20 mb, everything is OK. Then I run this
> query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable
> contains ca 916.000 rows. I thought this query would still execute in
> only a few seconds, but it takes minutes. And the worst thing is that
> sqlserver.exe's memory usage grows to about 300-400 mb when the query
> runs. What am I doing wrong??

So what is the average row size of this table? Say that is 300 bytes,
then that is 300 MB of data to read. That is not very likely to be done
instantly.

SQL Server's memory consumption will increase, as it will read the entire
table into cache, and the table will stay in the cache as long as no
other data competes about the space. This means that if you resubmit the
query, the response time will be significantly shorter.

SQL Server is designed to grab as much memory it can, as the more data
in can have in cache, the better the response times. If there are other
applications competing for memory on the machine, SQL Server will yield,
but in this case it may be better to configure how much memory you want
SQL Server to use.

Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
to see what shape the table is in. To defragment it, you would have to
create a clustered index on the table, and then drop that index.

The query itself would benefit enormously by a non-clustered index on
postoffice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C183A0F9A0DYazorman@.127.0.0.1...
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> I am experiencing the following problem;
>>
>> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
>> in Task manager. It is ca 20 mb, everything is OK. Then I run this
>> query;
>>
>> select postoffice, count(*) as counter from tblTable
>> where postoffice is not null
>> group by postoffice
>> order by counter DESC
>>
>> There's no index or constraint on field "postoffice" and tblTable
>> contains ca 916.000 rows. I thought this query would still execute in
>> only a few seconds, but it takes minutes. And the worst thing is that
>> sqlserver.exe's memory usage grows to about 300-400 mb when the query
>> runs. What am I doing wrong??
> So what is the average row size of this table? Say that is 300 bytes,
> then that is 300 MB of data to read. That is not very likely to be done
> instantly.
> SQL Server's memory consumption will increase, as it will read the entire
> table into cache, and the table will stay in the cache as long as no
> other data competes about the space. This means that if you resubmit the
> query, the response time will be significantly shorter.
> SQL Server is designed to grab as much memory it can, as the more data
> in can have in cache, the better the response times. If there are other
> applications competing for memory on the machine, SQL Server will yield,
> but in this case it may be better to configure how much memory you want
> SQL Server to use.
> Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
> to see what shape the table is in. To defragment it, you would have to
> create a clustered index on the table, and then drop that index.
> The query itself would benefit enormously by a non-clustered index on
> postoffice.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks for a well-written answer!

I guess my expectations on selecting based on a non-indexed column were
somewhat high. When I added an index, my query executes in seconds.
One index solved this problem, but my table contains 30-40 similar columns,
and I don't think indexing every column is a good idea.

Isn't there any other ways of speeding up selects? Well, I guess not...

/Magnus|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I guess my expectations on selecting based on a non-indexed column were
> somewhat high. When I added an index, my query executes in seconds. One
> index solved this problem, but my table contains 30-40 similar columns,
> and I don't think indexing every column is a good idea.
> Isn't there any other ways of speeding up selects? Well, I guess not...

Well, once data is in cache it will be faster. Or at least less slow.

But if you need to do this on every column, it sounds to me like one
of those things Analysis Services is good for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Magnus sterberg wrote:
> I guess my expectations on selecting based on a non-indexed column
> were somewhat high. When I added an index, my query executes in
> seconds.
> One index solved this problem, but my table contains 30-40 similar
> columns, and I don't think indexing every column is a good idea.

Two things come to mind:

1. rethink your table design. If these columns are so similar you might
be better off with a second table which is joined. But that of course
depends on your data - just an idea.

2. Create indexes (possibly composite indexes) for most used queries. You
might even get away with a single (or few) composite index if your queries
only use a leading portion of this index's fields.

> Isn't there any other ways of speeding up selects? Well, I guess
> not...

Well, there are tons of other options, like having data files on several
disks, putting tx log on a separate disk, adding memory, ... It all
depends. :-)

Kind regards

robert|||Thank you Robert, these all are things that I'll keep in mind.

One more thing though; why is the sqlservr.exe process consuming 395 mb RAM
when the entire server is idle?
My "cron job" is inserting a few 1000 rows of data at night, but now it is
late morning here in Finland, and the entire machine is more or less idle.
On my other servers, the RAM consumption on idle is about 20-30 mb only.
Anyone got any ideas?

/Magnus

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3ngkc3F1dnm7U1@.individual.net...
> Magnus sterberg wrote:
>> I guess my expectations on selecting based on a non-indexed column
>> were somewhat high. When I added an index, my query executes in
>> seconds.
>> One index solved this problem, but my table contains 30-40 similar
>> columns, and I don't think indexing every column is a good idea.
> Two things come to mind:
> 1. rethink your table design. If these columns are so similar you might
> be better off with a second table which is joined. But that of course
> depends on your data - just an idea.
> 2. Create indexes (possibly composite indexes) for most used queries. You
> might even get away with a single (or few) composite index if your queries
> only use a leading portion of this index's fields.
>> Isn't there any other ways of speeding up selects? Well, I guess
>> not...
> Well, there are tons of other options, like having data files on several
> disks, putting tx log on a separate disk, adding memory, ... It all
> depends. :-)
> Kind regards
> robert|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> One more thing though; why is the sqlservr.exe process consuming 395 mb
> RAM when the entire server is idle?

I believe that was in my first reply.

SQL Server is designed to get as much memory as it can, and only yield
if an another application needs it. This is because it keeps data in
cache so that future requests for the same data can be answered without
reading from disk.

Thus, this is perfectly normal behaviour.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> One more thing though; why is the sqlservr.exe process consuming 395
>> mb RAM when the entire server is idle?
> I believe that was in my first reply.
> SQL Server is designed to get as much memory as it can, and only yield
> if an another application needs it. This is because it keeps data in
> cache so that future requests for the same data can be answered
> without reading from disk.
> Thus, this is perfectly normal behaviour.

Adding to that max memory consumption is easily configurable so if 400MB
is too much for you then simply turn that down.

Kind regards

robert

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus Ã?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus Österberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory Usage from sql server.

Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
On this server every day runs a process that take about 3 minutes to
finish. But once a month it takes about 30 min. or one hour to finish.
At this moment the only thing that help us to fix the problem is to
restart the server, so we have to restart the server almost once a
month.
Today, we were thinking if maybe the memory of the server is not been
freeing so, it is not enought and it has being free when the server is
restarted.
Do somebody know if sql server has some command that can help us to free
the memory or to check if this is busy and avoid to restart the server.
I'll apreciate a lot your help.
Regards,
*** Sent via Developersdex http://www.codecomments.com ***
My guess would be that you have other apps running onthe server and don't
leave enough for them to operate properly. If so try setting your max
memory setting to a few hundred MB less than the max and see if that helps.
YOU should also check for blocking during that time.
Andrew J. Kelly SQL MVP
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uJ$rY9MMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
> On this server every day runs a process that take about 3 minutes to
> finish. But once a month it takes about 30 min. or one hour to finish.
> At this moment the only thing that help us to fix the problem is to
> restart the server, so we have to restart the server almost once a
> month.
> Today, we were thinking if maybe the memory of the server is not been
> freeing so, it is not enought and it has being free when the server is
> restarted.
> Do somebody know if sql server has some command that can help us to free
> the memory or to check if this is busy and avoid to restart the server.
> I'll apreciate a lot your help.
> Regards,
>
> *** Sent via Developersdex http://www.codecomments.com ***

Memory Usage from sql server.

Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
On this server every day runs a process that take about 3 minutes to
finish. But once a month it takes about 30 min. or one hour to finish.
At this moment the only thing that help us to fix the problem is to
restart the server, so we have to restart the server almost once a
month.
Today, we were thinking if maybe the memory of the server is not been
freeing so, it is not enought and it has being free when the server is
restarted.
Do somebody know if sql server has some command that can help us to free
the memory or to check if this is busy and avoid to restart the server.
I'll apreciate a lot your help.
Regards,
*** Sent via Developersdex http://www.codecomments.com ***My guess would be that you have other apps running onthe server and don't
leave enough for them to operate properly. If so try setting your max
memory setting to a few hundred MB less than the max and see if that helps.
YOU should also check for blocking during that time.
Andrew J. Kelly SQL MVP
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uJ$rY9MMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
> On this server every day runs a process that take about 3 minutes to
> finish. But once a month it takes about 30 min. or one hour to finish.
> At this moment the only thing that help us to fix the problem is to
> restart the server, so we have to restart the server almost once a
> month.
> Today, we were thinking if maybe the memory of the server is not been
> freeing so, it is not enought and it has being free when the server is
> restarted.
> Do somebody know if sql server has some command that can help us to free
> the memory or to check if this is busy and avoid to restart the server.
> I'll apreciate a lot your help.
> Regards,
>
> *** Sent via Developersdex http://www.codecomments.com ***

Memory Usage from sql server.

Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
On this server every day runs a process that take about 3 minutes to
finish. But once a month it takes about 30 min. or one hour to finish.
At this moment the only thing that help us to fix the problem is to
restart the server, so we have to restart the server almost once a
month.
Today, we were thinking if maybe the memory of the server is not been
freeing so, it is not enought and it has being free when the server is
restarted.
Do somebody know if sql server has some command that can help us to free
the memory or to check if this is busy and avoid to restart the server.
I'll apreciate a lot your help.
Regards,
*** Sent via Developersdex http://www.developersdex.com ***My guess would be that you have other apps running onthe server and don't
leave enough for them to operate properly. If so try setting your max
memory setting to a few hundred MB less than the max and see if that helps.
YOU should also check for blocking during that time.
--
Andrew J. Kelly SQL MVP
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uJ$rY9MMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi, I have a server that has sql server2000 sp3 on win2000 server sp4.
> On this server every day runs a process that take about 3 minutes to
> finish. But once a month it takes about 30 min. or one hour to finish.
> At this moment the only thing that help us to fix the problem is to
> restart the server, so we have to restart the server almost once a
> month.
> Today, we were thinking if maybe the memory of the server is not been
> freeing so, it is not enought and it has being free when the server is
> restarted.
> Do somebody know if sql server has some command that can help us to free
> the memory or to check if this is busy and avoid to restart the server.
> I'll apreciate a lot your help.
> Regards,
>
> *** Sent via Developersdex http://www.developersdex.com ***

Monday, March 12, 2012

Memory Usage - PAE / AWE question

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

Memory Usage - PAE / AWE question

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

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

Memory Usage - PAE / AWE question

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

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

Friday, March 9, 2012

Memory Upgrade on SQL Server 2000

Hi,
I have a SQL Server 2000 SP4 with Hotfixes 2040 and 2187 applied running on
is Windows Server 2000. I recently moved a database to this server. This
database has around 50 users. The meory on this server is 1792 MB.
As users have been complaining of a slow response I checked the available
memory which is around 5 - 10 MB. The a processor is showing an average
utilization of 40 - 50%.
I am upgrading the memory to 4GB now. What are the settings do I need to
apply to get maximum advantage out of this increased memory on SQL Server?
Thanks in advance.I would like to add that it is SQL Server 2000 SP4 Enterprise Edition.
"sharman" wrote:
> Hi,
> I have a SQL Server 2000 SP4 with Hotfixes 2040 and 2187 applied running on
> is Windows Server 2000. I recently moved a database to this server. This
> database has around 50 users. The meory on this server is 1792 MB.
> As users have been complaining of a slow response I checked the available
> memory which is around 5 - 10 MB. The a processor is showing an average
> utilization of 40 - 50%.
> I am upgrading the memory to 4GB now. What are the settings do I need to
> apply to get maximum advantage out of this increased memory on SQL Server?
> Thanks in advance.|||Hi
http://support.microsoft.com/kb/274750
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:89B87DB7-46F9-4616-84CD-5DF9BD9F2D04@.microsoft.com...
> Hi,
> I have a SQL Server 2000 SP4 with Hotfixes 2040 and 2187 applied running
> on
> is Windows Server 2000. I recently moved a database to this server. This
> database has around 50 users. The meory on this server is 1792 MB.
> As users have been complaining of a slow response I checked the available
> memory which is around 5 - 10 MB. The a processor is showing an average
> utilization of 40 - 50%.
> I am upgrading the memory to 4GB now. What are the settings do I need to
> apply to get maximum advantage out of this increased memory on SQL Server?
> Thanks in advance.

Memory setting right ?

I have a few questions about memory setting inherited

Windows 2003, SQL Server SP4, both Enterprise Edition 8 GB RAM

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

4. Should I set max worker set also ?

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Thanks

Srinivas(masri999@.gmail.com) writes:

Quote:

Originally Posted by

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

This is in Books Online. It's 64 GB for Enterprise Edition, if memory
serves. (Either I look it up, or you do. It's under SQL Server
Architecture, Implementation details. Again, if memory serves.

Quote:

Originally Posted by

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

Yes, I believe you have to turn AWE to use all that memory.

Quote:

Originally Posted by

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

You should stick to 0, and let SQL Server configure it, unless you have
a sepecial reason.

Quote:

Originally Posted by

4. Should I set max worker set also ?

Don't know, but I have never heard of that.

Quote:

Originally Posted by

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Unfortunately, I never play with big servers myself. I've only been
eavesdropping on others who have been talking about them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland . See my Comments in [Sri]

Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

(masri999@.gmail.com) writes:

Quote:

Originally Posted by

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

>
This is in Books Online. It's 64 GB for Enterprise Edition, if memory
serves. (Either I look it up, or you do. It's under SQL Server
Architecture, Implementation details. Again, if memory serves.

[Sri] What I meant was that how much memory SQL Server can see. ie If I
put 8 GB on SQL Server System , whether SQL Server able to utilise all
the 8 GB installed and not wasted .

Quote:

Originally Posted by

Quote:

Originally Posted by

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

>
Yes, I believe you have to turn AWE to use all that memory.
>

Quote:

Originally Posted by

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

>
You should stick to 0, and let SQL Server configure it, unless you have
a sepecial reason.

[Sri] My information is that , once you turn AWE , you should turn off
Dynamically Configure memory . Otherwise SQL Server will utilise all
the memory and leaving 128 MB to O/S . Am I right ?

Quote:

Originally Posted by

Quote:

Originally Posted by

4. Should I set max worker set also ?

>
Don't know, but I have never heard of that.

[Sri] Typo Error . This should have been set working set size .Should I
set it to 1 or 0 ?

Quote:

Originally Posted by

Quote:

Originally Posted by

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

>
Unfortunately, I never play with big servers myself. I've only been
eavesdropping on others who have been talking about them.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(masri999@.gmail.com) writes:

Quote:

Originally Posted by

[Sri] My information is that , once you turn AWE , you should turn off
Dynamically Configure memory . Otherwise SQL Server will utilise all
the memory and leaving 128 MB to O/S . Am I right ?

Not entirely, but in essence. Since I don't this stuff too well, I looked in
Books Online. Check the topic "Using AWE Memory on Windows 2000" in Books
Online. There is also another configuration parameter "awe enabled" that
you need to change.

memory question day..

SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
Windows Server 2003 R2, Enterprise, 64bit
40gb ram in the system, no other apps.
I'm looking for the optimal settings for this box with regards to Min/Max,
AWE, Boot.ini file etc.
Sp_configure shows that whoever built this box set AWE =1, min and max -
33000000 (32.6gb)
The properties of the server, memory tab show the memory fixed at the full
40gb
perfmon shows the SQL memory Manager:total server memory counter at
39973432kb (38gb).
Not sure which GUI to trust here.
Thanks!
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available./pae in boot.ini?
Set min memory a gig or so less than max.
watch pages/sec in perfmon occassionally to ensure not paging.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23lIXDm4XIHA.6140@.TK2MSFTNGP02.phx.gbl...
> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> Windows Server 2003 R2, Enterprise, 64bit
> 40gb ram in the system, no other apps.
> I'm looking for the optimal settings for this box with regards to Min/Max,
> AWE, Boot.ini file etc.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
> The properties of the server, memory tab show the memory fixed at the full
> 40gb
> perfmon shows the SQL memory Manager:total server memory counter at
> 39973432kb (38gb).
> Not sure which GUI to trust here.
> Thanks!
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
>|||In this situation...would you set max at the total ram on the box? Seems
like you should specifically allow some room for the O/S...but I'm just not
all that familiar with 64 bit architecture
there are two lines in the boot.ini (alternate startup options)...one with
/pae, one without. Anyway to tell which was used? The one without is the
default
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13pkgobj171ir65@.corp.supernews.com...
> /pae in boot.ini?
> Set min memory a gig or so less than max.
> watch pages/sec in perfmon occassionally to ensure not paging.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23lIXDm4XIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max, AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>
>|||Note that the numbers for max/min server memory are in MB, not in KB.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
So you are setting max/min server memory to ~33,000GB, which basically
leaves it up to the SQL Server instance to grab as much memory as it needs
minus some for the OS. On a box like this, in most cases, I'd just leave 4GB
for the OS and set the rest for max/min server memory.
Linchi
"Kevin3NF" wrote:
> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> Windows Server 2003 R2, Enterprise, 64bit
> 40gb ram in the system, no other apps.
> I'm looking for the optimal settings for this box with regards to Min/Max,
> AWE, Boot.ini file etc.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
> The properties of the server, memory tab show the memory fixed at the full
> 40gb
> perfmon shows the SQL memory Manager:total server memory counter at
> 39973432kb (38gb).
> Not sure which GUI to trust here.
> Thanks!
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
>
>|||:) I didn't build this box, and just noticed that someone set it to
32TB...too funny.
Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
enabled.
Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
Thanks,
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
> Note that the numbers for max/min server memory are in MB, not in KB.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
> So you are setting max/min server memory to ~33,000GB, which basically
> leaves it up to the SQL Server instance to grab as much memory as it needs
> minus some for the OS. On a box like this, in most cases, I'd just leave
> 4GB
> for the OS and set the rest for max/min server memory.
> Linchi
> "Kevin3NF" wrote:
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max,
>> AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full
>> 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>|||> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
No need for /PAE.
Linchi
"Kevin3NF" wrote:
> :) I didn't build this box, and just noticed that someone set it to
> 32TB...too funny.
> Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
> enabled.
> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
> Thanks,
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
> > Note that the numbers for max/min server memory are in MB, not in KB.
> >
> >> Sp_configure shows that whoever built this box set AWE =1, min and max -
> >> 33000000 (32.6gb)
> >
> > So you are setting max/min server memory to ~33,000GB, which basically
> > leaves it up to the SQL Server instance to grab as much memory as it needs
> > minus some for the OS. On a box like this, in most cases, I'd just leave
> > 4GB
> > for the OS and set the rest for max/min server memory.
> >
> > Linchi
> >
> > "Kevin3NF" wrote:
> >
> >> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> >> Windows Server 2003 R2, Enterprise, 64bit
> >> 40gb ram in the system, no other apps.
> >>
> >> I'm looking for the optimal settings for this box with regards to
> >> Min/Max,
> >> AWE, Boot.ini file etc.
> >>
> >> Sp_configure shows that whoever built this box set AWE =1, min and max -
> >> 33000000 (32.6gb)
> >>
> >> The properties of the server, memory tab show the memory fixed at the
> >> full
> >> 40gb
> >>
> >> perfmon shows the SQL memory Manager:total server memory counter at
> >> 39973432kb (38gb).
> >>
> >> Not sure which GUI to trust here.
> >>
> >> Thanks!
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >>
> >>
>
>|||/PAE is an operating system flag, that changes the amount of directly
addressable memory for a 32-bit OS, for all applications. It has nothing
specific to do with SQL Server. So if Windows is 64-bit, there is no need
for /PAE.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eFkqEi5XIHA.5980@.TK2MSFTNGP04.phx.gbl...
> :) I didn't build this box, and just noticed that someone set it to
> 32TB...too funny.
> Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
> enabled.
> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
> Thanks,
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
>> Note that the numbers for max/min server memory are in MB, not in KB.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> So you are setting max/min server memory to ~33,000GB, which basically
>> leaves it up to the SQL Server instance to grab as much memory as it
>> needs
>> minus some for the OS. On a box like this, in most cases, I'd just leave
>> 4GB
>> for the OS and set the rest for max/min server memory.
>> Linchi
>> "Kevin3NF" wrote:
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max,
>> AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full
>> 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>
>

Wednesday, March 7, 2012

Memory Question

This question is aimed to the experienced DBAs in server
configuration:
We currently have the following configuration:
-. Windows 2000 Server SP4
-. SQL Server Standard SP3
-. 1GB RAM
We are going to upgrade the RAM size. According with Books
On Line, SQL Server supports up to 2GB.
My question is, if we put 4GB on the server, SQL Server
will use 2GB max and Windows would be able to use the
other 2 Gigs?
or
The server (Windows and SQL Server) will not see more than
2GB alltogether?
Thanks in advance.Hope you will get you answer here:
http://support.microsoft.com/?id=274750
"DBA" <anonymous@.discussions.microsoft.com> wrote in message
news:00e201c39800$7cb7fb40$a501280a@.phx.gbl...
> This question is aimed to the experienced DBAs in server
> configuration:
> We currently have the following configuration:
> -. Windows 2000 Server SP4
> -. SQL Server Standard SP3
> -. 1GB RAM
> We are going to upgrade the RAM size. According with Books
> On Line, SQL Server supports up to 2GB.
> My question is, if we put 4GB on the server, SQL Server
> will use 2GB max and Windows would be able to use the
> other 2 Gigs?
> or
> The server (Windows and SQL Server) will not see more than
> 2GB alltogether?
> Thanks in advance.

Memory Problem

Hello,
I look after several SQL server DB's, all are SQL 2000 standard edition,
some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
fine and dandy, except for one application that's causing me a headache. The
SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
and sits on a dual processor box with 2GB of RAM.
Through profiler and performance monitor I can see the app logs into SQL,
runs a sql statement, and logs out again - it does this circa 250 times every
30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
release it, seemingly it's due to this login process (on perfmon I can see
when the logins occur every 30 seconds or so, and using taskmanager I can see
inline with this login process the memory sqlservr.exe uses increases). The
memory increases at a rate of around 116mb per hour, and when it hits the
physical limit, SQL crashes. The actual error in the log relates to
CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
over.
Anyone any ideas why this is happening?Sounds like you hit a bug in SQL Server. Are you current on Service Pack? Did you search KB for the
related error messages? If you still didn't find anything, you should open a case with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:A8248C82-7633-4092-B334-B910B47FE063@.microsoft.com...
> Hello,
> I look after several SQL server DB's, all are SQL 2000 standard edition,
> some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
> fine and dandy, except for one application that's causing me a headache. The
> SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
> and sits on a dual processor box with 2GB of RAM.
> Through profiler and performance monitor I can see the app logs into SQL,
> runs a sql statement, and logs out again - it does this circa 250 times every
> 30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
> release it, seemingly it's due to this login process (on perfmon I can see
> when the logins occur every 30 seconds or so, and using taskmanager I can see
> inline with this login process the memory sqlservr.exe uses increases). The
> memory increases at a rate of around 116mb per hour, and when it hits the
> physical limit, SQL crashes. The actual error in the log relates to
> CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
> over.
> Anyone any ideas why this is happening?|||Thanks for the reply Tibor, We're on SP4 (which is current), Can't find any
KB articles that cover the issue, can't find anything on web at all really (a
part from a couple with slight relavance)
"Tibor Karaszi" wrote:
> Sounds like you hit a bug in SQL Server. Are you current on Service Pack? Did you search KB for the
> related error messages? If you still didn't find anything, you should open a case with MS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:A8248C82-7633-4092-B334-B910B47FE063@.microsoft.com...
> > Hello,
> >
> > I look after several SQL server DB's, all are SQL 2000 standard edition,
> > some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
> > fine and dandy, except for one application that's causing me a headache. The
> > SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
> > and sits on a dual processor box with 2GB of RAM.
> > Through profiler and performance monitor I can see the app logs into SQL,
> > runs a sql statement, and logs out again - it does this circa 250 times every
> > 30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
> > release it, seemingly it's due to this login process (on perfmon I can see
> > when the logins occur every 30 seconds or so, and using taskmanager I can see
> > inline with this login process the memory sqlservr.exe uses increases). The
> > memory increases at a rate of around 116mb per hour, and when it hits the
> > physical limit, SQL crashes. The actual error in the log relates to
> > CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
> > over.
> >
> > Anyone any ideas why this is happening?
>
>|||I did a quick Google search and I guess we found similar cases. Not exactly your case. My guess is
that your code hit a memory leak in that code section of SQL Server, and that you would need
assistance from MS to either work around it or have them prepare a hotfix for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:8391C053-4B6B-4AEF-8193-8FAC4B056B87@.microsoft.com...
> Thanks for the reply Tibor, We're on SP4 (which is current), Can't find any
> KB articles that cover the issue, can't find anything on web at all really (a
> part from a couple with slight relavance)
> "Tibor Karaszi" wrote:
>> Sounds like you hit a bug in SQL Server. Are you current on Service Pack? Did you search KB for
>> the
>> related error messages? If you still didn't find anything, you should open a case with MS.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:A8248C82-7633-4092-B334-B910B47FE063@.microsoft.com...
>> > Hello,
>> >
>> > I look after several SQL server DB's, all are SQL 2000 standard edition,
>> > some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
>> > fine and dandy, except for one application that's causing me a headache. The
>> > SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
>> > and sits on a dual processor box with 2GB of RAM.
>> > Through profiler and performance monitor I can see the app logs into SQL,
>> > runs a sql statement, and logs out again - it does this circa 250 times every
>> > 30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
>> > release it, seemingly it's due to this login process (on perfmon I can see
>> > when the logins occur every 30 seconds or so, and using taskmanager I can see
>> > inline with this login process the memory sqlservr.exe uses increases). The
>> > memory increases at a rate of around 116mb per hour, and when it hits the
>> > physical limit, SQL crashes. The actual error in the log relates to
>> > CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
>> > over.
>> >
>> > Anyone any ideas why this is happening?
>>|||Thanks for your help Tibor :-)
"Tibor Karaszi" wrote:
> I did a quick Google search and I guess we found similar cases. Not exactly your case. My guess is
> that your code hit a memory leak in that code section of SQL Server, and that you would need
> assistance from MS to either work around it or have them prepare a hotfix for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:8391C053-4B6B-4AEF-8193-8FAC4B056B87@.microsoft.com...
> > Thanks for the reply Tibor, We're on SP4 (which is current), Can't find any
> > KB articles that cover the issue, can't find anything on web at all really (a
> > part from a couple with slight relavance)
> >
> > "Tibor Karaszi" wrote:
> >
> >> Sounds like you hit a bug in SQL Server. Are you current on Service Pack? Did you search KB for
> >> the
> >> related error messages? If you still didn't find anything, you should open a case with MS.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:A8248C82-7633-4092-B334-B910B47FE063@.microsoft.com...
> >> > Hello,
> >> >
> >> > I look after several SQL server DB's, all are SQL 2000 standard edition,
> >> > some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
> >> > fine and dandy, except for one application that's causing me a headache. The
> >> > SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
> >> > and sits on a dual processor box with 2GB of RAM.
> >> > Through profiler and performance monitor I can see the app logs into SQL,
> >> > runs a sql statement, and logs out again - it does this circa 250 times every
> >> > 30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
> >> > release it, seemingly it's due to this login process (on perfmon I can see
> >> > when the logins occur every 30 seconds or so, and using taskmanager I can see
> >> > inline with this login process the memory sqlservr.exe uses increases). The
> >> > memory increases at a rate of around 116mb per hour, and when it hits the
> >> > physical limit, SQL crashes. The actual error in the log relates to
> >> > CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
> >> > over.
> >> >
> >> > Anyone any ideas why this is happening?
> >>
> >>
> >>
>
>|||If you do get a fix would you mind posting it so we can see what
happened....thanks.
--
burt_king@.yahoo.com
"BenUK" wrote:
> Thanks for your help Tibor :-)
> "Tibor Karaszi" wrote:
> > I did a quick Google search and I guess we found similar cases. Not exactly your case. My guess is
> > that your code hit a memory leak in that code section of SQL Server, and that you would need
> > assistance from MS to either work around it or have them prepare a hotfix for you.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> > news:8391C053-4B6B-4AEF-8193-8FAC4B056B87@.microsoft.com...
> > > Thanks for the reply Tibor, We're on SP4 (which is current), Can't find any
> > > KB articles that cover the issue, can't find anything on web at all really (a
> > > part from a couple with slight relavance)
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> Sounds like you hit a bug in SQL Server. Are you current on Service Pack? Did you search KB for
> > >> the
> > >> related error messages? If you still didn't find anything, you should open a case with MS.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> > >> news:A8248C82-7633-4092-B334-B910B47FE063@.microsoft.com...
> > >> > Hello,
> > >> >
> > >> > I look after several SQL server DB's, all are SQL 2000 standard edition,
> > >> > some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
> > >> > fine and dandy, except for one application that's causing me a headache. The
> > >> > SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
> > >> > and sits on a dual processor box with 2GB of RAM.
> > >> > Through profiler and performance monitor I can see the app logs into SQL,
> > >> > runs a sql statement, and logs out again - it does this circa 250 times every
> > >> > 30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
> > >> > release it, seemingly it's due to this login process (on perfmon I can see
> > >> > when the logins occur every 30 seconds or so, and using taskmanager I can see
> > >> > inline with this login process the memory sqlservr.exe uses increases). The
> > >> > memory increases at a rate of around 116mb per hour, and when it hits the
> > >> > physical limit, SQL crashes. The actual error in the log relates to
> > >> > CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
> > >> > over.
> > >> >
> > >> > Anyone any ideas why this is happening?
> > >>
> > >>
> > >>
> >
> >
> >|||no problem, will do
"burt_king" wrote:
> If you do get a fix would you mind posting it so we can see what
> happened....thanks.
> --
> burt_king@.yahoo.com
>
> "BenUK" wrote:
> > Thanks for your help Tibor :-)
> >
> > "Tibor Karaszi" wrote:
> >
> > > I did a quick Google search and I guess we found similar cases. Not exactly your case. My guess is
> > > that your code hit a memory leak in that code section of SQL Server, and that you would need
> > > assistance from MS to either work around it or have them prepare a hotfix for you.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> > > news:8391C053-4B6B-4AEF-8193-8FAC4B056B87@.microsoft.com...
> > > > Thanks for the reply Tibor, We're on SP4 (which is current), Can't find any
> > > > KB articles that cover the issue, can't find anything on web at all really (a
> > > > part from a couple with slight relavance)
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> Sounds like you hit a bug in SQL Server. Are you current on Service Pack? Did you search KB for
> > > >> the
> > > >> related error messages? If you still didn't find anything, you should open a case with MS.
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://www.solidqualitylearning.com/
> > > >>
> > > >>
> > > >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> > > >> news:A8248C82-7633-4092-B334-B910B47FE063@.microsoft.com...
> > > >> > Hello,
> > > >> >
> > > >> > I look after several SQL server DB's, all are SQL 2000 standard edition,
> > > >> > some are SP3, some are SP4, some are Win 2000, some are Win 2003. All is
> > > >> > fine and dandy, except for one application that's causing me a headache. The
> > > >> > SQL DB behind the app is SQL 2000 standard edition SP4, it's on Win 2003 SP1,
> > > >> > and sits on a dual processor box with 2GB of RAM.
> > > >> > Through profiler and performance monitor I can see the app logs into SQL,
> > > >> > runs a sql statement, and logs out again - it does this circa 250 times every
> > > >> > 30 seconds. The problem I have is SQL keeps on grabbing memory and doesn't
> > > >> > release it, seemingly it's due to this login process (on perfmon I can see
> > > >> > when the logins occur every 30 seconds or so, and using taskmanager I can see
> > > >> > inline with this login process the memory sqlservr.exe uses increases). The
> > > >> > memory increases at a rate of around 116mb per hour, and when it hits the
> > > >> > physical limit, SQL crashes. The actual error in the log relates to
> > > >> > CImageHelper:: and dbghelp.dll and gets posted several times before SQL falls
> > > >> > over.
> > > >> >
> > > >> > Anyone any ideas why this is happening?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||You can increase machine to 3GB, and let SQL Server use full 2gb and leave
1GB for OS.

Memory Problem

Friday, February 24, 2012

Memory Pressure Question

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

"Patricia" wrote:

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

Wednesday, March 21, 2012

Memtoleave and -g-switch

Memtoleave and -g-switch

Monday, March 19, 2012

Memory usage\Performance problem

Memory usage\Performance problem

Memory usage\Performance problem

Memory usage\Performance problem

Memory Usage from sql server.

Memory Usage from sql server.

Memory Usage from sql server.

Monday, March 12, 2012

Memory Usage - PAE / AWE question

Memory Usage - PAE / AWE question

Memory Usage - PAE / AWE question

Friday, March 9, 2012

Memory Upgrade on SQL Server 2000

Memory setting right ?

memory question day..

Wednesday, March 7, 2012

Memory Question

Memory Problem

Memory Problem

Memory Problem

Friday, February 24, 2012

Memory Pressure Question

Sql Memory Leaks

Blog Archive

About Me