Memory Pressure Question

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

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

Memory Pressure Question

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

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

Memory Pressure Question

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

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/de...v_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.
Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:

> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/de...v_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:
> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/d...ev_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:

> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/d...ev_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> & #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory pressure

When I issue the command, sp_browsereplcmds, it returns
Msg 6513, Level 16, State 27, Procedure sp_browsereplcmds, Line 110
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to
memory pressure. Please restart SQL server in Address Windowing Extensions
(AWE) mode to use CLR integration features.
Kindly advise. Thanks in advance
Stephanie,
please do some monitoring of your server as in this article:
http://msdn2.microsoft.com/en-us/library/ms176018.aspx. It'll give some more
info about what is happening over time.
Also check out how much memory is in your server, and what other processes
are running (eg is it also an exchange server or webserver)?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

memory portion tied to -g switch of sqlservr

I want to know what this is virtual memory address space is . Where can i
understand more about this particular allocation and why just 256MB is given
for it ?
Just a bit confused
Bunch of good articles here:
http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:eW40%23lP4FHA.2640@.TK2MSFTNGP09.phx.gbl...
>I want to know what this is virtual memory address space is . Where can i understand more about
>this particular allocation and why just 256MB is given for it ?
> Just a bit confused
>

memory portion tied to -g switch of sqlservr

I want to know what this is virtual memory address space is . Where can i
understand more about this particular allocation and why just 256MB is given
for it ?
Just a bit confusedBunch of good articles here:
http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:eW40%23lP4FHA.2640@.TK2MSFTNGP09.phx.g
bl...
>I want to know what this is virtual memory address space is . Where can i u
nderstand more about
>this particular allocation and why just 256MB is given for it ?
> Just a bit confused
>

memory portion tied to -g switch of sqlservr

I want to know what this is virtual memory address space is . Where can i
understand more about this particular allocation and why just 256MB is given
for it ?
Just a bit confusedBunch of good articles here:
http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:eW40%23lP4FHA.2640@.TK2MSFTNGP09.phx.gbl...
>I want to know what this is virtual memory address space is . Where can i understand more about
>this particular allocation and why just 256MB is given for it ?
> Just a bit confused
>

Memory Performance counter?

Can anyone tell me what is considered a bad number for the "Memory:
Pages/sec" counter? Microsoft indicates a number greater than zero by a small
amount is acceptable, but what number is a "small amount"?
Thanks RichDBA wrote:
> Can anyone tell me what is considered a bad number for the "Memory:
> Pages/sec" counter? Microsoft indicates a number greater than zero by a small
> amount is acceptable, but what number is a "small amount"?
> Thanks Rich
It depends - you first need to determine what is "normal" for your
server. A lot of paging activity usually, but not always, indicates
that something other than SQL is demanding memory. For instance, on my
servers, I have no page swapping during the majority of the day. During
the period of time when my tape backup agent is backing up the SQL
backups, then I'll get a flurry of paging activity. When backups
complete, the paging stops.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The Memory:pages/sec counter indicates hard paging activities. On a box
dedicated to SQL Server, if its value is greater than zero for any sustained
period of time, you should look into it.
Linchi
"DBA" wrote:
> Can anyone tell me what is considered a bad number for the "Memory:
> Pages/sec" counter? Microsoft indicates a number greater than zero by a small
> amount is acceptable, but what number is a "small amount"?
> Thanks Rich|||Those pages are actually the backup files being written. The write
mechanism used by SQL Backup triggers this counter. This happens both for
local file or tape writes and network file writes. It isn't caused by
memory pressure, it is just an artifact of writing backup files. As such,
you have to disregard the non-zero counters when a SQL is running an active
backup. Yet another operating complication with SQL.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:enjwmcBsGHA.2240@.TK2MSFTNGP04.phx.gbl...
> DBA wrote:
>> Can anyone tell me what is considered a bad number for the "Memory:
>> Pages/sec" counter? Microsoft indicates a number greater than zero by a
>> small amount is acceptable, but what number is a "small amount"? Thanks
>> Rich
> It depends - you first need to determine what is "normal" for your server.
> A lot of paging activity usually, but not always, indicates that something
> other than SQL is demanding memory. For instance, on my servers, I have
> no page swapping during the majority of the day. During the period of
> time when my tape backup agent is backing up the SQL backups, then I'll
> get a flurry of paging activity. When backups complete, the paging stops.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Geoff N. Hiten wrote:
> Those pages are actually the backup files being written. The write
> mechanism used by SQL Backup triggers this counter. This happens both for
> local file or tape writes and network file writes. It isn't caused by
> memory pressure, it is just an artifact of writing backup files. As such,
> you have to disregard the non-zero counters when a SQL is running an active
> backup. Yet another operating complication with SQL.
>
That was basically the point I was trying to make. Also, I don't see
excessive paging when *SQL* is running a backup, I see paging when my
tape backup agent is backing up the SQL backups. I dump SQL backups to
disk, then back those dumps up to tape.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||We're running sql 2000 on 64bit itanium server with 12gig ram. We have batch
processes that run all hours of the day as well internet users hitting the db
via a web application. The memory Pages/Sec are consistently between 3000 and
6000. Is this reasonable or excessive?
Thanks Rich
"Linchi Shea" wrote:
> The Memory:pages/sec counter indicates hard paging activities. On a box
> dedicated to SQL Server, if its value is greater than zero for any sustained
> period of time, you should look into it.
> Linchi
> "DBA" wrote:
> > Can anyone tell me what is considered a bad number for the "Memory:
> > Pages/sec" counter? Microsoft indicates a number greater than zero by a small
> > amount is acceptable, but what number is a "small amount"?
> >
> > Thanks Rich

Memory Performance counter?

Can anyone tell me what is considered a bad number for the "Memory:
Pages/sec" counter? Microsoft indicates a number greater than zero by a smal
l
amount is acceptable, but what number is a "small amount"?
Thanks RichDBA wrote:
> Can anyone tell me what is considered a bad number for the "Memory:
> Pages/sec" counter? Microsoft indicates a number greater than zero by a sm
all
> amount is acceptable, but what number is a "small amount"?
> Thanks Rich
It depends - you first need to determine what is "normal" for your
server. A lot of paging activity usually, but not always, indicates
that something other than SQL is demanding memory. For instance, on my
servers, I have no page swapping during the majority of the day. During
the period of time when my tape backup agent is backing up the SQL
backups, then I'll get a flurry of paging activity. When backups
complete, the paging stops.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The Memory:pages/sec counter indicates hard paging activities. On a box
dedicated to SQL Server, if its value is greater than zero for any sustained
period of time, you should look into it.
Linchi
"DBA" wrote:

> Can anyone tell me what is considered a bad number for the "Memory:
> Pages/sec" counter? Microsoft indicates a number greater than zero by a sm
all
> amount is acceptable, but what number is a "small amount"?
> Thanks Rich|||Those pages are actually the backup files being written. The write
mechanism used by SQL Backup triggers this counter. This happens both for
local file or tape writes and network file writes. It isn't caused by
memory pressure, it is just an artifact of writing backup files. As such,
you have to disregard the non-zero counters when a SQL is running an active
backup. Yet another operating complication with SQL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:enjwmcBsGHA.2240@.TK2MSFTNGP04.phx.gbl...
> DBA wrote:
> It depends - you first need to determine what is "normal" for your server.
> A lot of paging activity usually, but not always, indicates that something
> other than SQL is demanding memory. For instance, on my servers, I have
> no page swapping during the majority of the day. During the period of
> time when my tape backup agent is backing up the SQL backups, then I'll
> get a flurry of paging activity. When backups complete, the paging stops.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Geoff N. Hiten wrote:
> Those pages are actually the backup files being written. The write
> mechanism used by SQL Backup triggers this counter. This happens both fo
r
> local file or tape writes and network file writes. It isn't caused by
> memory pressure, it is just an artifact of writing backup files. As such,
> you have to disregard the non-zero counters when a SQL is running an activ
e
> backup. Yet another operating complication with SQL.
>
That was basically the point I was trying to make. Also, I don't see
excessive paging when *SQL* is running a backup, I see paging when my
tape backup agent is backing up the SQL backups. I dump SQL backups to
disk, then back those dumps up to tape.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||We're running sql 2000 on 64bit itanium server with 12gig ram. We have batch
processes that run all hours of the day as well internet users hitting the d
b
via a web application. The memory Pages/Sec are consistently between 3000 an
d
6000. Is this reasonable or excessive?
Thanks Rich
"Linchi Shea" wrote:
[vbcol=seagreen]
> The Memory:pages/sec counter indicates hard paging activities. On a box
> dedicated to SQL Server, if its value is greater than zero for any sustain
ed
> period of time, you should look into it.
> Linchi
> "DBA" wrote:
>

