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

Alert for Memory Consumption Expand / Collapse
Author
Message
Posted Monday, August 31, 2009 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 43, Visits: 893
Can somebody provide some suggestions on how to configure an email alert for SQL Server memory consumption using counters like Total Server Memory(KB),Working Set or the SQL DMV counters?

I would prefer if this alert can be configured from inside SQL Server as I have DB mail configured.Example:It would send me an email if the Total Server Memory(KB) reaches 2GB.

Any help is appreciated.
Post #779918
Posted Monday, August 31, 2009 1:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 43, Visits: 893
Any thoughts on this guys?
Post #780187
Posted Monday, August 31, 2009 1:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:40 AM
Points: 110, Visits: 379
not sure how to configure an alert for that ....

but you could set up a job to poll this memory counter say every 1 minute and put it into a place holder table with a timestamp. then you could check last 2 values to see if it exceeded your threshold ... if it did send an email - database mail ... telling you that threshold exceede in the last 1 minute.

last step of this job could be to delete data older than a day or so from your place holder table to keep it under control.


-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! - desparately trying to fish
Post #780210
Posted Monday, August 31, 2009 2:42 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 4,386, Visits: 9,502
Not sure why you would be worried about that - since you have control over the max memory setting for SQL Server. If you set the max memory - you wouldn't need to worry about SQL Server taking up too much memory on the server.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #780261
Posted Monday, August 31, 2009 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 43, Visits: 893
This is a 32-bit Windows 2003 Enterprise server having 4GB RAM.Earlier we had enabled the /3GB switch on it to allow user processes including SQL to consume upto 3GB memory.But then we ran into memory leak issues arising out of older bios drivers and other stuff.Microsoft gave us some action items among which one was to remove this /3GB switch.
So now we are back to a 2GB user memory and I have to monitor the SQL memory consumption over the next few weeks to see if it is running fine under this limit or if it needs more memory.This is why I need to automate this memory monitoring process.
Now can you suggest something more substantial than a max memory setting?
Post #780283
Posted Monday, August 31, 2009 3:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 4,386, Visits: 9,502
Nope - can't suggest anything more than setting the max memory. SQL Server will take the max memory you assign it - and shouldn't take any more than that.

What you are really needing to look at is memory pressure issues? Again, SQL Server is not going to take more memory than is allocated - and in your configuration won't be able to use more than 2GB anyways.

So, you need to be monitoring for memory pressure - and not target memory/total memory on the server. These counters will just tell you if you have another memory leak issue - and not whether or not SQL Server is performing well with 2GB.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #780294
Posted Tuesday, September 1, 2009 5:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 43, Visits: 893
What are the appropraite counters which can be used from inside SQL Server for monitoring memory usage withing 2GB,which can tell me whether the allocated 2 GB max memory is fine or if I need to configue more memory?

Currently i have configured: Buffer Cache Hit ratio,Connection memory,Cache Hit ratio and Lazy writes/sec. Any thoughts?
Post #780579
Posted Tuesday, September 1, 2009 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 43, Visits: 893
Any suggestions guys??

Thanks.
Post #780999
Posted Wednesday, September 2, 2009 1:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 8, Visits: 177
Hi,

As this discussion open for the discuss about how we can actively monitor SQL Server memory utilization and you want to know by email.

Before we jump in to the any memory we have to first know how SQL will grab memory in to Server memory since we are talking about the SQL Server 32-bit edition on Windows Server 2k3 32-bit on without /3GB or USRENVA environment.

As we all know SQL or any application will get the 2GB of virtual Address now internally SQL Server allocated memory in two mail part. which is known as

1. Buffer pool (used for SQL Optimizer,Plan Generation Etc..
2. Non Buffer Pool (Which is AKA "Mem to leave Area")(Used for the 3rd party DLL COM component for extended stored proc and CLR related operation and it will be 256 MB default and


Now these two sections has their own separate function in the SQL Server. so if we are talking to the which region is consuming more memory is decided by the queries to SQL Sever DMV which is very use full.

-- I will give you some list good DMVs in SQL Server 2005 which will describe how you will find memory consumption by querying them

-- Now you can check the performance Counter with the SQL Server
-- SQL Server memory related DMV
-- DBCC MEMORYSTATUS output


-- In the SQL Server 2008 there are new DMV added for the dwell in to the problem of the SQL Server
sys.dm_os_memory_brokers
sys.dm_os_memory_nodes
sys.dm_exec_procedure_stats

-- Like we have there existing DMVs in the SQL Server 2005
sys.dm_os_sys_info
sys.dm_exec_requests
sys.dm_exec_requests

Please refer to the BOOKS Online for further description of the these DMV's these all DMVs will talk about the buffer pool



-- Now let's talk about how will you check for Non Buffer pool memory (Mem to leave) we can use the following DMV
sys.dm_os_virtual_address_dump

Please let me know if you need any more information about it.

Regards,
Nirav Joshi
Post #781782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse