Lifting The Lid On SQL Server Performance Using Splunk
Let's face it. Databases are still key in today's enterprise environments. They also tend to be complex and therefore the most misunderstood component of the application stack, resulting in databases being a disproportionately high contributor to stability issues and performance bottlenecks. Wouldn't it be great if you were able to readily gather, retrieve and visualise database performance metrics? Wouldn't it be even better if you were able to easily correlate these metrics with telemetry gathered from other areas of the application and infrastructure tier? If you have Splunk Enterprise and Microsoft SQL Server, you can!
In February of this year, Splunk released the Splunk Add-on for Microsoft SQL Server. Its goal is to facilitate the collection of performance, log, audit, and status data from Microsoft SQL Server deployments and to present this data in a CIM compatible format. There are four key sources of data collected by the Add-on.
- Logs - The Add-on tails the SQL Server Error Log and the SQL Server Agent Log;
- Perfmon - Windows host level as well as SQL Server specific perfmon metrics are collected;
- Dynamic Management View (DMV) Objects; and
- Audit and Trace logs.
A detailed discussion on the data gathered for each sourcetype, can be found in the Add-on documentation.
I had the opportunity to deploy Splunk's new Add-on for a client a couple of weeks ago. Deployment was quite straightforward:
- Install Universal Forwarder on the Database Server;
- Deploy Windows Add-on to Universal Forwarder with an inputs.conf file to enable the perfmon and file monitor input stanzas.
- Deploy Windows Add-on to the server running DB Connect with an inputs.conf file to enable to dbmon input stanzas.
Some things to be aware of:
- The current version of the Add-on (v1.0 at the time of this post) officially supports Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2012 Enterprise. However, I was able to use the Add-on to collect log, perfmon and DMV data from SQL Server 2014 Enterprise databases.
- When turning on the dbmon input for the DMV objects, enable them one at a time to get a handle on the volume of data generated by the input.
- There are no dashboards out of the box. The Add-on is concerned with the retrieval of data. That being said, the data is in CIM format and also in a format that can be readily visualised. It did not take much to pull together the dashboard below.
- Be sure when enabling dbmon inputs, be sure to explicitly specify the host, otherwise Splunk guesses the host, using the $decideOnStartup variable.
Happy (database) Splunking!