memory performance

The server is a SQL2k standard edition SP3, on Win2k server.
I have 4GB physical memory and 5GB page file.
I know that SQL2k standard edition can only utilize 2GB memory.
Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
The working set maximum is 1779MB and pagefults/sec maximum is 34.
Do I have memory problem?
Thanks in advance.
Looks fine to me.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:BC14859F-F926-484A-9DFD-569C301A43E6@.microsoft.com...
> The server is a SQL2k standard edition SP3, on Win2k server.
> I have 4GB physical memory and 5GB page file.
> I know that SQL2k standard edition can only utilize 2GB memory.
> Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
> The working set maximum is 1779MB and pagefults/sec maximum is 34.
> Do I have memory problem?
> Thanks in advance.

memory performance

The server is a SQL2k standard edition SP3, on Win2k server.
I have 4GB physical memory and 5GB page file.
I know that SQL2k standard edition can only utilize 2GB memory.
Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
The working set maximum is 1779MB and pagefults/sec maximum is 34.
Do I have memory problem?
Thanks in advance.Looks fine to me.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:BC14859F-F926-484A-9DFD-569C301A43E6@.microsoft.com...
> The server is a SQL2k standard edition SP3, on Win2k server.
> I have 4GB physical memory and 5GB page file.
> I know that SQL2k standard edition can only utilize 2GB memory.
> Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
> The working set maximum is 1779MB and pagefults/sec maximum is 34.
> Do I have memory problem?
> Thanks in advance.

Memory pb

Hie,
When i restrict the memory of the db in property dialog box, the process sqlserv.exe increase his memry above this limit why ?Have you enabled AWE? That pretty much circumvents the normal Windows process management, mostly because that is exactly what it is supposed to do!

Even if you haven't enabled AWE, the server responds to its environment. If large blocks of RAM are not being used, then the OS can elect to dynamically ease the restrictions on an application that is memory starved (which happens fast with a busy SQL Server).

-PatP

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAM
What is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAMWhat is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAMWhat is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory Paging Issue

