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.

Setting this up is pretty easy. Here is a quick overview before we delve into the details into the rest of the page:

• We will need to create a SQL Server database, JiMetrics, to store metrics data;
• The collection process relies on SQL Server Agent;
• The domain account SQL Server Agent jobs run under must have:
  • Administrator access to all server hosts you intend to collect metrics data from.
  • dbo access to the JiMetrics database. SQL Server Agent job steps, described below, use stored procedures heavily
• Manual steps needed are minimal. If you create steps in the order that is presented in this page, you will be fine. Each PowerShell script corresponds to one SQL Server Agent job. Inside the PowerShell script, it knows which stored procedure to call, which Hosts are involved, what metrics to gather, and which table to put that metrics into. Here is a pretty typical use case:
  1. Create the database with the SQL script in the link below, which has all necessary tables and stored procedures;
  2. Populate the Host table. You only need to insert HostName for each record. The rest of the property associated with a Host such as Domain, OS, Version number, hardware model, hardware vendor, memory, CPU, and such will be discovered with one of the SQL Server Jobs, described in the next step below. Note that the Host table has a property called IsActive. Update that column to 'N' to indicate that it is no longer active.
  3. Now it is time to create the job to update the Host table since we have HostName populated. You don't need to schedule this job often, since Host info does not change that much. Monthly scheduling, or even kicking it off manually on an ad-hoc basis would be fine;
  4. The next step is to create and schedule a job to gather Host storage/disk information. Schedule this daily would be a pretty good start;
  5. Now it is time to discover SQL Server instances. Once again, this information is fairly static. Running it on a monthly schedule or even manually would be fine;
  6. Once SQL Server instances are discovered, it is time to gather info such as instance version, edition, and if it is running. If it is not running, then its IsActive will be 'N', and subsequent steps will filter this instance out. Once again, running it monthly or as needed would be fine;
  7. Next we create job to gather SQL Server instance configuration information. The scheduling of this job really depends on how often those parameters are tweaked. Running that as often as you feel necessary, weekly, biweekly, monthly, or as needed;
  8. For DMV performance counters, it once again depends on your situation, your network latency, and how many instances you are monitoring with this job. Typically, this job should be run at least a few minutes. Running it every 15 minutes would be a good start;
  9. Next we come to SQL Server database file stats. Scheduling this daily would be a pretty good start;
  10. For table stats, once again, scheduling it daily is a pretty good starting point.
• Once you've started collecting for a while, you will certainly be able to get a lot out of this information. There are very few tables involved so the database is pretty easy to query against. I suggest you follow the database diagram for help.

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 JiMetrics database for metrics data storage.

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

USE JiMetrics;
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 all active servers in the Windows.Host table, it is now time to find out if the instances are running and their edition level and versions. 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.

Now that we have names of all currently running SQL Server instances, it is time to gather their configuration information, such as its min and max memory usage boundaries, instance level parallelism, CLR usage, et cetera. Please follow this link to create a SQL Server job to gather that data.

To get a good handle on the performance characteristics and metrics, it is important to gather and store performance counters that can shed light on that. Please follow this link to create a SQL Server job to gather that data.

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.