Does SCOM collect the memory the memory usage of SQL instances? Say there were multiple instances on an SQL server, would it be possible to see the individual memory usage of each one? In particular I’m after a method to determine the max memory limitations placed on an SQL instance without having to use SQL management studio.
SQL DB Engine – Stolen Server Memory (MB) is collected, but
Stolen Server Memory (KB) shows the amount of memory used by SQL Server, but not for database pages. It is used for sorting or hashing operations, or “as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information”
As far as I can see, this is the only SQL memory metric available, that isn’t simply the memory associated with the OS.
It’s like that this is something that SQL itself is aware of – Do you have a TSQL query that can give you this info? If so, it would be fairly easy to collect this as a perf rule using the PowerShell Authoring MP (https://www.cookdown.com/scom-essentials/powershell-authoring) and the SQL CMD module.
Alternatively, if collecting it isn’t so much of a concern, then SquaredUp’s SQL tile will let you run SQL queries directly against any SQL database and return a Scalar, tabular, or time series (graph): https://support.squaredup.com/hc/en-us/articles/360007867677-How-to-use-the-SQL-tile