We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I know
that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
shown below:
Averaged of 24 hour period:
Page Reads/sec: 39.287
Pages Input/sec: 438.466
I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
2000 Enterprise Edition. I have now been asked to give management some idea
of when this configuration will be to limited and prove to them that this
will eliminate the paging issues.
Anybody have any ideas on how I can intelligently give Management this
information? Does Microsoft have any Capacity Planning White Papers?First, what happens to the application when it starts
paging? What behaviors do you notice? Have you done any
other monitoring?
Since SQL 2000 is a different beast than SQL 7, start your
numbers based on what you see in SQL 7 - with N number of
users doing X, Y, and Z type of work, we max out at 4
proc/2 GB for SQL. But that really is a guesstimate.
What you need to honestly do is understand why your app is
paging - is the code written poorly? Sure, throwing new
OS, SQL, and HW at it may solve your problem, but it may
not. You should definitively prove that. As much as I
love SQL 2K, moving for the sake of moving is never a good
idea.
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||sql server by itself should not cause paging,
are there other applications that could be generating the
paging activity?
are there extended stored procs?
if there a large difference between the memory usage and
virtual memory in task manager?
have you checked the perfmon counters for Process - sql
servr Page File Bytes and Page Faults?
I like many of the features in SQL Server 2000, but agree
with Allan
in any case the SQL Serve version should not be related to
the paging,
your SQL instance may certainly benefit from more memory,
but should show in the physical disk counters, not the
memory paging counters
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||I do know that the code is very poorly written and there are over 200
databases on the server. I have recommended that they need to move some of
the databases to a different server. All 200+ are heavily used. I recently
counted over 2 million queries/Stored Procs that were run in a 1 hour
period. The only thing running on the server is SQL. I have monitored CPU
and it is actually in good shape, the only problem is paging and during the
periods of paging, extreme slowness. So far I have been unable to corelate
the paging with any particular query/stored proc.
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:010f01c3be76$2731a850$a301280a@.phx.gbl...
> First, what happens to the application when it starts
> paging? What behaviors do you notice? Have you done any
> other monitoring?
> Since SQL 2000 is a different beast than SQL 7, start your
> numbers based on what you see in SQL 7 - with N number of
> users doing X, Y, and Z type of work, we max out at 4
> proc/2 GB for SQL. But that really is a guesstimate.
> What you need to honestly do is understand why your app is
> paging - is the code written poorly? Sure, throwing new
> OS, SQL, and HW at it may solve your problem, but it may
> not. You should definitively prove that. As much as I
> love SQL 2K, moving for the sake of moving is never a good
> idea.
>
> >--Original Message--
> >We currently are using SQL 7.0 Standard Edition w/3 gigs
> of ram (yes, I know
> >that SQL is only using 2 gigs) and 4 cpu's. I am having
> paging issues as
> >shown below:
> >
> >Averaged of 24 hour period:
> > Page Reads/sec: 39.287
> > Pages Input/sec: 438.466
> >
> >I have recommended we go to a server with 8 gigs of ram,
> 8 cpu's and SQL
> >2000 Enterprise Edition. I have now been asked to give
> management some idea
> >of when this configuration will be to limited and prove
> to them that this
> >will eliminate the paging issues.
> >
> >Anybody have any ideas on how I can intelligently give
> Management this
> >information? Does Microsoft have any Capacity Planning
> White Papers?
> >
> >
> >.
> >|||SQL should not page in that configuration if it's the only app on there.
Are you seeing high disk queuing on the page file disk and have correleted
spikes in those counters with performance problems as well? If not, I
wouldn't be overly concerned about those numbers.
A couple things that causes fairly steady paging I have seen is file server
or disk-tape backup activities, but those generally won't cause severe
performance problems.
"Mike Johnson" <mj@.noemail.com> wrote in message
news:%23V8An$mvDHA.2464@.TK2MSFTNGP12.phx.gbl...
> We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I
know
> that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
> shown below:
> Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
> I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
> 2000 Enterprise Edition. I have now been asked to give management some
idea
> of when this configuration will be to limited and prove to them that this
> will eliminate the paging issues.
> Anybody have any ideas on how I can intelligently give Management this
> information? Does Microsoft have any Capacity Planning White Papers?
>

Memory Paging

I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
that is almost constantly paging OS processes (>500pg/sec 6-10 times an
hour).
Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major paging
culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have to
have the MOM stuff on there.
What are the other 2, and do they need to be running on a box dedicated to
SQL?
Any other ideas for helping me lower my paging rate?
The paging is really not that excessive. But in order to reduce paging you
need to ensure there is always enough memory for the apps other than SQL
Server as well as the OS. So 2GB might not be enough to satisfy these other
apps. I don't know how much MOM requires but I am suppressed the 2GB is not
enough for these.
Andrew J. Kelly SQL MVP
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?
|||wmiprvse is a process that is an instance of a wmi provider
(there are some trojans/worms that hide in this proccess also)
but it is more likely mom is using this provider.
RemoteRegistry does what its name implies, it lets users access the registry
remotely....it is a service which CAN be disabled...but i dont know about
moms dependencies on this either.
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?

Memory Paging

I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
that is almost constantly paging OS processes (>500pg/sec 6-10 times an
hour).
Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major paging
culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have t
o
have the MOM stuff on there.
What are the other 2, and do they need to be running on a box dedicated to
SQL?
Any other ideas for helping me lower my paging rate?The paging is really not that excessive. But in order to reduce paging you
need to ensure there is always enough memory for the apps other than SQL
Server as well as the OS. So 2GB might not be enough to satisfy these other
apps. I don't know how much MOM requires but I am suppressed the 2GB is not
enough for these.
Andrew J. Kelly SQL MVP
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?|||wmiprvse is a process that is an instance of a wmi provider
(there are some trojans/worms that hide in this proccess also)
but it is more likely mom is using this provider.
RemoteRegistry does what its name implies, it lets users access the registry
remotely....it is a service which CAN be disabled...but i dont know about
moms dependencies on this either.
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?

