Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

High Memory utilization alerts in SQL Server 2008 Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 11:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:54 PM
Points: 92, Visits: 737
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
Post #1453245
Posted Wednesday, May 15, 2013 2:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1453263
Posted Thursday, May 16, 2013 2:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 459, Visits: 1,884
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
Post #1453367
Posted Thursday, May 16, 2013 4:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
are you using 32 bit? can you post the error message you received?
Post #1453411
Posted Thursday, May 16, 2013 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1453500
Posted Thursday, May 16, 2013 7:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:54 PM
Points: 92, Visits: 737
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...

Post #1453520
Posted Thursday, May 16, 2013 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:54 PM
Points: 92, Visits: 737
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
Post #1453524
Posted Thursday, May 16, 2013 7:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 459, Visits: 1,884
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
Post #1453536
Posted Thursday, May 16, 2013 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1453643
Posted Thursday, May 16, 2013 10:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1453650
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse