Logging DMVs from Microsoft SQL Server with PowerShell

Some systems are easy to monitor and diagnose – just Splunk the log file or performance counter and you are pretty much done. Others take a little more work. Take, for example, Microsoft SQL Server. Many of the best bits of management information are stored in Dynamic Management Views, or DMVs. Getting to them is not so straight forward.

In order to get those nuggets, we need to do some pre-work. Firstly, install a Splunk Universal Forwarder on the SQL Server. Then fire up the SQL Server Management Studio and add the LOCAL SYSTEM account to the sysadmin role. This will allow the local machine access to all the information you need to monitor any database within the instances that are installed. If you have multiple instances on the server then make sure you add the LOCAL SYSTEM to the sysadmin role on each instance. Finally, push or install the SA-ModularInput-PowerShell to the Splunk Universal Forwarder. This will allow you to grab the information you need.

Now that we have the pre-work out of the way, we can start concentrating on the basics. In the Splunk App for SQL Server, I have a PowerShell module that simplifies the SQL Server access. For instance, it has a command to list the instances on the server:

PS#> Import-Module .\\Common.psm1
PS#> Get-SQLInstanceInformation

This will list out the instances, most notably a field called ServerInstance. You can feed this to another cmdlet to get database information:

PS#> Get-SQLInstanceInformation | Get-SQLDatabases

We want to get access to the Dynamic Management Views. These are accessed via SQL statements. To assist with this, I have another module called SQL.psm1. For instance, in the Splunk App for SQL Server, I include an indexhealth.ps1 script. This runs a DMV query to find out if any indices are suggested for any databases within the instance. Here is the basic process:

PS#> $conns = (Get-SQLInstanceInformation | `
    Where-Object { $_.ServiceState -eq "Running" } | `
    Open-SQLConnection
PS#> $conns | Invoke-SQLQuery -SourceType "MSSQL:DMV:Indexes" -Query $query
PS#> $conns | Close-SQLConnection

As you can see, it’s a three part process. Firstly, we open up connections to each of the running instances. Secondly, we execute our SQL query to retrieve the information. The Invoke-SQLQuery is a wrapper around Invoke-SQLCmd that also formats the objects to be Splunk-friendly. Finally, we close the connections. You can place this in a *.ps1 script and use the PowerShell modular input to execute it.

You can find both modules mentioned in the TA-SQLServer bin directory when you install Splunk App for SQL Server.

The real power here is the DMV-related SQL query. For the index health, here is the query:

SELECT
    DB_NAME(s.database_id) AS [DatabaseName],
    OBJECT_NAME(s.[object_id]) AS [ObjectName],
    i.name AS [IndexName],i.index_id,
    user_seeks + user_scans + user_lookups AS [Reads],
    user_updates AS [Writes],
    i.type_desc AS [IndexType],
    i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE i.index_id = s.index_id AND i.index_id != 0

You can find a wide array of DMV related SQL scripts sites such as MSSQLTips.com – particularly this blog post, and to get you started, here is a list of five more queries.

Now, go forth and monitor that SQL Server!