2000 server always uses max memory
-
After a few moments my SQL 2
server uses way t much memory. It uses the MAX available.Can't figure out why.
How do I trouble sh
t? -
DBMS servers love memory. SQL Server (and most others) will take all they can get as soon as they can use it. Unless you specifically set the max memory, SQL Server will use as much as it can find. It's not a bug, it's the way the beast works.
And then again, I might be wrong ...
David Webb -
Is he wrong?
-
no he's right, it will grab as much as your version of SQL Server will allow. See 'Maximum Capacity Specification' in sql server BOL.
-
No way. Then why is there performance tuning
-
Performance tuning for a SQL DBMS involves a lot of other activities and analysis. In my experience, most performance problems revolve around the SQL being used and the way the query optimizer chooses to execute it. Memory is typically only an issue in two cases: there isn't enough of it to support the troughput requirements, or the server is not the only thing running on the box and the applications are thrashing each other over resources.
What problems are you experiencing? The folks here can probably give you some good advice if we know what the problem is.
And then again, I might be wrong ...
David Webb -
OK thanks.
My issue is a small web app that only two or three of us use.
After a while it just hangs. A restart of the SQL Server fixes it.
Temorarily.
I've written a lot of web apps, and this one is a little different in that it does make 10 or 15 SQL requests in a row at the beginning of each user's session.
I can't nail it down to any one of these requests.
Also we have about a dozen read-only databases (not for use in the web app) that have humdreds of millions of rows. They just sit and spin. No one ever looks at them.
4 processors - 2 gig of memory.
Used to work fine until a few weeks ago.
-
Are the web server and the SQL Server on the same box? Can you run profiler while this is going on and trace the sql being sent to the server? When the app hangs, can other people still connect to the SQL Server? How many connections is the SQL Server set to accept? Is the web app well-behaved as far as closing connections gracefully?
Of course there's always the question "What changed just before this stopped working?" New code on the SQL Server or web app? Service packs or patches applied?
Web stuff isn't my specialty, so if anyone out there has other ideas, please chime in.
And then again, I might be wrong ...
David Webb -
Are the web server and the SQL Server on the same box?
YEs!
Can you run profiler while this is going on and trace the sql being sent to the server?
Profiler? Profiler?
When the app hangs, can other people still connect to the SQL Server?
I dunno
How many connections is the SQL Server set to accept?
I dunno
Is the web app well-behaved as far as closing connections gracefully?
YEs
Of course there's always the question "What changed just before this stopped working?"
I was hoping you could help me with that.
New code on the SQL Server or web app?
APP
Service packs or patches applied?
Yes sp4- last night - boy did that screw things up!
Web stuff isn't my specialty, so if anyone out there has other ideas, please chime in.
-
Hmmm, best practice is to keep DBMS and web server on different boxes. It's possible that you're having memory contention problems and that cycling the SQL Server frees things up, not because the SQL Server was hung up, but because that would free up resources for the web server.
You might look up sp_configure on books online and take a look at the 'max server memory' option. If the SQL Server is underused at present, it might not hurt to limit the memory it will grab, and it might provide more stability.
SQL Profiler is a tool that can be used to monitor traffic and events that happen on the server. It's configurable to display a lot of stuff, so you need to check the books online to learn all the options.
And then again, I might be wrong ...
David Webb -
First off, add more RAM 2 Gb is a max value to grab for the OS, SQL Server and IIS. I'd make it 4 Gb minimun - configure SQL for 2 GB (the max for standard edition) leaving the OS and IIS 2 Gb between them. If possible bump up the RAM to 6 Gb giving everyone thier respective 2 Gb max memory allocation. Second, get IIS off of the database server if possible. SQL Server 101 states - a SQL Server is a dedicated machine, dedicated to SQL Server only.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thanks a lot. I'm sure you are right
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply