Performance problem, lots of disk activity, running out of memory

[Mee Yamo]

Fellas!!

This is a very complicated one and it took me a few days to figure out
exactly what's going on, but here's the final story:

I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.

Once the SQLServer is started, it hits 300MB RAM (the minimum that was
set in the configuration of the server - remember, it is dynamically
aquired).

Then there is a .NET program that requests just about all the data the
SQL Server contains (apart from a single table that contains roughly
1.6 million rows and another table that contains about 10000 rows
which are all of type IMAGE).

Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.

Some performance checks showed me the SQLServer has a lot of disk
activity, it seems it is reading and writing pages of data from/to the
HD all the time (which causes the queries to be much much much
slower).

We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.

I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.

It does not seem to have anything to do with the .NET code.

Thank you very much,

M Yamo.

[Uri Dimant]

Mee
my 2 cents
1)
I you store image data , the actual data is not stored on the data
pages,instead it stores a 16 -byte pointer in the data row that indicates
where the actual data can be found.
2)

We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all. I think you have answered on your own question.
3)
DBCC PINTABLE is best used to keep small, frequently referenced tables in
memory. The pages for the small table are read into memory one time, then
all future references to their data do not require a disk read.

"Mee Yamo" <me*****@hotmai l.com> wrote in message
news:b5******** *************** ***@posting.goo gle.com... Fellas!!

This is a very complicated one and it took me a few days to figure out
exactly what's going on, but here's the final story:

I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.

Once the SQLServer is started, it hits 300MB RAM (the minimum that was
set in the configuration of the server - remember, it is dynamically
aquired).

Then there is a .NET program that requests just about all the data the
SQL Server contains (apart from a single table that contains roughly
1.6 million rows and another table that contains about 10000 rows
which are all of type IMAGE).

Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.

Some performance checks showed me the SQLServer has a lot of disk
activity, it seems it is reading and writing pages of data from/to the
HD all the time (which causes the queries to be much much much
slower).

We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.

I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.

It does not seem to have anything to do with the .NET code.

Thank you very much,

M Yamo.

[Erland Sommarskog]

Mee Yamo (me*****@hotmai l.com) writes:

I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.
...
Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.
...
I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.
I guess since you post, you have a problem with your application. However,
your posting gives little information of what that problem might be.

The default behaviour of SQL Server is go grab as much as memory as
possible. The more data, SQL Server have in cache, the less it has to
read from disks. If there are competing applications on the machine,
this can be a problem if SQL Server does not yield memory fast enough.
But you say that this is dedicated to SQL Server, so if SQL Server gets
some more memory that is no cause for alarm.

DBCC PINTABLE is something you have little reason to play with. If you have
data that you access rarely, but when you access it, you need it quick,
then PINTABLE may be an option. For instance, the Managing Director wants a
report the first day of each months, and he cannot wait those two minutes
it would take to get the data from disk. But using DBCC PINTABLE is likely
to mean that you sacrifice overall performance.
We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.

The amount of data is surely the clue here. Assuming that you really
have performance problems, they are only likely to show when you have
a full-size database.

Since I don't know what your real problems are, it is diffiult to
give some relevant advice, but a good starting point is to review
indexing, if you have not done this already.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mee Yamo]

Hi,

Thanks for trying to help.

Well the problem still goes on. The SQLServer process has hit its max
RAM allowance and started to slow down again (DISK ACTIVITY). I
restarted the SQLServer service and that solved it.

Why is it memory hogging? Every update seems to leave a residue on the
process's memory consumption.
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

Mee Yamo (me*****@hotmai l.com) writes:

I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.
...
Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.
...
I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.

I guess since you post, you have a problem with your application. However,
your posting gives little information of what that problem might be.

The default behaviour of SQL Server is go grab as much as memory as
possible. The more data, SQL Server have in cache, the less it has to
read from disks. If there are competing applications on the machine,
this can be a problem if SQL Server does not yield memory fast enough.
But you say that this is dedicated to SQL Server, so if SQL Server gets
some more memory that is no cause for alarm.

DBCC PINTABLE is something you have little reason to play with. If you have
data that you access rarely, but when you access it, you need it quick,
then PINTABLE may be an option. For instance, the Managing Director wants a
report the first day of each months, and he cannot wait those two minutes
it would take to get the data from disk. But using DBCC PINTABLE is likely
to mean that you sacrifice overall performance.

We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.

The amount of data is surely the clue here. Assuming that you really
have performance problems, they are only likely to show when you have
a full-size database.

Since I don't know what your real problems are, it is diffiult to
give some relevant advice, but a good starting point is to review
indexing, if you have not done this already.

[Erland Sommarskog]

Mee Yamo (me*****@hotmai l.com) writes:

Well the problem still goes on. The SQLServer process has hit its max
RAM allowance and started to slow down again (DISK ACTIVITY). I
restarted the SQLServer service and that solved it.

Why is it memory hogging? Every update seems to leave a residue on the
process's memory consumption.

As I tried to explain, this by design. You should not worry about it.

If you have real performance problem with long response times etc, the
people in these newsgroups can assist, but you need to provide more
information.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp