PowerShell and Performance Monitor (Perfmon) Counter

I previously blogged about how to evaluate SQL server performance by using performance monitor (Perfmon) or logman to capture performance counters. Recently I discover that PowerShell could be utilize to capture the performance counters as well as a better tool to automate process. This post consist of the PowerShell Script I developed and how the perfmon data are imported into the database.

There are multiple way to use PowerShell to capture the performance counter, it could be used together with logman, or use the PowerShell Cmdlets Get-Counter to get the performance counter. The example script below is using PowerShell Cmdlets.

To extract the performance counter, we pass the variable $server (server name) and $counters (counter lists) and its sample interval and maximum sample size.

$server = 'YourRemoteServerName'
$counters = @("\Processor(*)\% Processor Time", "\System\Processor Queue Length")
Get-Counter -ComputerName $server -Counter $counters -SampleInterval 2 -MaxSamples 1

Note that the @ is used as an array identifier. For local computer, the -ComputerName parameter is not required. This cmdlet return the perfmon counterst result as shown below.

For scalability, I used a parameterized function to pass in server and SQL instance name. The PowerShell script below invoke a Get-Counter Cmdlets to extract one set of perfmon counters of a remote server (e.g. YourRemoteServerName) and some of the SQL counters for an instance (e,g. SQLTest) named instance every time the PowerShell is executed. The results is then imported into a SQL monitoring database through a stored procedure.

Since we are using Invoke-Sqlcmd cmdlet, we need to import the sqlps module. Before you import the sqlps module, you may need to change your execution policy. By default, the execution policy is set to restricted.

Use Get-ExecutionPolicy to identify your current execution policy.

Use Set-ExecutionPolicy cmdlet to change execution policy. For example, to run script you created or scripts have been signed by trusted publisher, you could change it to RemoteSigned,

Set-ExecutionPolicy RemoteSigned

After setting the appropriate execution policy, depending on PowerShell installed version, you could use the command below,

PowerShell 2.0 and 3.0
Import-Module "sqlps" -DisableNameChecking

For PowerShell 1.0
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapon SqlServerProviderSnapin100

Here is the PowerShell (3.0) script,

Import-Module "sqlps" -DisableNameChecking

function ExtractPerfmonData 
{
    param(
        [string]$server, 
        [string]$instance
        )

    [Microsoft.PowerShell.Commands.GetCounter.PerformanceCounterSampleSet]$collections
    
    $monitorServer = "YourMonitorServerName"
    $monitorDB = "YourMonitorDatabase"

$counters = @(
        "\Processor(*)\% Processor Time",
        "\System\Processor Queue Length",
        "\Memory\Available MBytes",
        "\Memory\Available MBytes",
        "\Paging File(*)\% Usage",
        "\$($instance):Memory Manager\Memory Grants Pending",
        "\$($instance):Memory Manager\Total Server Memory (KB)",
        "\$($instance):Buffer Manager\Page Life Expectancy",
        "\$($instance):General Statistics\User Connections",
        "\$($instance):SQL Statistics\Batch Requests/sec",
        "\$($instance):SQL Statistics\SQL Compilations/sec",
        "\$($instance):SQL Statistics\SQL Re-Compilations/sec"
        )
    
    $collections = Get-Counter -ComputerName $server -Counter $counters -SampleInterval 1 -MaxSamples 1

    $sampling = $collections.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue
    $xmlString = $sampling | ConvertTo-Xml -As String

    $query = "dbo.usp_InsertPerfmonCounter '$xmlString';"
    Invoke-Sqlcmd -ServerInstance $monitorServer -Database $monitorDB -Query $query
}

ExtractPerfmonData -server "YourRemoteServerName" -instance "MSSQL`$SQLTest"

Please note that there is a backtick(`) on the SQL named instance to escape the dollar($) sign. For default SQL Server instance, the instance name would be SQLSERVER.

On SQL server, I created a simple table to store all the performance counter data. A stored procedure is also created to insert the data into the table. Since the counters resultset from PowerShell is delimited, I convert it into xml format and later as a string. SQL XQuery (value) is used to extract the xml and its data is inserted into the SQL table.

USE [YourMonitorDatabase];

CREATE TABLE [dbo].[PerfmonCounterData]
(
  [ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
  [Server] [nvarchar](50) NOT NULL,
  [TimeStamp] [datetime2](0) NOT NULL,
  [CounterGroup] [varchar](200) NULL,
  [CounterName] [varchar](200) NOT NULL,
  [CounterValue] [decimal](18, 5) NULL
);
GO

CREATE PROCEDURE [dbo].[usp_InsertPerfmonCounter]
(
  @xmlString varchar(max)
)
AS
SET NOCOUNT ON;
 
DECLARE @xml xml;
SET @xml = @xmlString;
 
INSERT INTO [dbo].[PerfmonCounterData] ([TimeStamp], [Server], [CounterGroup], [CounterName], [CounterValue])
SELECT [Timestamp]
 , SUBSTRING([Path], 3, CHARINDEX('\',[Path],3)-3) AS [Server]
 , SUBSTRING([Path]
      , CHARINDEX('\',[Path],3)+1
      , LEN([Path]) - CHARINDEX('\',REVERSE([Path]))+1 - (CHARINDEX('\',[Path],3)+1)) AS [CounterGroup]
 , REVERSE(LEFT(REVERSE([Path]), CHARINDEX('\', REVERSE([Path]))-1)) AS [CounterName]
 , CAST([CookedValue] AS float) AS [CookedValue]
FROM
    (SELECT
        [property].value('(./text())[1]', 'VARCHAR(200)') AS [Value]
        , [property].value('@Name', 'VARCHAR(30)') AS [Attribute]
        , DENSE_RANK() OVER (ORDER BY [object]) AS [Sampling]
    FROM @xml.nodes('Objects/Object') AS mn ([object]) 
    CROSS APPLY mn.object.nodes('./Property') AS pn (property)) AS bp
PIVOT (MAX(value) FOR Attribute IN ([Timestamp], [Path], [CookedValue]) ) AS ap;
GO

That's it. You can run schedule task to run the PowerShell script manually, or schedule a SQL Agent job at the frequency desired and do further analysis from the perfmon data captured and stored in the SQL table.. Be aware of the PowerShell version if you plan to run it with SQL Agent job. Refer to this blog post, SQL Server Agent Job and Powershell Version.