Memory Paging

I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
that is almost constantly paging OS processes (>500pg/sec 6-10 times an
hour).
Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major paging
culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have to
have the MOM stuff on there.
What are the other 2, and do they need to be running on a box dedicated to
SQL?
Any other ideas for helping me lower my paging rate?The paging is really not that excessive. But in order to reduce paging you
need to ensure there is always enough memory for the apps other than SQL
Server as well as the OS. So 2GB might not be enough to satisfy these other
apps. I don't know how much MOM requires but I am suppressed the 2GB is not
enough for these.
--
Andrew J. Kelly SQL MVP
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?|||wmiprvse is a process that is an instance of a wmi provider
(there are some trojans/worms that hide in this proccess also)
but it is more likely mom is using this provider.
RemoteRegistry does what its name implies, it lets users access the registry
remotely....it is a service which CAN be disabled...but i dont know about
moms dependencies on this either.
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?

Memory Orphan Connections

Does SQL Server 2000 clean up broken or orphan connections
and lease their corresponding memory?
Is there a Microsoft Article that goes into this topic.
Thanks,
Mark P.
Basically yes. When a connection is broken SQL Server will rollback any
open transactions for that connection and any memory used as such will be
available for use by other connections.
Andrew J. Kelly SQL MVP
"Mark P." <anonymous@.discussions.microsoft.com> wrote in message
news:34f501c49cfc$55623e50$a601280a@.phx.gbl...
> Does SQL Server 2000 clean up broken or orphan connections
> and lease their corresponding memory?
> Is there a Microsoft Article that goes into this topic.
> Thanks,
> Mark P.

Memory Orphan Connections

Does SQL Server 2000 clean up broken or orphan connections
and lease their corresponding memory?
Is there a Microsoft Article that goes into this topic.
Thanks,
Mark P.Basically yes. When a connection is broken SQL Server will rollback any
open transactions for that connection and any memory used as such will be
available for use by other connections.
--
Andrew J. Kelly SQL MVP
"Mark P." <anonymous@.discussions.microsoft.com> wrote in message
news:34f501c49cfc$55623e50$a601280a@.phx.gbl...
> Does SQL Server 2000 clean up broken or orphan connections
> and lease their corresponding memory?
> Is there a Microsoft Article that goes into this topic.
> Thanks,
> Mark P.

Memory optimization

Hi
We are running full text search with 4GB RAM on SQL server 2000. Ihave the
/3G option on in boot.ini. The VM setting is set at 2048 to 4095.
I read it in the manual that the optimal configuration for full text search
is
1. The virtual memory size to at least 3 times the physical memory installed
in the computer.
2. The SQL Server max server memory server configuration option to 1.5 times
the physical memory (half the virtual memory size setting).
Questions:
1. How do I set max sql server memory to 1.5 times physical memory? Max
memory that SQL server allows us to set is 4095 MB? Is this only possible
with AWE?
2. My observation when I made the VM 3X the physical, was that the full text
queries were slower.
What are the right settings and what am I doing wrong?
Matt
Hi
Can any body throw some ideas on this one?
MM
"ISTS" wrote:

> Hi
> We are running full text search with 4GB RAM on SQL server 2000. Ihave the
> /3G option on in boot.ini. The VM setting is set at 2048 to 4095.
> I read it in the manual that the optimal configuration for full text search
> is
> 1. The virtual memory size to at least 3 times the physical memory installed
> in the computer.
> 2. The SQL Server max server memory server configuration option to 1.5 times
> the physical memory (half the virtual memory size setting).
> Questions:
> 1. How do I set max sql server memory to 1.5 times physical memory? Max
> memory that SQL server allows us to set is 4095 MB? Is this only possible
> with AWE?
> 2. My observation when I made the VM 3X the physical, was that the full text
> queries were slower.
> What are the right settings and what am I doing wrong?
> --
> Matt

