May 15, 2013 at 11:51 am
Hi,
We are getting alerts from our monitoring tool Idera SQL Diagnostic manager ( which is use as monitoring and notification tool ). We are receiving these alerts since 4 days. I don't see anything unusual in the server like sessions using to more memory...not sure how to proceed and decide if its SQL memory issue orOS issue.
Environment Details :
SQL Server 2008 SP1, standard Edition, Clustered with Active/passive nodes
Windows Server 2008 Enterprise SP2
Memory : 12 GB
SQL Memory config : Min 7168, Max 8192 MB
Processors : 16
Please let me know if you need any information
May 15, 2013 at 2:00 pm
What kind of alert? That the OS is low on memory? How low?
You have max memory set but that only pertains to the buffer pool. Did you check to see if SQL Server is using an increased amount of non-buffer pool memory over the last 4 days? This can be caused by things like Linked Servers, large execution plans, SQLCLR processes, among lots of other things.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2013 at 2:35 am
As you posted, you have already fixed SQL Server and given it a range between which it an fly up or down.
I would suggest to collect some 'Available Memory' through Perfmon or DMVs to see if the remaining memory on the OS goes too low(depending upon threshold of diagnostic manager). There could be some process other than SQL causing this and should be looked upon. Ideally the operating system should have 2-3 GBs free all the time. Going too low may crash it
Thanks
Chandan
May 16, 2013 at 4:28 am
are you using 32 bit? can you post the error message you received?
May 16, 2013 at 7:05 am
chandan_jha18 (5/16/2013)
As you posted, you have already fixed SQL Server and given it a range between which it an fly up or down.I would suggest to collect some 'Available Memory' through Perfmon or DMVs to see if the remaining memory on the OS goes too low(depending upon threshold of diagnostic manager). There could be some process other than SQL causing this and should be looked upon. Ideally the operating system should have 2-3 GBs free all the time. Going too low may crash it
Thanks
Chandan
Where you getting this information?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2013 at 7:26 am
Thanks for the repsonse guys...
Today it went to 99.33%
Here's the alert info...
5/16/2013 3:36:32 AM, OS Memory Usage (Percent) on XXX is Warning.
SQL Server instance XXX - The operating system has 99.33% of memory in use.
OS Memory Usage (Percent): Percentage of total memory used on the SQL Server computer.
And there is no linked server on this instance, and CLR is enabled ( not sure if its using CLR )
we are using 64 bit
Available memory Details :
Total_Physical_Memory_In_MB
12277
Available_Physical_Memory_In_MB
12482
System_Memory_State_Desc ( dont know what this means )
Available physical memory is high
Used this query :
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
Please let me know how to proceed...
May 16, 2013 at 7:30 am
One application job has run from 3:30 AM to 5 AM today morning...we got the alert around...4:07 AM.
Not sure if its the reason...
Do i need to change the memory config of sql server to make this alert go ?
please let me know
thanks
May 16, 2013 at 7:40 am
opc.three (5/16/2013)
chandan_jha18 (5/16/2013)
As you posted, you have already fixed SQL Server and given it a range between which it an fly up or down.I would suggest to collect some 'Available Memory' through Perfmon or DMVs to see if the remaining memory on the OS goes too low(depending upon threshold of diagnostic manager). There could be some process other than SQL causing this and should be looked upon. Ideally the operating system should have 2-3 GBs free all the time. Going too low may crash it
Thanks
Chandan
Where you getting this information?
Sir, Apologize if there is a mistake in the comment above. As far as I know, we restrict memory for SQL Server so that if some other process wants to get some memory, the sql server does not have to release it from its buffer which is not considered ideal.
Also, through perfmon counters, we can track how much physical memory is available on OS and if it goes too low, it indicates external memory pressure and I have read so many times even in this forum that the OS should have some free memory for its own work.
Kindly let me know which assumption is wrong here.
Thanks
Chandan
May 16, 2013 at 9:59 am
Robin35 (5/16/2013)
One application job has run from 3:30 AM to 5 AM today morning...we got the alert around...4:07 AM.Not sure if its the reason...
Do i need to change the memory config of sql server to make this alert go ?
please let me know
thanks
The way you have your SQL Server configured:
Memory : 12 GB
SQL Memory config : Min 7168, Max 8192 MB
Says that 4 GB will be left for non-buffer pool memory. This includes some memory (for things mentioned earlier) used by SQL Server, but also for the OS. You need to find out what else is using memory on your server. You could back down the max memory setting from 8192 to something lower, but chances are whatever this thing is, it will gobble up whatever else you leave open for it to grab anyway so best to figure out what it is. What you can do is, setup a PowerShell script to run every minute that stores the list of all OS processes and how much memory they are using. Then, when you encounter an issue like this you can refer to that list and see the culprit. Maybe your monitoring software, the one sending you the alerts, is already doing this or can be setup to do it without writing any code yourself.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2013 at 10:05 am
chandan_jha18 (5/16/2013)
opc.three (5/16/2013)
chandan_jha18 (5/16/2013)
As you posted, you have already fixed SQL Server and given it a range between which it an fly up or down.I would suggest to collect some 'Available Memory' through Perfmon or DMVs to see if the remaining memory on the OS goes too low(depending upon threshold of diagnostic manager). There could be some process other than SQL causing this and should be looked upon. Ideally the operating system should have 2-3 GBs free all the time. Going too low may crash it
Thanks
Chandan
Where you getting this information?
Sir, Apologize if there is a mistake in the comment above. As far as I know, we restrict memory for SQL Server so that if some other process wants to get some memory, the sql server does not have to release it from its buffer which is not considered ideal.
Also, through perfmon counters, we can track how much physical memory is available on OS and if it goes too low, it indicates external memory pressure and I have read so many times even in this forum that the OS should have some free memory for its own work.
Kindly let me know which assumption is wrong here.
Thanks
Chandan
It is good to leave some memory for non-buffer pool and non-SQL Server processes, and to leave enough to allow for peak usage of those processes so as not to force the OS to make a hard decision about who to give memory too, or worse, to page memory to disk. However making a blanket statement about leaving 2-3 GB RAM free at all times on a machine with only 12GB of RAM is not what I would call a good use of available resources in a lot of cases. It will depend on what else the server does. Some servers dedicated to running SQL Server happily run with 99% of RAM utilized on a consistent basis because a level of stability has been reached where that is possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2013 at 12:33 pm
opc.three (5/16/2013)
chandan_jha18 (5/16/2013)
opc.three (5/16/2013)
chandan_jha18 (5/16/2013)
As you posted, you have already fixed SQL Server and given it a range between which it an fly up or down.I would suggest to collect some 'Available Memory' through Perfmon or DMVs to see if the remaining memory on the OS goes too low(depending upon threshold of diagnostic manager). There could be some process other than SQL causing this and should be looked upon. Ideally the operating system should have 2-3 GBs free all the time. Going too low may crash it
Thanks
Chandan
Where you getting this information?
Sir, Apologize if there is a mistake in the comment above. As far as I know, we restrict memory for SQL Server so that if some other process wants to get some memory, the sql server does not have to release it from its buffer which is not considered ideal.
Also, through perfmon counters, we can track how much physical memory is available on OS and if it goes too low, it indicates external memory pressure and I have read so many times even in this forum that the OS should have some free memory for its own work.
Kindly let me know which assumption is wrong here.
Thanks
Chandan
It is good to leave some memory for non-buffer pool and non-SQL Server processes, and to leave enough to allow for peak usage of those processes so as not to force the OS to make a hard decision about who to give memory too, or worse, to page memory to disk. However making a blanket statement about leaving 2-3 GB RAM free at all times on a machine with only 12GB of RAM is not what I would call a good use of available resources in a lot of cases. It will depend on what else the server does. Some servers dedicated to running SQL Server happily run with 99% of RAM utilized on a consistent basis because a level of stability has been reached where that is possible.
thanks very much for the response....
i will try setup a job to see the processes.. i'm believe this is an OS issue...
At the time when alert was generated...i don't see much activity in the sql server ( we can see past history using history browser in Idera ).
and here we got another alert on this instace...not sure if its related to memory...
5/16/2013 10:40:14 AM, SQL Server Thread Count on XXXXXX is Warning.
SQL Server instance XXXXXX- SQL Server Thread Count is 400.00.
SQL Server Thread Count: The number of threads currently active in this process. An instruction is the basic unit of execution in a processor, and a thread is the object that executes instructions. Every running process has at least one thread.
please let me know if you need anymore information
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply