Thread: Memory bug
We are currently experiencing a problem with memory usage on our application server. There is an application on our server that uses SQL Server to keep track of data that is sent from our mainframe computer here. There are two databases that are kept PPMDB and HISTDB. Both have the same structure, but different time periods of updating. PPMDB is updated every 2 minutes, and HISTDB is every 4 hours. The application that runs on the server connects to the databases through the ODBC driver. Once that is done, it starts the SQL engines, which take up a good amount of memory, 8 MB. The starting point of memory ranges between 75 MB and 85 MB. The system is equipped with 320 MB of memory. What happens is that once the first update occurs SQL Server uses up a large amount of memory, as said before, but it doesn't relinquish the memory that it used to make the updates. And with each update, SQL Server uses up a small amount of memory, but our main concern is that somewhere down the road it will use up all of the memory on the serve. The current version of SQL Server that we currently have is 7.0, with Service Pack 1.0. Is this something that is supposed to happen, or is this a major problem? I would be most thankful for any information anybody can supply.
The SQL Server will cache data and query plans and maintain them in cache for performance reasons. There are memory management processes in SQL Server to decide when to clear items from cache based on aging and on other memory requirements on the system. Unless you're processes are doing operations like loading an entire large table in cache (e.g., select * from somebigtable), I wouldn't expect it to dominate the server's memory. There is a configuration option to set the maximum amount of system memory that SQL Server will use, but you shouldn't have to set it unless the SQL Server is regularly monopolizing memory resources at the expense of other applications or the OS. -- Bob Pfeiff MCDBA, MCSD, MCSE, MCT, SQL Server MVP Practice Leader, Data Management CIBER, Washington, DC
The SQL Server will cache data and query plans and maintain them in cache for performance reasons. There are memory management processes in SQL Server to decide when to clear items from cache based on aging and on other memory requirements on the system. Unless you're processes are doing operations like loading an entire large table in cache (e.g., select * from somebigtable), I wouldn't expect it to dominate the server's memory. There is a configuration option to set the maximum amount of system memory that SQL Server will use, but you shouldn't have to set it unless the SQL Server is regularly monopolizing memory resources at the expense of other applications or the OS. -- Bob Pfeiff MCDBA, MCSD, MCSE, MCT, SQL Server MVP Practice Leader, Data Management CIBER, Washington, DC
Bob, We have a SQL 7.0 database that frequently runs out of memory and crash's, the system has a GIG of RAM and is set up with the default memory settings. I'm seeing an increase in the amount of memory used by the process called SQLSERVR.EXE and to date I haven't seen it release any, just playing around with the Enterprise Manager increases the amount of memory in use by the process and in the past hour it hasn't dropped. We are adding a lot of data to the system currently through a front end app that uses named pipes, have you run into this problem at all? Bob Pfeiff wrote: > The SQL Server will cache data and query plans and maintain them in cache > for performance reasons. There are memory management processes in SQL > Server to decide when to clear items from cache based on aging and on other > memory requirements on the system. Unless you're processes are doing > operations like loading an entire large table in cache (e.g., select * from > somebigtable), I wouldn't expect it to dominate the server's memory. > > There is a configuration option to set the maximum amount of system memory > that SQL Server will use, but you shouldn't have to set it unless the SQL > Server is regularly monopolizing memory resources at the expense of other > applications or the OS. > -- > Bob Pfeiff > MCDBA, MCSD, MCSE, MCT, SQL Server MVP > Practice Leader, Data Management > CIBER, Washington, DC
Can't say I've seen a problem like this and I've looked at a variety of systems including a heavily used Peoplesoft system and a data warehouse that we did daily loads on. A GB of RAM is a lot to use up, how much data is coming in via the app that's loading data?
We had the same problem (2GB Memory). We had called Microsoft TS and were told the exact same thing Bob Pfeiff said in the previous post. Go to the properties for the SQL server and set SQL Server to use a maximum ammount of RAM (how much depends on what else you have the NT server doing).