SQL Server Memory usage

Question

SQL Server Memory usage

ShekharNaidu

SSCrazy

Points: 2092
More actions
September 29, 2006 at 1:37 am

#62434

one of our SQL server using almost 2GB of memory and it seems not releasing the memory unused..
how can I identify the problem why the sql server using that much memory...
Thank You
Thanks ,
Shekhar
October 2, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This is not a problem. SQL Server is designed to use up all of the memory available to it. It will not release the memory that it has claimed by design. If you truely need SQL Server to consume less memory, change the MAX Server Memory setting. Keep in mind that performance will decrease with less memory. Is there a reason why you want SQL Server to not use the 2GB?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 2

Thank you for your reply..

actually my intention was not to make SQL Server use less than 2GB.. but the problem is that memory usage is getting increased all the way... no way its releasing any sort of memory..

ex:I observed SQL Server is consuming memory when I run queries or some other database related programs... but Even when I stop the program also I didnot see that SQL Server memory usage is getting reduced...????

Can you explain me this??????

Thanks ,

Shekhar

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 3

When SQL Server performs some operation, it loads as many related pages as it requires into the memory. For example, suppose you are running a stored proceudre which forces full scan of a table, then all the data pages (and index pages) related to the referred table will be loaded into the memory. Also, the execution plan of the stored proc will also be loaded in the memory. All the sorting, comaring, temporary storage locations will be created in teh memory only. When the stored procedure is completed (and your operation is finished), the already loaded data pages remain there in the meory until they are replaced by the paes belonging to some other database object being used by some other process. However, if all the connections ceased, or SQL Server engine is restarted, the memory is cleared. Also, there are certain DBCC commands that can clear buffer cache. Read books online for details on these commands.

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

John Rowan SSC Guru Points: 56440 More actions · Answer 4

In addition to Pankaj's response, the more memory you have on a server, the more space SQL Server can use for the different memory pools (cache). The reason SQL Server does this is for speed. It is much faster to retrieve data from the data cache than from disk.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 5

Thank you for your explanations..

Is there any tool to find out how memory is allocated for different caches...for example how much memory allocated for buffer cache...

Thank You

Thanks ,

Shekhar

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 6

Thank you for your explanations..

Is there any tool to find out how memory is allocated for different caches...for example how much memory allocated for buffer cache...

Thank You

Thanks ,

Shekhar

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 7

The data is available though windows performance counters or master.dbo.sysperfinfo