July 13, 2012 at 10:05 am
The SQL database is using most of the memory. The memory is at 95%. There were also SQL errors in the application logs. As a DBA how do you solve this problem?
July 13, 2012 at 10:10 am
Depends what the errors are.
SQL uses a lot of memory. That's by design and a good thing. You should always limit the max server memory to a sensible value based on your server and what else you have running on it, if SQL is still using too much memory you'd just lower max server memory slightly.
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 14, 2012 at 6:34 am
By any chance did you see the following message in the SQL error log?
"A significant part of sql server memory has been paged out. This may result in performance degradation."
if that is indeed the case then you may have to re-evaluate ( as mentioned in the earlier reply from GilaMonster) as to how much memory you are leaving for the operating system.
Are the errors logged at a particular time? and Is this a 64 bit system?
Further have you set the lock pages in memory option? Though not always a very good idea this option prevents the operating system from paging out SQL server memory when it needs that extra bit of memory when other stuff like file copy is in progress.
Also keep in mind that extended stored procedures use memory outside SQL server buffer space and therefore add to operating system memory usage. I think this applies to CLR routines as well. If you are using external third party backup tool it is likely that they are using extended stored procedures.
There are SQL Server DMVs available that can be queried for checking memory related pressure \ issues.
The information from these DMVs will help you confirm whether:
1) Memory grants are pending on a regular basis.
2) Page Life Expectancy looks good or bad.
3) Inefficient ad-hoc queries (which are generally executed only once) are wasting internal SQL server buffer memory.
'
This looks like a interesting problem and I am sure you will end up learning a lot when working on troubleshooting the same.
Good luck with the resolution.
July 16, 2012 at 8:02 am
Thanks a lot for your reply's
July 17, 2012 at 9:06 am
I always set max memory for our SQL Servers to leave 2 gig free for Windows and other things running on the server. You are just asking for trouble if you don't put a max on SQL Server.
July 17, 2012 at 10:34 am
Markus (7/17/2012)
I always set max memory for our SQL Servers to leave 2 gig free for Windows and other things running on the server. You are just asking for trouble if you don't put a max on SQL Server.
In many case 2GB is sufficient but on larger systems these days you will need to look at a true algorithm.
This is from a sql cat article.
Configure the SQL Server “max server memory (MB)” setting by taking the amount of memory allocated to the database system and subtracting one GB for every four cores (round up). This leaves the operating system with enough memory to work efficiently without having to “grab” memory back from SQL Server. For example, if the server has 64 GB of RAM and 24 cores, set the maximum memory to 58 GB (64 GB minus 6 [24 cores divided by 4]).
July 17, 2012 at 10:40 am
MysteryJimbo (7/17/2012)
Markus (7/17/2012)
I always set max memory for our SQL Servers to leave 2 gig free for Windows and other things running on the server. You are just asking for trouble if you don't put a max on SQL Server.In many case 2GB is sufficient but on larger systems these days you will need to look at a true algorithm.
This is from a sql cat article.
Configure the SQL Server “max server memory (MB)” setting by taking the amount of memory allocated to the database system and subtracting one GB for every four cores (round up). This leaves the operating system with enough memory to work efficiently without having to “grab” memory back from SQL Server. For example, if the server has 64 GB of RAM and 24 cores, set the maximum memory to 58 GB (64 GB minus 6 [24 cores divided by 4]).
You are correct. I guess I should have said my reply is with not understanding the environment and larger systems will require more thought. Mine is just a rule of thumb for most basic systems.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply