August 31, 2009 at 8:14 am
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.
August 31, 2009 at 1:29 pm
Any thoughts on this guys?
August 31, 2009 at 1:48 pm
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 !! :w00t: - desparately trying to fish [/size]
August 31, 2009 at 2:42 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 31, 2009 at 3:04 pm
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?
August 31, 2009 at 3:24 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 1, 2009 at 5:25 am
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?
September 1, 2009 at 1:12 pm
Any suggestions guys??
Thanks.
September 2, 2009 at 1:40 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply