JiMetrics

Here are step by step instructions on setting up a process to collect Windows and SQL Server related metrics data. Most of the automation scripts are written in PowerShell. To be honest, given a choice, I'd rather write my tools in either Python or Perl. However, on Windows systems, those languages require a separate install, whereas PowerShell is present already. That, plus the tight integration between PowerShell and Windows (and other Microsoft server products), makes starting with PowerShell pretty compelling. I may present the same tools written in Python, Perl, and/or other languages if/when I develop them.

The whole process is pretty lightweight, and I suggest you start doing this as soon as possible. Over time, you will find the information gathered very valuable and you'd be happy with the fact that you started the collection sooner rather than later.

Please follow this link to create the SysMetrics database.

Enter Windows servers whose metrics you are interested in gathering. You can do this manually by running SQL statements like this:

USE SysMetrics;
GO;
INSERT INTO Windows.Host (HostName) VALUES ('WindowsHost1');
GO;

Or, if you have a list of servers in a file, it is not hard to import that into the Host table.

Please follow this link to create a SQL Server job for updating server host info. These include things like domain, OS Version Number, hardware model, hardware vendor, memory size in GB, CPU type, CPU core count. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.

Please follow this link to create a SQL Server job for gathering server host disk information. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.

Please follow this link to create a SQL Server job for discovering installed SQL Server instance(s) on server hosts in the Host table. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.

After SQL Server instances are discovered in server hosts, it is now time to get some metadata about those instances. This step gathers information like instance edition, version number, service pack level, and whether or not it is running. Please follow this link to create a SQL Server job. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.

After server hosts, hosts' disk information, and SQL Server instance information have been gathered, we drill down one more level to metrics about database files. These include database name, its associated files, file size, free size, max size information, and growth property (percentage growth or not). Please follow this link.

We drill down one more level to metrics about database tables. These include database name, schema name, table name, total row count, data size, and index size. Please follow this link.