Target Server Memory is large!

[sqlturbo]

I run a Enterprise edition on a Windows 2k Advanced server with 768MB RAM. Lately, its been consuming all the memory even with no connections or queries running on it. The Total and Target Server memory object show a average value of more then 750 MB most of the time. And that is with no queries running on it. I have to stop the MSSQL Server service to release the memory and begin again, only to have the problem come back.

Is there a way I could find out why is it consuming so much memory? And what could I do to prevent it from becoming a 'Hulk'?

Thanks in advance for any ideas that you may have.
L'Tayn

 

[SQLBill]

What version SP do you have installed? It looks like you might have the SLAMMER worm.

Run

SELECT @@VERSION

the version should be 8.00.760

If it's not, search this forum and the Microsoft SQL Server: Programming forum (and FAQs) for more information on the SLAMMER worm. Also, check out

http://www.microsoft.com

and search for info on the worm.

-SQLBill

 

[fluteplr]

Are you saying it is consuming all memory whith no connections / querys curently running or when no connections / queries have ever been made?

By design SQL Server will use all available memory to cache data. This cache will continue to use that memory even when no query is currently running. It will release data only as needed.

If you want to restrict how much memory is being used go into enterprise manager and change the server setting.

Also 768mg seems like an small aount of memory for an enterprise install.

 

[sqlturbo]

Thanks for the replies guys.

The version on it is 8.00.194 with SP2. I'll run SP3 and see what happens.

On start up, it consumes about 23MB. That is with no queries/connections. The moment I run a simple select statement it usurps 750MB and does not let go either after the query has returened results or after the process has been killed. It stays at 750MB untill I turn off the service. On the memory size...I know its small but it does not have much work load on it. I use it for data transformation puposes with everthing on the same machine. We thought we would need it to support increased demand but we are still waiting for that to happen.

 

[SQLBill]

Even though you should still upgrade to SP 3, I think fluteplr has the right answer for your issue.

If it's the SLAMMER worm you will see lots of packets going out on the network and they will be from your SQL Server address. So, if you are also seeing a huge increase in bandwith usage then it's probably the SLAMMER. If not, then it's most likely just SQL Server doing its thing and reserving memory.

-SQLBill

 

[tlbroadbent]

As fluteplr mentioned, SQL Server uses as much memory as possible for data cache. If it didn't do that performance would be very poor. Why are you worried about the amount of memory SQL Server uses? Is the server running other processes or services? That is generally poor practice. SQL Server should be on a dedicated machine. If it is, then the amount of memory used is not an issue. I would only be concerned if SQL Server isn't able to allocate the memory needed.

I've found that SQL BOL are helpful in understanding how SQL Server uses memory. I recommend reading about memory usage in the documentation. You'll find that you can set an upper limit to the amount of memory used by SQL Server. However, performance will probably take a hit if you do.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[sqlturbo]

The problem lay in one of the tables...it had no indexes defined to suit the query. I created a index and now SQL Server uses half the memory it was using at the time.

I do run one VB application on the same machine but that uses just 25MB. SQL Server hogged 750MB and it wanted more which resulted in heavy paging. And that slowed the querying process as well. In fact, it would take more than a few seconds to respond to a click button operation. And that was what was bugging me. After I created the index, it consumes only 350MB.

I did however install SP3. Thanks for the suggestion and all the inputs.

I more thing...its a very reassuring to have access to the wealth of information guys like SQLBill, fluteplr, Tlbroadbent have when one needs it. Thanks for all the help.