memory on server

I have an application that is used 85 % of the time by 25 % of the people in
the organization.
we have three servers, all having 2000 server and sql 2000 server the main
one is on SP2 and the two terminal servers have sp4
main server has 2048 meg of ram and ts1 has 3840 meg and ts2 has 2048 meg of
ram in it.
the main server utilization runs anywhere from 20% to 100%. I am purposing
adding another 2048 in the main server, is that enough?
Kevin
kevins (kevins@.discussions.microsoft.com) writes:
> I have an application that is used 85 % of the time by 25 % of the
> people in the organization.
> we have three servers, all having 2000 server and sql 2000 server the
> main one is on SP2 and the two terminal servers have sp4 main server has
> 2048 meg of ram and ts1 has 3840 meg and ts2 has 2048 meg of ram in it.
> the main server utilization runs anywhere from 20% to 100%. I am
> purposing adding another 2048 in the main server, is that enough?
When it comes to memory for SQL Server, too much is not enough! :-)
Here is the deal: SQL Server loves cache. The more data it can hold in
cache, the less often it has to read from disk. Thus, once SQL Server
has maxed out on memory, it will stay there (unless there is pressure
from other applications.)
Thus, if you get another 2 GB of memory, you may see that SQL Server
till maxes out. This all depends on your databasees and your application.
If you have a 100 GB database of which all data is accessed at some
point during the day, you will have to access disk accesses. On the
other hand, if your only database is 2GB, those extra 2GB will not have
that much effect, as you already have the database in memory.
Note also that you need Enterprise Edition to be able to make use from
more than 2GB memory. There are also switches in Windows you need to set,
to able to use this memory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

memory on server

I have an application that is used 85 % of the time by 25 % of the people in
the organization.
we have three servers, all having 2000 server and sql 2000 server the main
one is on SP2 and the two terminal servers have sp4
main server has 2048 meg of ram and ts1 has 3840 meg and ts2 has 2048 meg of
ram in it.
the main server utilization runs anywhere from 20% to 100%. I am purposing
adding another 2048 in the main server, is that enough?
Kevinkevins (kevins@.discussions.microsoft.com) writes:
> I have an application that is used 85 % of the time by 25 % of the
> people in the organization.
> we have three servers, all having 2000 server and sql 2000 server the
> main one is on SP2 and the two terminal servers have sp4 main server has
> 2048 meg of ram and ts1 has 3840 meg and ts2 has 2048 meg of ram in it.
> the main server utilization runs anywhere from 20% to 100%. I am
> purposing adding another 2048 in the main server, is that enough?
When it comes to memory for SQL Server, too much is not enough! :-)
Here is the deal: SQL Server loves cache. The more data it can hold in
cache, the less often it has to read from disk. Thus, once SQL Server
has maxed out on memory, it will stay there (unless there is pressure
from other applications.)
Thus, if you get another 2 GB of memory, you may see that SQL Server
till maxes out. This all depends on your databasees and your application.
If you have a 100 GB database of which all data is accessed at some
point during the day, you will have to access disk accesses. On the
other hand, if your only database is 2GB, those extra 2GB will not have
that much effect, as you already have the database in memory.
Note also that you need Enterprise Edition to be able to make use from
more than 2GB memory. There are also switches in Windows you need to set,
to able to use this memory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

memory on server

I have an application that is used 85 % of the time by 25 % of the people in
the organization.
we have three servers, all having 2000 server and sql 2000 server the main
one is on SP2 and the two terminal servers have sp4
main server has 2048 meg of ram and ts1 has 3840 meg and ts2 has 2048 meg of
ram in it.
the main server utilization runs anywhere from 20% to 100%. I am purposing
adding another 2048 in the main server, is that enough?
Kevinkevins (kevins@.discussions.microsoft.com) writes:
> I have an application that is used 85 % of the time by 25 % of the
> people in the organization.
> we have three servers, all having 2000 server and sql 2000 server the
> main one is on SP2 and the two terminal servers have sp4 main server has
> 2048 meg of ram and ts1 has 3840 meg and ts2 has 2048 meg of ram in it.
> the main server utilization runs anywhere from 20% to 100%. I am
> purposing adding another 2048 in the main server, is that enough?
When it comes to memory for SQL Server, too much is not enough! :-)
Here is the deal: SQL Server loves cache. The more data it can hold in
cache, the less often it has to read from disk. Thus, once SQL Server
has maxed out on memory, it will stay there (unless there is pressure
from other applications.)
Thus, if you get another 2 GB of memory, you may see that SQL Server
till maxes out. This all depends on your databasees and your application.
If you have a 100 GB database of which all data is accessed at some
point during the day, you will have to access disk accesses. On the
other hand, if your only database is 2GB, those extra 2GB will not have
that much effect, as you already have the database in memory.
Note also that you need Enterprise Edition to be able to make use from
more than 2GB memory. There are also switches in Windows you need to set,
to able to use this memory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creating execution plans are
expensive operations. This is why SQL Server caches plans and pages. How would SQL Server know when
to release something? SQL Server will release memory when the OS becomes pressured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.developersdex.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creating execution plans are
expensive operations. This is why SQL Server caches plans and pages. How would SQL Server know when
to release something? SQL Server will release memory when the OS becomes pressured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max server memory).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
>> For example, thought the day SQL needs more memory and at
>> the end of the day has 1 gig RAM used - if everyone logs out of our
>> application so nothing is hitting SQL, will the memory be released? If
>> not, why not?
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
>> maximum set in the Management Studio. DOes it ever release and free up
>> the memory. For example, thought the day SQL needs more memory and at
>> the end of the day has 1 gig RAM used - if everyone logs out of our
>> application so nothing is hitting SQL, will the memory be released? If
>> not, why not?
>> Darin
>> *** Sent via Developersdex http://www.developersdex.com ***
>

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creat
ing execution plans are
expensive operations. This is why SQL Server caches plans and pages. How wou
ld SQL Server know when
to release something? SQL Server will release memory when the OS becomes pre
ssured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max
server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.
gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>

Memory of sqlserver.exe cannot released

Hi everybody

Help me, Help me.
When I backup the MSSQL databases using ARCServe 2000 in the server, the memory of sqlserver.exe is 1.5G in task manager. After backup was finished, the memory of sqlserver.exe cannot released. It is still 1.5G. Our users can get the resource of that server but they get it slowly. I need to stop and restart the MSSQL service. The server can back to normal.
Can everyone fix it ?

My server is
Windows 2000 Server Standard Edition with SP4
Windows SQL 2000 Standard Edition With SP3
Memory is 2G RAM

