SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


High Memory utilization alerts in SQL Server 2008


High Memory utilization alerts in SQL Server 2008

Author
Message
Robin35
Robin35
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 1230
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15649 Visits: 14396
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
chandan_jha18
chandan_jha18
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 2134
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
SQL Show
SQL Show
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 1078
are you using 32 bit? can you post the error message you received?
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15649 Visits: 14396
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
Robin35
Robin35
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 1230
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...
Robin35
Robin35
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 1230
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
chandan_jha18
chandan_jha18
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 2134
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15649 Visits: 14396
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15649 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search