memory  
Author Message
sophieee!





PostPosted: Wed Nov 24 10:33:08 CST 2004 Top

SQL Server >> memory

Hi everyone,

I have one production server where users are complaining
of slow downs. I am wondering if the problem could be
caused by insuficient memory.SQL Server is configured to
dinamicaly adjust the amount of memory used based upon
demand. From task menager I can see that SQL Server always
uses more then available physical memery ( RAM) and has to
go to the pagefile. How can I minimize or eliminate
pagefile IO ?
I am also confused whit Curent disk Queue lenght counter
always being in the range for all processors. If i have a
problem with memory Curent Disk Queue lenght should be out
of the range right ?

Thanks very much,
Mirna

SQL Server37  
 
 
SasanSaidi





PostPosted: Wed Nov 24 10:33:08 CST 2004 Top

SQL Server >> memory Hi Mirna,

One way to find out if SQL Server is bottlenecked on RAM is to check these
two Performance Monitor counters: SQLServer:Memory Manager: Total Server
Memory (KB) and SQLServer:Memory Manager: Target Server Memory (KB).

The first counter, SQLServer:Memory Manager: Total Server Memory (KB), tells
you how much memory, in KB, that SQL Server is currently using. The second
counter, SQLServer:Memory Manager: Target Server Memory (KB), tells you how
much memory, in KB, that SQL Server would like to have. If the
SQLServer:Memory Manager: Target Server Memory (KB) counters is larger than
the SQLServer:Memory Manager: Total Server Memory (KB), this means that SQL
Server would like to have more memory, but can't get it.

Generally, this indicates a memory bottleneck and you need to identify what
the cause is and resolve it. In most cases, the easiest way to resolve this
problem is to add more RAM to the server.

If both of the counters are the same, then SQL Server has as much memory as
it can use and there is no existing memory bottleneck.

Take a look at the following link for more infomation:

http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp

--
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administration
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."



> Hi everyone,
>
> I have one production server where users are complaining
> of slow downs. I am wondering if the problem could be
> caused by insuficient memory.SQL Server is configured to
> dinamicaly adjust the amount of memory used based upon
> demand. From task menager I can see that SQL Server always
> uses more then available physical memery ( RAM) and has to
> go to the pagefile. How can I minimize or eliminate
> pagefile IO ?
> I am also confused whit Curent disk Queue lenght counter
> always being in the range for all processors. If i have a
> problem with memory Curent Disk Queue lenght should be out
> of the range right ?
>
> Thanks very much,
> Mirna
>
 
 
Christo





PostPosted: Wed Nov 24 10:37:04 CST 2004 Top

SQL Server >> memory Set your max memory setting in Enterprise Manager to force SQL Server not to
exceed the thresshold of physical - x MB for the O/S and other apps. Thus
you will change the dynamic memory allocation to min/max or fixed size.

Regards



> Hi everyone,
>
> I have one production server where users are complaining
> of slow downs. I am wondering if the problem could be
> caused by insuficient memory.SQL Server is configured to
> dinamicaly adjust the amount of memory used based upon
> demand. From task menager I can see that SQL Server always
> uses more then available physical memery ( RAM) and has to
> go to the pagefile. How can I minimize or eliminate
> pagefile IO ?
> I am also confused whit Curent disk Queue lenght counter
> always being in the range for all processors. If i have a
> problem with memory Curent Disk Queue lenght should be out
> of the range right ?
>
> Thanks very much,
> Mirna
>
 
 
Mirna





PostPosted: Wed Nov 24 13:22:42 CST 2004 Top

SQL Server >> memory Thanks Sasan,

yes the target server memory counter is higher then total
server memory ( 20 %) Should I try setting the max server
memory to match the physical memory or the only other
solution is to add more RAM ?

Thanks again,
Mirna



>-----Original Message-----
>Hi Mirna,
>
>One way to find out if SQL Server is bottlenecked on RAM
is to check these
>two Performance Monitor counters: SQLServer:Memory
Manager: Total Server
>Memory (KB) and SQLServer:Memory Manager: Target Server
Memory (KB).
>
>The first counter, SQLServer:Memory Manager: Total Server
Memory (KB), tells
>you how much memory, in KB, that SQL Server is currently
using. The second
>counter, SQLServer:Memory Manager: Target Server Memory
(KB), tells you how
>much memory, in KB, that SQL Server would like to have.
If the
>SQLServer:Memory Manager: Target Server Memory (KB)
counters is larger than
>the SQLServer:Memory Manager: Total Server Memory (KB),
this means that SQL
>Server would like to have more memory, but can't get it.
>
>Generally, this indicates a memory bottleneck and you
need to identify what
>the cause is and resolve it. In most cases, the easiest
way to resolve this
>problem is to add more RAM to the server.
>
>If both of the counters are the same, then SQL Server has
as much memory as
>it can use and there is no existing memory bottleneck.
>
>Take a look at the following link for more infomation:
>
>http://www.sql-server-
performance.com/performance_monitor_counters_sql_server.asp
>
>--
>Sasan Saidi,
>MSc in CS, MCSE (NT4), IBM Certified MQ Administration
>Senior DBA
>Brascan Business Services
>"I saw it work in a cartoon once so I am pretty sure I
can do it."
>

>
>> Hi everyone,
>>
>> I have one production server where users are
complaining
>> of slow downs. I am wondering if the problem could be
>> caused by insuficient memory.SQL Server is configured
to
>> dinamicaly adjust the amount of memory used based upon
>> demand. From task menager I can see that SQL Server
always
>> uses more then available physical memery ( RAM) and has
to
>> go to the pagefile. How can I minimize or eliminate
>> pagefile IO ?
>> I am also confused whit Curent disk Queue lenght
counter
>> always being in the range for all processors. If i have
a
>> problem with memory Curent Disk Queue lenght should be
out
>> of the range right ?
>>
>> Thanks very much,
>> Mirna
>>
>.
>