January 24, 2017 at 2:50 am
Hi Experts,
In my Production environment, we have 64 GB of RAM, in that SQL server itself using around 99% of memory.Previously it was 32 GB now we increased it to 64 GB. Again the memory pressure continues.What is the main reason for the memory pressure.How can we control it???
The memory pressure is always high.
Details:
Version: MS 2008 R2
RAM: 64 GB
No of processors: 8
Thanks in advance
Narine M
January 24, 2017 at 2:53 am
What have you set your Max Server Memory to? SQL Server will always grab all of the memory it can.
January 24, 2017 at 2:56 am
NorthernSoul - Tuesday, January 24, 2017 2:53 AMWhat have you set your Max Server Memory to? SQL Server will always grab all of the memory it can.
No we didn't set any max server memory.
January 24, 2017 at 2:59 am
What I'm curious about is how you know whatever problems you're having are caused by memory?
John
January 24, 2017 at 3:02 am
In Management Studio right click on your instance and then go into the memory settings. I'd set your Max Server Memory to be around 48gb initially but keep an eye on it to see if you get any memory alerts regarding SQL Server. This will leave memory available for the OS to use.
January 24, 2017 at 3:03 am
The following guide for Max memory is as good as any, and better than most.https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/
however, when you talk about "memory pressure" because SQL Server is using most or nearly all available RAM, this is wat SQL Server is actually designed to do. Frequently or recently accessed data pages will be stored in memory (buffer cache) to allow faster retrieval, and thus better response times than pulling them from disc. SQL Server also stores a range of other stuff in memory pools - such as query plans - to improve performance.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 24, 2017 at 3:03 am
John Mitchell-245523 - Tuesday, January 24, 2017 2:59 AMDefinitely check your max server memory, yes. If you're not running any other applications or SQL Server components on the server, leave about 8GB for the operating system.What I'm curious about is how you know whatever problems you're having are caused by memory?
John
Always the memory utilization is high..Sometimes the server is slow and receiving the mails from client..Whether it is due to memory pressure..
January 24, 2017 at 3:07 am
NorthernSoul - Tuesday, January 24, 2017 3:02 AMIn Management Studio right click on your instance and then go into the memory settings. I'd set your Max Server Memory to be around 48gb initially but keep an eye on it to see if you get any memory alerts regarding SQL Server. This will leave memory available for the OS to use.
Why the SQL server is utilising the whole memory, only 400 MB is free...What will be reason...By setting the max server memory whether it leads to any performance issues..
January 24, 2017 at 3:10 am
Narine M - Tuesday, January 24, 2017 3:03 AMJohn Mitchell-245523 - Tuesday, January 24, 2017 2:59 AMDefinitely check your max server memory, yes. If you're not running any other applications or SQL Server components on the server, leave about 8GB for the operating system.What I'm curious about is how you know whatever problems you're having are caused by memory?
John
Always the memory utilization is high..Sometimes the server is slow and receiving the mails from client..Whether it is due to memory pressure..
As Andrew explained, high memory utilisation is expected behaviour. SQL Server will grab as much memory as it can and only return it if the operating system asks for it. There could be any number of reasons that the server is slow - you need to do a bit more investigation. As I and others have said, start by setting max server memory - that certainly won't do any harm.
John
January 24, 2017 at 3:10 am
Narine M - Tuesday, January 24, 2017 3:07 AMNorthernSoul - Tuesday, January 24, 2017 3:02 AMIn Management Studio right click on your instance and then go into the memory settings. I'd set your Max Server Memory to be around 48gb initially but keep an eye on it to see if you get any memory alerts regarding SQL Server. This will leave memory available for the OS to use.Why the SQL server is utilising the whole memory, only 400 MB is free...What will be reason...By setting the max server memory whether it leads to any performance issues..
Andrew has explained it. SQL Server will grab all the memory it can, it's designed that way. It best practice to set the max server memory on all of your instances both production and development.
January 24, 2017 at 3:16 am
John Mitchell-245523 - Tuesday, January 24, 2017 3:10 AMNarine M - Tuesday, January 24, 2017 3:03 AMJohn Mitchell-245523 - Tuesday, January 24, 2017 2:59 AMDefinitely check your max server memory, yes. If you're not running any other applications or SQL Server components on the server, leave about 8GB for the operating system.What I'm curious about is how you know whatever problems you're having are caused by memory?
John
Always the memory utilization is high..Sometimes the server is slow and receiving the mails from client..Whether it is due to memory pressure..
As Andrew explained, high memory utilisation is expected behaviour. SQL Server will grab as much memory as it can and only return it if the operating system asks for it. There could be any number of reasons that the server is slow - you need to do a bit more investigation. As I and others have said, start by setting max server memory - that certainly won't do any harm.
John
Previously there is a CXpacket waits, so we changed the max DOP. Now the waits has been reduced. Now the queries are running fine.But the client is experiencing slowness for calls against databases.??? What will be the reason
January 24, 2017 at 3:22 am
Narine M - Tuesday, January 24, 2017 3:16 AMJohn Mitchell-245523 - Tuesday, January 24, 2017 3:10 AMNarine M - Tuesday, January 24, 2017 3:03 AMJohn Mitchell-245523 - Tuesday, January 24, 2017 2:59 AMDefinitely check your max server memory, yes. If you're not running any other applications or SQL Server components on the server, leave about 8GB for the operating system.What I'm curious about is how you know whatever problems you're having are caused by memory?
John
Always the memory utilization is high..Sometimes the server is slow and receiving the mails from client..Whether it is due to memory pressure..
As Andrew explained, high memory utilisation is expected behaviour. SQL Server will grab as much memory as it can and only return it if the operating system asks for it. There could be any number of reasons that the server is slow - you need to do a bit more investigation. As I and others have said, start by setting max server memory - that certainly won't do any harm.
John
Previously there is a CXpacket waits, so we changed the max DOP. Now the waits has been reduced. Now the queries are running fine.But the client is experiencing slowness for calls against databases.??? What will be the reason
It could be a number of reasons. Is actual database performance looking ok? Has the application server performance been looked at? How about network performance?
January 24, 2017 at 3:25 am
Narine M - Tuesday, January 24, 2017 3:16 AMJohn Mitchell-245523 - Tuesday, January 24, 2017 3:10 AMNarine M - Tuesday, January 24, 2017 3:03 AMJohn Mitchell-245523 - Tuesday, January 24, 2017 2:59 AMDefinitely check your max server memory, yes. If you're not running any other applications or SQL Server components on the server, leave about 8GB for the operating system.What I'm curious about is how you know whatever problems you're having are caused by memory?
John
Always the memory utilization is high..Sometimes the server is slow and receiving the mails from client..Whether it is due to memory pressure..
As Andrew explained, high memory utilisation is expected behaviour. SQL Server will grab as much memory as it can and only return it if the operating system asks for it. There could be any number of reasons that the server is slow - you need to do a bit more investigation. As I and others have said, start by setting max server memory - that certainly won't do any harm.
John
Previously there is a CXpacket waits, so we changed the max DOP. Now the waits has been reduced. Now the queries are running fine.But the client is experiencing slowness for calls against databases.??? What will be the reason
Well, if you've reduced your maxdop just because you were seeing CXPACKET waits, that could be contributing to the poor performance. I'm not sure how you can say the queries are running fine but the client is experiencing slowness? I hate to say this, but I think you're out of your depth. This isn't something we can diagnose from here, either - I would recommend you hire a consultant for half a day to a day. He or she could identify the causes of your performance issues and guide you on how to carry out such investigations yourself the next time this happens.
John
January 24, 2017 at 3:32 am
In our environment, we are experiencing slowness for calls against databases. what will be reason ..can anyone help on this...
January 24, 2017 at 3:33 am
One more thing, Some of the columns in the SP's are using full scan.. Whether that leads to memory pressure??
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply