Management pack to monitoring and collect SQL Memory for each instance
With the new SQL MP, you now have a collection of the percent CPU use by an instance. But still nothing on the memory ?
It could quite complicated to go with perfmon as the proccess doesn't have the name of the instance in it :/
But you can retive the memory use by SQL DB engine by running the following queries:
select cntr_value from sys.dm_os_performance_counters where counter_name like 'Total Server Memory (KB)%'
This MP is design to provide you 2 collection rules and a monitor. It’s apply on SQL 2008, 2008 R2 and 2012 DB engine.
First rule is named: “MSSQL 2008: Collect DB Engine Memory (MB)”
It simply collect the MB consume by this instance.
Second rule is: “MSSQL 2008: Collect DB Engine Memory (%)”
The percentage is the amount of MB use divided by the SQL max memory set for the instance.
If SQL max memory isn't configured, it gets system memory instead.
The monitor will raise an alert if the percentage value goes over 90% (4 samples every 5 min)
Here is a look on what it's look like :
Prerequisite of this MP : SQL MP 6.4.1.0
Find the MP has attachement or on TechNet Gallery : https://gallery.technet.microsoft.com/SQL-Memory-Collection-and-c04ae51e