Memory Stress Test in SQL Server 2005

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

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

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

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

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

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

Thanks, Ron D.

memory probelm

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

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

memory probelm

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

memory probelm

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

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.