473,394 Members | 1,752 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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

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.
Jul 20 '05 #1
4 7196
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*****@hotmail.com> wrote in message
news:b5**************************@posting.google.c om... 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.

Jul 20 '05 #2
Mee Yamo (me*****@hotmail.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
Jul 20 '05 #3
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*****@hotmail.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.

Jul 20 '05 #4
Mee Yamo (me*****@hotmail.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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Jos? Cardoso | last post by:
Hi, I have developed a java application that interacts with a database. Now i've the folowing problem: - I have a list retrieved from the database(about 200 records); - for each, i do a...
3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
11
by: rishi_israni | last post by:
Hi, i am having a strange problem running memory intensive queries on SQL server. I am doing an update on a table with 9 million records from another table with 50 records. the query i am...
0
by: pshroads | last post by:
I am trying to restore a 200 GB database on to a newly formatted SAN volume. The restore has been running for hours but there doesn't seem to be a lot of disk activity on the disks I'm restoring to...
3
by: Mario.Reif | last post by:
We have developed an application which was running under DB2 v7.2.5 quite well for some years. Four weeks ago we installed DB2 v8.1.5 Express Fixpak 5 on a new Server (hardware is nearly the same...
0
by: Bob | last post by:
I'd like to be able to make a little app that monitors disk activity just like task manager, listing all active processes with their current disk read and write values. It's not obvious to me how...
4
by: max | last post by:
Hi all, I want to write a program in C/C++ which monitor any hard disk activity by a particular program which have assigned for WINXP. For example, the program are going to run like this.....
6
by: Bobby | last post by:
Hi, I'm not 100% sure that this is an Access problem. A friend of mine has an Access 2003 database. Occasionally he brings a copy to me so that I can import some of his data into my Access 2003...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.