July 13, 2015 at 12:46 am
I have an issue with a server memory. SQL server is allocated 32 Gb out of 40 GB memory and sql server is using all the memory which is allocated to it.
The memory usage is gradually increasing to the max value that is allocated to SQL Server.
I have tried all ways but still no luck:
1.Killed all the open transactions.
2.Cleared the system cache.
3.Cleared session cache
4.Cleared procedure cache.
5.Checked for blocked transactions and killed them.
6.Ran DBCC opentran and found no open transactions.
July 13, 2015 at 1:25 am
giri10488 (7/13/2015)
I have an issue with a server memory. SQL server is allocated 32 Gb out of 40 GB memory and sql server is using all the memory which is allocated to it.The memory usage is gradually increasing to the max value that is allocated to SQL Server.
I have tried all ways but still no luck:
1.Killed all the open transactions.
2.Cleared the system cache.
3.Cleared session cache
4.Cleared procedure cache.
5.Checked for blocked transactions and killed them.
6.Ran DBCC opentran and found no open transactions.
This is the expected behaviour.
😎
If the OS or other processes are experiencing memory starvation, lower the max mem setting of the SQL Server.
July 13, 2015 at 2:32 am
giri10488 (7/13/2015)
I have an issue with a server memory. SQL server is allocated 32 Gb out of 40 GB memory and sql server is using all the memory which is allocated to it.The memory usage is gradually increasing to the max value that is allocated to SQL Server.
Normal, expected, documented behaviour. SQL will use all the memory you allow it to use. You've said it can use 32 GB, so it will use 32 GB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2015 at 2:34 am
can sql server free some memory to os & when,how?
July 13, 2015 at 2:37 am
giri10488 (7/13/2015)
can sql server free some memory to os & when,how?
Too increase the OS memory, change the max memory settings.
😎
July 13, 2015 at 2:41 am
giri10488 (7/13/2015)
can sql server free some memory to os & when,how?
Only when the OS is under memory pressure or someone reduces the max server memory setting.
To be clear, SQL using lots of memory is NOT a problem. It's by design, it's intentional, it's to ensure optimal performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2015 at 3:07 am
http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/
More RAM is better this way SQL doesnt need to write to disk. MS has been spending a lot of time trying to put as much data in RAM in recent releases e.g in memory OLTP ,get used to provisioning and working with RAM this way.
July 13, 2015 at 3:18 am
THNKS TO ALL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply