I’ve been using my own SQL Server metrics collection package called JiMetrics for a couple of years. It is easy to set up. All you need are just two things: SQL Server instance and an account that has admin access to both the servers and instances you care about.
JiMetrics doesn’t do anything that will be hard to undo: no binary install, no registry changes, no cookies and temp files to store states and all that junk that pollutes your environment. Its footprint is small and just quietly collects important metrics data at an interval of your own choosing. Follow steps here and start today. As a system administrator, you will be glad to have the metrics data for analysis and decision making.
Today I made the database a bit better: I changed the database schema by adding a new column, IsVM, to the Windows.Host table. Here is the idea:
- IsVM tells you if a particular Windows host is a virtual machine or not;
- IsVM can have only one of two values: “Y” and “N”, and is not NULLable;
- IsVM is a persisted computed column. The computation is done via the Manufacture property of Win32_ComputerSystem class;
- Currently, if the Manufacure is one of “microsoft”, “xen”, or “innotek GmbH”, then IsVM value is “Y”, otherwise it is “N”.
Here is the relevant portion of the computed column definition:
[IsVM] AS (CASE HardwareVendor WHEN 'innotek GmbH' THEN 'Y' WHEN 'microsoft' THEN 'Y' WHEN 'xen' THEN 'Y' ELSE 'N' END) PERSISTED,
Enjoy! If you have any comments on either of these questions, I’d love to hear them:
1. What other values of Manufacture in Win32_ComputerSystem indicates a VM, other than the three I listed above?
2. Are there better ways to detect if a Windows host is a VM?