question

ksr avatar image
ksr asked

Why Sql server consuming more memory?

Hi Friends Good Morning, This is subba reddy , i am working Sql server data base developer in small organization.in production the sql server consuming more memory why? Configuration: Sql server -2012. Server Ram- 7 Gb Current data bse size(.Mdf)= 20 gb Consuming memory: With out front-application:intialy 3 gb graduallyincress to 4 gb With application running with along 30 users: 6.5 gb nearly. if there is no any sql are not in wait status. but why its consuming more memory>
memory-utilization
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
what is the MIN and MAX memory setting of the SQL Server? You can check this by executing the below command: EXEC sp_Configure look for the below two option: min memory per query (KB) min server memory (MB) In case they are not visible then execute the sp_configure command once again with below parameters: EXEC sp_configure 'show advanced options',1 GO RECONFIGURE
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLGoooRooo avatar image
SQLGoooRooo answered
I will expand on vishalhsingh answer. It is important to set the MAX RAM to LESS than total server RAM. If you do not set the Max memory BELOW the Server's max RAM then SQL Server will consume as much RAM as it can and leave almost nothing for the Operating System. So in your case with 7GB of RAM I suggest 5120 as the MAX. This leaves approximately 2GB for OS functionality. This assumes you have only one instance installed.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
This is what SQL Server does. It gradually increases the memory it is using with its data cache, to improve response times to the users. If you wish to limit the amount of memory that SQL Server can allocate, the other answers here have posted some suggestions. See the SQLSkills team's recommendation at https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Also we should explain why it's faster. It is highly preferable for all the most frequently used data in your database to be stored in memory for I/O reasons. The DB Engine slows down noticably and significantly every time it has to work on a spinning drive. Those disks have moving parts versus the quicker read and writes of solid state and the lag becomes noticeable the more transactions you conduct. Corollary: different types of RAID sets on the physical disks actually can increase or decrease performance due to striping over many spindles.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.