Memory Usage for SQL Server 7.0 high
-
Dear all,
in my office, I Have found problems,
which is our SQL Server, since last 2 weeks
the memory Usage was very high, althought the users already logout.
Now, every morning day I must Restart Service from SQL Server, and then memory usage "released".
Last week I tried to reinstall SQL Server 7 with Service Pack 4,
but I found the same problem.
FYI, Memory for HP Server ML 530 (Dobule Processor) - 2 GB.
Please, I need your help, where's the problems,
because there's no error message in the Event Viewer.
Thank's for all your support & help.
Regard,
Beni Santoso
-
What size databases do you have on the server?
Do you have SQL Server set to dynamically manage it's memory?
What else is running on the server that needs the memory?
--------------------
Colt 45 - the original point and click interface
-
Have you checked Task Manager on the server to see what process is using all the memory? If SQL Server is the culprit, you can check what's going on using sp_who2 from Query Analyzer. Or you can set up a profiler trace on the server to see what is gobbling up all the memory.
Did this just suddenly start or is it a new installation? What about anti-virus software?
My hovercraft is full of eels.
-
Check that. I just re-read the post and realized you're using 7.0 so try sp_who instead. I don't believe sp_who2 was available until SQL 2000 came out. Best of luck.
My hovercraft is full of eels.
-
Thank's for reply,
fyi, there's no one connect in the morning until 08:30 a.m
So when I Check for SQL Profiler, the database was close, except for Master & tempDB.
Yes that was true, SQLServer take the biggest memeory on Task Manager.
The Server is dedicated for SQL Server & Veritas Backup Exec Ver 9.0 (used SQL MSDE).
FYI, this suddenly happend on the server, usually normal.
The total of Some data base now approx. 10 GB
Thanks you for your help.
-
Well you do know that SQL Server does a lot of data caching
10GB into 2Gb will probably equate to SQL Server wanting most of the memory for its data cache.
If you grab hold of Spotlight on SQL Server from http://www.quest.com/spotlight_on_sql_server/index.asp you can actually drill into SQL Server's memory usage and see what the breakdown is.
--------------------
Colt 45 - the original point and click interface -
Thank's for your reply,
FYI, in the morning before 08:30 a.m
there're no user login / access the SQL Server.
But the memory usage still high, because not released.
When I tried from SQL Profiler, the Database was already closed, and there was no user.
Could you help me, where's the real problem,
that effect memory usage stll high, and cannot released automatically, everytime user logout / disconnect from the SQL Server.
Thank's
-
From your response I gather your not familiar with how SQL Server utilises memory. What you have described doesn't sound like a problem to me. It sounds like normal operation. The server will try and keep as much data in memory to minimise physical disk I/O so users get the best performance.
You have 10GB of data and 2GB of memory. When a user queries the database, SQL Server will read the data the user requires, plus additional data that might be required for future requests, into memory. It then holds this data in memory in case the same user, or other users, need the data again. The operation of reading data into memory is repeated for every request for new data. If new data is requested and there is no memory available, SQL Server will flush some of the data that hasn't been used recently back out to the physical disk before reading in the new data.
Users logging out has no affect on whether the data will be flushed out to disk or not. SQL Server will still hold the data in cache until the memory is required for something else.
If you've got SQL Server setup to dynamically manage its memory, the Server should also release un-needed memory back to the operating system when its requested.
You can read a bit more about how it operates by looking at the "Memory Architecture" and "I/O Architecture" topics in Books Online. Both of these topics should clear up any questions you have and probably correct any mistakes in my explanation above
--------------------
Colt 45 - the original point and click interface -
Thank's for your explaination,
I used dynamic memory.
FYI, This server was running well from 1 year ago, the data grow from 8 GB to 10 GB now in several database (the biggest database is 2 GB).
Before, the operation is running well.
But suddenly about 2 weeks ago, the memory canot released.
Last week I tried to reinstall SQL Server with SP4,
but the problem still occurs.
I'm wait your suggestion.
Thank's.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply