For the impatient, here is the link to JiMetrics web site, where you can get the scripts and get going. To learn why it is called JiMetrics, you will have to read all the way to the end.
During the last couple of years, I’ve been working with health care related BI data warehouse, running on dozens of SQL Server instances. The workflow is pretty typical: nightly loads, daily usage for reporting, analytical, and ad-hoc research questions, general administration, instance monitoring and upgrade, things of that nature. As a result, I do a lot of administrative and DevOps sort of work.
To support that, and for monitoring, planning, and provisioning, we need to collect and store metrics data for analysis. Here are a few examples of typical use cases:
- Storage management and provisioning. We need total size and available space for all disk drives on a server. Not just disks with drive letters, but also mount points on Windows. This sounds silly, but I was surprised to learn that not all monitoring software does that for Windows;
- Software/hardware asset management. What hardware does it run on (core count, CPU class, manufacturer)? What about memory size? Is it a VM or a physical server?
- Software patching. What is service pack level of the operating system? What about its version number? Do we need to patch the server?
- How many rows of records do we have for tables holding information on allergy reactions on day 1, day 5, day 23, and day 500? For that table, how much space does it take? Of that space, how much is taken by data and how much is taken by indexes? What do we need to do to anticipate, plan, and execute so that we are on top of things?
- What about instance configuration? Has the max memory size of a SQL Server instance been changed in the last few weeks?
I surveyed the landscape. There are a few commercial products that could work, but were not picked in the end due to licensing issues. Microsoft’s MAP Toolkit and SCCM can be helpful, but like so many Microsoft products, they are unnecessarily complex and cumbersome. They tend to try too hard to be cute but feel annoying in the end. So I started writing my own. In fact, I wrote two versions: one for general purposes that I worked on the side, and the other customized version based on internal requirements and instructions. The one that I worked on my own time is called JiMetrics.
When I started writing, it wasn’t a difficult decision to use SQL Server database for metrics data storage, because most servers are running Windows and SQL Server. I did think about using column-store databases for metrics data storage due to the nature of the data: insert only, no updates, and good compression. InfoBright provides a wonderful and free column-store database engine under MySQL, but I didn’t have enough time. I may work on that later. It looks that SQL Server 2014 will have true column-store tables (to be verified), instead of the column-store indexes introduced in SQL Server 2012. SQL Server 2012’s column-store index feels like a hack job that was put together quickly for marketing purposes, but I digress.
I struggled a bit with what language to use. I thought about Python, Perl, PowerShell, and Ruby. From personal taste and productivity point of view, I’d rather use Python or Perl. However, in the end I chose PowerShell because of its tight integration with Windows and various Microsoft server products, and the fact that it is bundled with Windows so it is always there. Besides, I use SQL Server Agent to schedule collection processes, which makes PowerShell even more compelling.
So check out JiMetrics and let me know what you think. If you are interested, I suggest you start collecting data early. It is light weight and won’t take up much space. Down the road, I think you will be happy that you start the collection earlier. Currently, I’ve coded processes that captures the following metrics:
- Windows server host and storage metrics
- SQL Server instance discovery and instance configuration info
- Select SQL Server instance performance counters
- All SQL Server instance user database file metrics
- All SQL Server instance user database table stats
As to the JiMetrics name, I initially picked SysMetrics, but sysmetrics.com is taken (possibly due to domain squatting, because the site has an on-sale sign). I purchased sysmetrics.org and even set it up, but in the end decided a com domain is far easier for people to remember. Fortunately JiMetrics was available. I hope it sounds good and is easy enough to remember.