SQL Server Performance Forum – Threads Archive

Memory estimation

Hey there! Can anyone explain in an easy way how memory is used in the sql server? I’m currently arguing with a programmer that means that the available physical memory in the computer just has to be as big as the database. I assume that this can’t be correct. My application is a not very well optimized (yet) three tier app with lots of stored procedures. With approx 5-10 users I mean that server side cursors takes memory, temporary sorting areas for queries etc takes memory, cache, logs etc. Can anyone tell me the truth <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Your programmer doesn’t know what he’s talking about. The memory is used for several purposes in SQL Server. Procedures plans are stored in the procedure cache which uses memory. Logical reads and writes are done in the memory area which uses memory. Everything in SQL Server uses memory and is more efficient with a larger amount of memory. The ideal situation is to have SQL Server on a dedicated box with as much memory as possible, memory being directly related to performance. Let us know if you need more details. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

True. SQL server take all memory he can. So more memory, more performance.
BTW: Ask programmer if you have 10 databases 30Gbytes each, you need 30 server[:I]?
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Alot of Memory is always good. Here are some Hardware tips on what type of memory characteristics to look for. EDO RDRAM SDRAM
Error Correcting
Fault Tolerance
Access Speeds
Densities

When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB. By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 – 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;319942] to determin proper configuration. Also refer to Books Online; topics: "Effects of min and max server memory"; "Memory Architecture"; "Memory Architecture"; "Server Memory Options"; "SQL Server Memory Pool". Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>