MS SQL Server - SQL Server Performance

Author

SQL Server Performance

Brian C

2005-07-18, 1:23 pm

I am experiencing a performance problem on a Windows 2000 server with SQL
Server 2000. The problem is that the performance of applications
accessing SQL Server 2000 seems to degrade over time on this system.
Possibly this relates to the OS and the fact that we are only able to use 4
GB of RAM with Windows 2000 Server (not Advanced server).

I have set the dynamic memory allocation of SQL server to be limited to 1.5
GB since the server needs to share this memory with other applications. A
third party product needs over 1.5 GB of RAM to run. Within 1 day of
startup, the working page and virtual memory usage of sqlservr.exe is about
1.5 GB. The system runs with good performanace for about 3 weeks.
Previously, it had only run for about one week with good performance before
slowing down when the memory usage was set to 664 MB. This seemed to have a
positive effect.

We placed both the tempdb files (tempdb.mdf/templog.ldf) and the Windows
paging file on a Ram drive (from Superspeed software) which improves the
performance of these files by placing this files directly into RAM. These
frequently accessed files are running out of RAM, not the hard drive.

The application using SQL server experiences gradual slowdown after about 3
weeks of operation. I have taken metrics on the server and they are stable
after the first few days of operation. Typical metrics are ~2.3 Pages/sec
for paging, about 65% usages of a 600MB paging file (loaded as a RAM file),
and about 5-10% disk usage which is a stable value since the first day of
operation. If any process was experiencing performance degradation, I
would expect that this would be reflected in paging, but this is not showing
any increase.

The database size remains stable week to week as the data in the table is
deleted weekly. When performing in-house tests, SQL server increases to
using over 1.7 GB of virtual memory. Could the fact that I am forced to
limit the dynamic memory allocation of SQL server be causing my performance
degradation after 3 weeks. Is there a performance metric I can monitor to
prove this?

Thanks

frank chang

2005-07-18, 8:23 pm

Brian, Which windows 2000 service pack are you using? Are you using the /3GB
option? Thank you.

"Brian C" wrote:

> I am experiencing a performance problem on a Windows 2000 server with SQL
> Server 2000. The problem is that the performance of applications
> accessing SQL Server 2000 seems to degrade over time on this system.
> Possibly this relates to the OS and the fact that we are only able to use 4
> GB of RAM with Windows 2000 Server (not Advanced server).
>
> I have set the dynamic memory allocation of SQL server to be limited to 1.5
> GB since the server needs to share this memory with other applications. A
> third party product needs over 1.5 GB of RAM to run. Within 1 day of
> startup, the working page and virtual memory usage of sqlservr.exe is about
> 1.5 GB. The system runs with good performanace for about 3 weeks.
> Previously, it had only run for about one week with good performance before
> slowing down when the memory usage was set to 664 MB. This seemed to have a
> positive effect.
>
> We placed both the tempdb files (tempdb.mdf/templog.ldf) and the Windows
> paging file on a Ram drive (from Superspeed software) which improves the
> performance of these files by placing this files directly into RAM. These
> frequently accessed files are running out of RAM, not the hard drive.
>
> The application using SQL server experiences gradual slowdown after about 3
> weeks of operation. I have taken metrics on the server and they are stable
> after the first few days of operation. Typical metrics are ~2.3 Pages/sec
> for paging, about 65% usages of a 600MB paging file (loaded as a RAM file),
> and about 5-10% disk usage which is a stable value since the first day of
> operation. If any process was experiencing performance degradation, I
> would expect that this would be reflected in paging, but this is not showing
> any increase.
>
> The database size remains stable week to week as the data in the table is
> deleted weekly. When performing in-house tests, SQL server increases to
> using over 1.7 GB of virtual memory. Could the fact that I am forced to
> limit the dynamic memory allocation of SQL server be causing my performance
> degradation after 3 weeks. Is there a performance metric I can monitor to
> prove this?
>
> Thanks

Brian C

2005-07-18, 8:23 pm

Frank,

This is Windows 2000 Server standard edition, not Advanced Server. As
such, we are limited to the 4 GB of RAM and the /3GB is only useful to
recognize beyond the 4GB limit. In short, we aren't using /3GB. The
service pack is 4. These servers were set up (not by me) with 4 GB of RAM,
sent into the field, are now running for a customer and are experiencing a
delayed performance problem (after weeks of running). I would love to give
SQL Server more RAM but it needs to share the RAM with the other main
application on the server which uses the SQL server and which uses about 1.5
GB of RAM itself.

Brian

"frank chang" wrote:

> Brian, Which windows 2000 service pack are you using? Are you using the /3GB
> option? Thank you.
>

frank chang

2005-07-18, 8:23 pm

Brian, I understand your point about /3GB and Advanced Server. However, I have
seen Windows 2000 Server * standard edition * configured with /3GB and 4GB
of RAM. However, /3GB is not "only useful to recognize beyond the 4GB limit."
SQL Server can use every bit of memory given to it and people have used /3GB
with Windows 2000 Server in an attempt to improve SQL Server.

"Brian C" wrote:

> Frank,
>
> This is Windows 2000 Server standard edition, not Advanced Server. As
> such, we are limited to the 4 GB of RAM and the /3GB is only useful to
> recognize beyond the 4GB limit. In short, we aren't using /3GB. The
> service pack is 4. These servers were set up (not by me) with 4 GB of RAM,
> sent into the field, are now running for a customer and are experiencing a
> delayed performance problem (after weeks of running). I would love to give
> SQL Server more RAM but it needs to share the RAM with the other main
> application on the server which uses the SQL server and which uses about 1.5
> GB of RAM itself.
>
> Brian
>
> "frank chang" wrote:
>
>

frank chang

2005-07-19, 9:23 am

Brian, You are right. I apologize for the Windows 2000 Server standard
edition /3GB misunderstanding. It is my understanding that some people have
tried to use /3GB with Windows 2000 Server standard edition. However, in the
reference "Inside Windows 2000", it states that when you set /3GB switch on
Windows 2000 Server standard edition, the operating system will not utilize
the extra 1 GB of user address space. However, on Windows 2003 Server, the
operating system will utilize the extra 1 GB of user address space provided
by the /3GB switch.

With regard to the SQL Server issue, there are several possible
causes for this problem which other people have experienced.

1. Even though they have limited the amount of memory which SQL
Server can dynamically allocate, Sql Server is not relinquishing memory
properly after it is done using it. Please see,
http://www.cisco.com/en/US/products...080235f47.shtml

2.Quoting from "http://p2p.wrox.com/topic.asp?TOPIC_ID=147"
"In Overall memory management in SQL Server is complex. We suggest you
read the topic "Dynamically Managing Memory on Windows NT and Windows 2000"
in BOL"

"Generally and simply speaking, SQL Server will consume just about as much
memory as is physically available, stopping just short of having the system
start paging. This memory is allocated on demand. As other processes
require memory, it will begin to release memory back to the system as the
system needs it."

"The settings in EM only influence the size of the database buffer pool but
SQL Server needs memory for lots of other things, like the query execution
cache and buffering of the transaction log to name a couple. This usage
cannot be individually controlled, I believe."

Brian C

2005-07-19, 1:23 pm

The Cisco article desribed how you should dynamically set an upper limit for
SQL Server memory allocation. We have used the "Dynamically configure
SQL Server memory' option and set it to ~1.5 GB. In the Windows Task
Manager, sqlservr.exe reaches a maximum virtual memory size of ~1.5 GB after
about 1 day of running and does not change after that. This is not a
problem of runaway SQL Server memory allocation. It is an issue of degraded
performance of SQL server.

Thanks for your answer.

"frank chang" wrote:[color=darkred
]
>
> With regard to the SQL Server issue, there are several possible
> causes for this problem which other people have experienced.
>
> 1. Even though they have limited the amount of memory which SQL
> Server can dynamically allocate, Sql Server is not relinquishing memory
> properly after it is done using it. Please see,
> http://www.cisco.com/en/US/products...080235f47.shtml
>
>
> 2.Quoting from "http://p2p.wrox.com/topic.asp?TOPIC_ID=147"
> "In Overall memory management in SQL Server is complex. We suggest you
> read the topic "Dynamically Managing Memory on Windows NT and Windows 2000"
> in BOL"
>
> "Generally and simply speaking, SQL Server will consume just about as much
> memory as is physically available, stopping just short of having the system
> start paging. This memory is allocated on demand. As other processes
> require memory, it will begin to release memory back to the system as the
> system needs it."
>
> "The settings in EM only influence the size of the database buffer pool but
> SQL Server needs memory for lots of other things, like the query execution
> cache and buffering of the transaction log to name a couple. This usage
> cannot be individually controlled, I believe."
>
>
>
> .
>
> "Brian C" wrote:
>

frank chang

2005-07-21, 1:23 pm

Brian, I did some research on the issue you are facing. By "dynamically
setting an upper
limit for SQL Server memory allocation", you restrict SQL Server memory
usage.
As you indicated in your initial posts, the pages/sec drops to a 2.3
pages/sec.
On the other hand, by restricting the amount of memory SQL server uses, you
are
adversely impacting performance of the SQL Server in the long term. Someone
explained
this tradeoff by stating when you restrict the amount of memory SQL Server
2000 is using,
then SQL Server 2000 has to continually request and release memory. They
said( I can
give you the link if you wish), " the continuous reuesting for and release
of memory is
an activity that requires some resources, at the very least CPU time, that
could be
otherwise utilized."
In regard to your question about other metrics you could be
looking, perhaps
you could look at the performance counters relating to SQL Server CPU
activity.

"Brian C" wrote:
[color=darkred]
> The Cisco article desribed how you should dynamically set an upper limit for
> SQL Server memory allocation. We have used the "Dynamically configure
> SQL Server memory' option and set it to ~1.5 GB. In the Windows Task
> Manager, sqlservr.exe reaches a maximum virtual memory size of ~1.5 GB after
> about 1 day of running and does not change after that. This is not a
> problem of runaway SQL Server memory allocation. It is an issue of degraded
> performance of SQL server.
>
> Thanks for your answer.
>
> "frank chang" wrote:

Sponsored Links