Pls Help.
ThanksDo you want to stop and restart (http://msdn2.microsoft.com/en-us/library/ms187598.aspx) SQL Server from a script?

-PatP|||PatP
Thank you for your reply
I got a one problem. I must need to restart the service using password because this is security reason. So, how to write a script with password|||Hi Everybody

I want to reinstall ARCserve 2000 on the server but I lost the license key.
I cannot register it. Can I get or export or read the license key / serial number on existing ARCserve 2000 of server for reinstallation ?

Thank you|||You don't need a password to stop or restart SQL Server. If you are using a Windows Service login, then the password is stored by the service itself. If you need to run the script as a machine administrator, then that password needs to be stored with the Windows Task description. You don't need to code the password into the script, that's a very bad idea and should be avoided.

-PatP|||could the problem be on the arcserv end. i do not know arcserv, but i am wondering if it is leaving connections open or something like that. have you tried opening up profiler to see what is going on?|||Hi Pat
Thanks
A applications is running with connecting the SQL server on that server. If the sql service is restarted, I need to provide two passwords for application to run it. Although the sql service can use the script automatically, the application on that server need to be ran manually every time.

Memory of SQL server

The configuration of SQL server is
1. Windows 2000 server Standard version
2. SQL server 2000 standard
3. 3G memory
4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
i have allocated all the memory to the SQL server at Enterprise manager.
i find at task manager the SQL uses up 1.8G memory at maximum.
The database size at disk is 600m
i find the server runs a query at 30 minutes.
After reboot, the query runs at 15 minutues, maybe release of some memory.
Can i say the memory is not sufficient for the SQL?
If yes, i know there is memory limitation from standard version of Windows
2K and SQL 2K, should i consider Enterprise version of Windows Server and
SQL to allow more memory.
Grateful if you could kindly give me some advices. Thanks.
Tony
Tony
First of all I'd suggest you staring to tune a 'bad' query , see if the
optomizer uses indexes , how efficient is an execution plan?
"tony wong" <x34@.netvigator.com> wrote in message
news:ebKNJg3NHHA.5064@.TK2MSFTNGP04.phx.gbl...
> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>
|||On Sun, 14 Jan 2007 09:05:08 +0800, "tony wong" <x34@.netvigator.com>
wrote:

>The configuration of SQL server is
>1. Windows 2000 server Standard version
>2. SQL server 2000 standard
>3. 3G memory
>4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
>i have allocated all the memory to the SQL server at Enterprise manager.
>i find at task manager the SQL uses up 1.8G memory at maximum.
>The database size at disk is 600m
>i find the server runs a query at 30 minutes.
>After reboot, the query runs at 15 minutues, maybe release of some memory.
>Can i say the memory is not sufficient for the SQL?
>If yes, i know there is memory limitation from standard version of Windows
>2K and SQL 2K, should i consider Enterprise version of Windows Server and
>SQL to allow more memory.
>Grateful if you could kindly give me some advices. Thanks.
Generally you'd expect it to run slower after reboot, as the cache
would be empty and physical IO is a lot slower.
I'd say Uri has the right idea, you might be producing a huge join
that preferentially should be kept in memory, or missing an index that
causes some other unnatural memory/cache configuration to be optimal.
Generally these can be addressed by tuning the query.
Of course, a multitude of sins can be hidden if you have enough RAM!
J.
|||> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues,
Can this be consistently reproduced?
Linchi
"tony wong" wrote:

> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>
>

Memory of SQL server

The configuration of SQL server is
1. Windows 2000 server Standard version
2. SQL server 2000 standard
3. 3G memory
4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
i have allocated all the memory to the SQL server at Enterprise manager.
i find at task manager the SQL uses up 1.8G memory at maximum.
The database size at disk is 600m
i find the server runs a query at 30 minutes.
After reboot, the query runs at 15 minutues, maybe release of some memory.
Can i say the memory is not sufficient for the SQL?
If yes, i know there is memory limitation from standard version of Windows
2K and SQL 2K, should i consider Enterprise version of Windows Server and
SQL to allow more memory.
Grateful if you could kindly give me some advices. Thanks.
TonyTony
First of all I'd suggest you staring to tune a 'bad' query , see if the
optomizer uses indexes , how efficient is an execution plan?
"tony wong" <x34@.netvigator.com> wrote in message
news:ebKNJg3NHHA.5064@.TK2MSFTNGP04.phx.gbl...
> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>|||On Sun, 14 Jan 2007 09:05:08 +0800, "tony wong" <x34@.netvigator.com>
wrote:

>The configuration of SQL server is
>1. Windows 2000 server Standard version
>2. SQL server 2000 standard
>3. 3G memory
>4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
>i have allocated all the memory to the SQL server at Enterprise manager.
>i find at task manager the SQL uses up 1.8G memory at maximum.
>The database size at disk is 600m
>i find the server runs a query at 30 minutes.
>After reboot, the query runs at 15 minutues, maybe release of some memory.
>Can i say the memory is not sufficient for the SQL?
>If yes, i know there is memory limitation from standard version of Windows
>2K and SQL 2K, should i consider Enterprise version of Windows Server and
>SQL to allow more memory.
>Grateful if you could kindly give me some advices. Thanks.
Generally you'd expect it to run slower after reboot, as the cache
would be empty and physical IO is a lot slower.
I'd say Uri has the right idea, you might be producing a huge join
that preferentially should be kept in memory, or missing an index that
causes some other unnatural memory/cache configuration to be optimal.
Generally these can be addressed by tuning the query.
Of course, a multitude of sins can be hidden if you have enough RAM!
J.|||> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues,
Can this be consistently reproduced?
Linchi
"tony wong" wrote:

> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>
>