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,
MirnaHi 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."
"Mirna" wrote:
> 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
>|||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
"Mirna" wrote:
> 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
>|||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."
>"Mirna" wrote:
>> 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
>.
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Wednesday, March 28, 2012
memory
I have production SQL server with 2GB, Memory is configured Dynamically with
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
ThanksHello,
Take a look into the memory performance counters and see the memory usage
and buffer cache hit ratio during peak usage. If every thing is normal then
you can live with what you have. Incase if you have memory contension then
probably you can go for 4 GB plus.
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:%23ExlC0WcHHA.3648@.TK2MSFTNGP02.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to
>add more memory . would 500MB for the OS is planty?
> Thanks
>
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
ThanksHello,
Take a look into the memory performance counters and see the memory usage
and buffer cache hit ratio during peak usage. If every thing is normal then
you can live with what you have. Incase if you have memory contension then
probably you can go for 4 GB plus.
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:%23ExlC0WcHHA.3648@.TK2MSFTNGP02.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to
>add more memory . would 500MB for the OS is planty?
> Thanks
>
Memory
I have production SQL server with 2GB, Memory is configured Dynamically with
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
ThanksThis is clearly one of those "it depends answers". I would leave at least
500 for OS and all other ancillary processes (including SQL stuff not
directly related to buffer cache). But you can test with different max
values and see what performs best for you. Check the Pages/sec and page
fault delta performance stuff. If you are seeing consistently high values
there then sql max mem is too high.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:OjoHzYXcHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with
> Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
> more memory . would 500MB for the OS is planty?
> Thanks
>
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
ThanksThis is clearly one of those "it depends answers". I would leave at least
500 for OS and all other ancillary processes (including SQL stuff not
directly related to buffer cache). But you can test with different max
values and see what performs best for you. Check the Pages/sec and page
fault delta performance stuff. If you are seeing consistently high values
there then sql max mem is too high.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:OjoHzYXcHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with
> Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
> more memory . would 500MB for the OS is planty?
> Thanks
>
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,
MirnaHi 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:
[url]http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp[
/url]
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."
"Mirna" wrote:
> 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
>|||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
"Mirna" wrote:
> 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
>|||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."
>"Mirna" wrote:
>
complaining[vbcol=seagreen]
to[vbcol=seagreen]
always[vbcol=seagreen]
to[vbcol=seagreen]
counter[vbcol=seagreen]
a[vbcol=seagreen]
out[vbcol=seagreen]
>.
>
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,
MirnaHi 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:
[url]http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp[
/url]
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."
"Mirna" wrote:
> 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
>|||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
"Mirna" wrote:
> 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
>|||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."
>"Mirna" wrote:
>
complaining[vbcol=seagreen]
to[vbcol=seagreen]
always[vbcol=seagreen]
to[vbcol=seagreen]
counter[vbcol=seagreen]
a[vbcol=seagreen]
out[vbcol=seagreen]
>.
>
Memory
I have production SQL server with 2GB, Memory is configured Dynamically with
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
ThanksThis is clearly one of those "it depends answers". I would leave at least
500 for OS and all other ancillary processes (including SQL stuff not
directly related to buffer cache). But you can test with different max
values and see what performs best for you. Check the Pages/sec and page
fault delta performance stuff. If you are seeing consistently high values
there then sql max mem is too high.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:OjoHzYXcHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with
> Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
> more memory . would 500MB for the OS is planty?
> Thanks
>
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
ThanksThis is clearly one of those "it depends answers". I would leave at least
500 for OS and all other ancillary processes (including SQL stuff not
directly related to buffer cache). But you can test with different max
values and see what performs best for you. Check the Pages/sec and page
fault delta performance stuff. If you are seeing consistently high values
there then sql max mem is too high.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:OjoHzYXcHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with
> Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
> more memory . would 500MB for the OS is planty?
> Thanks
>
Memory
I have production SQL server with 2GB, Memory is configured Dynamically with
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
Thanks
This is clearly one of those "it depends answers". I would leave at least
500 for OS and all other ancillary processes (including SQL stuff not
directly related to buffer cache). But you can test with different max
values and see what performs best for you. Check the Pages/sec and page
fault delta performance stuff. If you are seeing consistently high values
there then sql max mem is too high.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:OjoHzYXcHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with
> Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
> more memory . would 500MB for the OS is planty?
> Thanks
>
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
Thanks
This is clearly one of those "it depends answers". I would leave at least
500 for OS and all other ancillary processes (including SQL stuff not
directly related to buffer cache). But you can test with different max
values and see what performs best for you. Check the Pages/sec and page
fault delta performance stuff. If you are seeing consistently high values
there then sql max mem is too high.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:OjoHzYXcHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with
> Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
> more memory . would 500MB for the OS is planty?
> Thanks
>
memory
I have production SQL server with 2GB, Memory is configured Dynamically with
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
Thanks
Hello,
Take a look into the memory performance counters and see the memory usage
and buffer cache hit ratio during peak usage. If every thing is normal then
you can live with what you have. Incase if you have memory contension then
probably you can go for 4 GB plus.
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:%23ExlC0WcHHA.3648@.TK2MSFTNGP02.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to
>add more memory . would 500MB for the OS is planty?
> Thanks
>
sql
Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to add
more memory . would 500MB for the OS is planty?
Thanks
Hello,
Take a look into the memory performance counters and see the memory usage
and buffer cache hit ratio during peak usage. If every thing is normal then
you can live with what you have. Incase if you have memory contension then
probably you can go for 4 GB plus.
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:%23ExlC0WcHHA.3648@.TK2MSFTNGP02.phx.gbl...
>I have production SQL server with 2GB, Memory is configured Dynamically
>with Maximum of 1537MB, can I increase this to maybe 1800MB or do I need to
>add more memory . would 500MB for the OS is planty?
> Thanks
>
sql
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
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.co...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."
"Mirna" wrote:
> 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
>
|||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
"Mirna" wrote:
> 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
>
|||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."[vbcol=seagreen]
>"Mirna" wrote:
complaining[vbcol=seagreen]
to[vbcol=seagreen]
always[vbcol=seagreen]
to[vbcol=seagreen]
counter[vbcol=seagreen]
a[vbcol=seagreen]
out
>.
>
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
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.co...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."
"Mirna" wrote:
> 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
>
|||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
"Mirna" wrote:
> 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
>
|||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."[vbcol=seagreen]
>"Mirna" wrote:
complaining[vbcol=seagreen]
to[vbcol=seagreen]
always[vbcol=seagreen]
to[vbcol=seagreen]
counter[vbcol=seagreen]
a[vbcol=seagreen]
out
>.
>
Wednesday, March 21, 2012
Measuring daily Inserts/updates on a production databse
I want to measure updates/Insertion rate of my databse in order to measure that how percent the databses is booked for insert and update.
can some one can suggest me the mechanism or other resource for doing this work...
thanxs in advance
It is not very clear what kind of data you are hoping to capture.
Perhaps looking at some of the Performance Monitor counters could help.
Perhaps some of the dynamic system views, such as: sys.dm_db_file_space_usage
|||I want to measure/calculate that how many number of Inserts and Updates operated on my database let say AAA|||probably you want to find out the number of transaction per database. Read : http://msdn2.microsoft.com/en-us/library/ms189883.aspx.
Madhu
|||In SQL 2005 you can also look at sys.dm_db_index_operational_stats.sql
Subscribe to:
Posts (Atom)