Load Perfmon Log Data into SQL Server with PowerShell

In yesterday's post I showed you how I set up performance data gathering in an environment where I can't directly update my analysis database. Today I'll show you how I load that data into SQL Server for the analysis.

First, I create tables to contain the performance counters in my analysis database. Here's what they look like:

CREATE TABLE [Analysis].[ServerStats] (
    ServerID    int IDENTITY(1,1) NOT NULL,
    ServerNm    varchar(30) NOT NULL,
    PerfDate    datetime NOT NULL,
    PctProc     decimal(10,4) NOT NULL,
    Memory      bigint NOT NULL,
    PgFilUse    decimal(10,4) NOT NULL,
    DskSecRd    decimal(10,4) NOT NULL,
    DskSecWrt   decimal(10,4) NOT NULL,
    ProcQueLn   int NOT NULL
    CONSTRAINT [PK_ServerStats] PRIMARY KEY CLUSTERED 
    (
        [ServerID] ASC
    )
)
GO

CREATE TABLE [Analysis].[InstanceStats] (
    InstanceID  int IDENTITY(1,1) NOT NULL,
    ServerID    int NOT NULL,
    ServerNm    varchar(30) NOT NULL,
    InstanceNm  varchar(30) NOT NULL,
    PerfDate    datetime NOT NULL,
    FwdRecSec   decimal(10,4) NOT NULL,
    PgSpltSec   decimal(10,4) NOT NULL,
    BufCchHit   decimal(10,4) NOT NULL,
    PgLifeExp   int NOT NULL,
    LogGrwths   int NOT NULL,
    BlkProcs    int NOT NULL,
    BatReqSec   decimal(10,4) NOT NULL,
    SQLCompSec  decimal(10,4) NOT NULL,
    SQLRcmpSec  decimal(10,4) NOT NULL
    CONSTRAINT [PK_InstanceStats] PRIMARY KEY CLUSTERED 
    (
        [InstanceID] ASC
    )
)
GO
ALTER TABLE [Analysis].[InstanceStats] WITH CHECK ADD  CONSTRAINT [FX_InstanceStats] FOREIGN KEY([ServerID])
REFERENCES [Analysis].[ServerStats] ([ServerID])
GO

ALTER TABLE [Analysis].[InstanceStats] CHECK CONSTRAINT [FX_InstanceStats] 
GO

I also use stored procedures to load the data, so here's that code (pretty straight CRUD process):

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Analysis].[insServerStats]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [Analysis].[insServerStats]
GO
CREATE PROCEDURE [Analysis].[insServerStats]
           (@ServerID       int OUTPUT
           ,@ServerNm       varchar(30) = NULL
           ,@PerfDate       datetime = NULL
           ,@PctProc        decimal(10,4) = NULL
           ,@Memory     bigint = NULL
           ,@PgFilUse       decimal(10,4) = NULL
           ,@DskSecRd       decimal(10,4) = NULL
           ,@DskSecWrt      decimal(10,4) = NULL
           ,@ProcQueLn      int = NULL)
AS
    SET NOCOUNT ON
    
    DECLARE @ServerOut table( ServerID int);

    INSERT INTO [Analysis].[ServerStats]
           ([ServerNm]
           ,[PerfDate]
           ,[PctProc]
           ,[Memory]
           ,[PgFilUse]
           ,[DskSecRd]
           ,[DskSecWrt]
           ,[ProcQueLn])
    OUTPUT INSERTED.ServerID INTO @ServerOut
        VALUES
           (@ServerNm
           ,@PerfDate
           ,@PctProc
           ,@Memory
           ,@PgFilUse
           ,@DskSecRd
           ,@DskSecWrt
           ,@ProcQueLn)

    SELECT @ServerID = ServerID FROM @ServerOut
    
    RETURN

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Analysis].[insInstanceStats]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [Analysis].[insInstanceStats]
GO
CREATE PROCEDURE [Analysis].[insInstanceStats]
           (@InstanceID     int OUTPUT
           ,@ServerID       int = NULL
           ,@ServerNm       varchar(30) = NULL
           ,@InstanceNm     varchar(30) = NULL
           ,@PerfDate       datetime = NULL
           ,@FwdRecSec      decimal(10,4) = NULL
           ,@PgSpltSec      decimal(10,4) = NULL
           ,@BufCchHit      decimal(10,4) = NULL
           ,@PgLifeExp      int = NULL
           ,@LogGrwths      int = NULL
           ,@BlkProcs       int = NULL
           ,@BatReqSec      decimal(10,4) = NULL
           ,@SQLCompSec     decimal(10,4) = NULL
           ,@SQLRcmpSec     decimal(10,4) = NULL)
AS
    SET NOCOUNT ON
    
    DECLARE @InstanceOut table( InstanceID int);

    INSERT INTO [Analysis].[InstanceStats]
           ([ServerID]
           ,[ServerNm]
           ,[InstanceNm]
           ,[PerfDate]
           ,[FwdRecSec]
           ,[PgSpltSec]
           ,[BufCchHit]
           ,[PgLifeExp]
           ,[LogGrwths]
           ,[BlkProcs]
           ,[BatReqSec]
           ,[SQLCompSec]
           ,[SQLRcmpSec])
    OUTPUT INSERTED.InstanceID INTO @InstanceOut
    VALUES
           (@ServerID
           ,@ServerNm
           ,@InstanceNm
           ,@PerfDate
           ,@FwdRecSec
           ,@PgSpltSec
           ,@BufCchHit
           ,@PgLifeExp
           ,@LogGrwths
           ,@BlkProcs
           ,@BatReqSec
           ,@SQLCompSec
           ,@SQLRcmpSec)

    SELECT @InstanceID = InstanceID FROM @InstanceOut
    
    RETURN

GO

Now the PowerShell. The target server may be a named instance, so I need to split the instance name on the backslash character, like I showed you yesterday. In this case, though, I'll use the name "MSSQLSERVER" for the instance name because that's the name of the default instance everywhere in SQL Server Land except for the performance counters. Once I've got my instance name ($instnm) and machine name ($mach) then I'm ready to pull in the log file from perfmon. The file is a binary file, but PowerShell 2.0 introduced a new cmdlet called Import-Counter which reads that file format nicely. I'll use that cmdlet and assign the results to the $logcntr variable.

$srv = $inst.Split("\")
if ($srv.Count -eq 1) {
	$instnm = 'MSSQLSERVER'
	}
	else {
	$instnm = $srv[1]
	}
$mach = $srv[0]

$logcntr = import-counter $logfil

We have to iterate through the rows in the counter object, and each row has a property called Timestamp, which is the date and time the counter was taken, and a collection called CounterSamples. This contains all the counters gathered at that time. We'll loop through that looking at the Path property to find the counter and match it with the variable we want to use to store the value for the counter. Once we find it (using wildcards, so we don't have to worry about service names) we assign the CookedValue property to the variable we're using for that counter.

foreach ($cntr in $logcntr) {
	$dtm = $cntr.Timestamp
	$clist = $cntr.CounterSamples
	foreach ($sample in $clist) {
		switch -wildcard ($sample.Path) {
			'*% Processor Time*' { $ppt = $sample.CookedValue }
			'*Available MBytes*' { $mab = $sample.CookedValue }
			'*Paging File*' { $pfu = $sample.CookedValue }
			'*Avg. Disk sec/Read*' { $drs = $sample.CookedValue }
			'*Avg. Disk sec/Write*' { $dws = $sample.CookedValue }
			'*Processor Queue Length*' { $pql = $sample.CookedValue }
			'*Forwarded Records/sec*' { $frs = $sample.CookedValue }
			'*Page Splits/sec*' { $pss = $sample.CookedValue }
			'*Buffer cache hit ratio*' { $bch = $sample.CookedValue }
			'*Page life expectancy*' { $ple = $sample.CookedValue }
			'*Log Growths*' { $log = $sample.CookedValue }
			'*Processes blocked*' { $blk = $sample.CookedValue }
			'*Batch Requests/sec*' { $brs = $sample.CookedValue }
			'*SQL Compilations/sec*' { $cmp = $sample.CookedValue }
			'*SQL Re-Compilations/sec*' { $rcm = $sample.CookedValue }
			 }
		}

Once we've got the counters correctly loaded into variables we can use the stored procedures created above to load the data into the analysis database. Note that I'm using the Invoke-SQLCMD cmdlet so you'll have to make sure that's been loaded. Also note that I'm returning the identity value from the ServerStats insert into the InstanceStats table so the two can be properly joined in your analysis queries. This design allows me to monitor multiple instances on the same machine and have the counters all properly synchronized when necessary. The @ServerID parameter in the insServerStats procedure is an output parameter, and by assigning the output from the first invoke-sqlcmd cmdlet to a variable called $res (for 'results'), I can get the value by examining the ServerID property of the $res variable.

	#Send the machine counters to our database
	$q = "declare @ServerID int; exec [Analysis].[insServerStats]"
	$q = $q + " @ServerID OUTPUT"
	$q = $q + ", @ServerNm='" + [string]$mach + "'"
	$q = $q + ", @PerfDate='" + [string]$dtm + "'"
	$q = $q + ", @PctProc=" + [string]$ppt
	$q = $q + ", @Memory=" + [string]$mab
	$q = $q + ", @PgFilUse=" + [string]$pfu
	$q = $q + ", @DskSecRd=" + [string]$drs
	$q = $q + ", @DskSecWrt=" + [string]$dws
	$q = $q + ", @ProcQueLn=" + [string]$pql
	$q = $q + "; select @ServerID as ServerID"
	#write-output $q
	$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
	$SrvID = $res.ServerID
			
	#Send the instance counters to the database
	$q = "declare @InstanceID int; exec [Analysis].[insInstanceStats]"
	$q = $q + " @InstanceID OUTPUT"
	$q = $q + ", @ServerID=" + [string]$SrvID
	$q = $q + ", @ServerNm='" + [string]$mach + "'"
	$q = $q + ", @InstanceNm='" + [string]$instnm + "'"
	$q = $q + ", @PerfDate='" + [string]$dtm + "'"
	$q = $q + ", @FwdRecSec=" + [string]$frs
	$q = $q + ", @PgSpltSec=" + [string]$pss
	$q = $q + ", @BufCchHit=" + [string]$bch
	$q = $q + ", @PgLifeExp=" + [string]$ple
	$q = $q + ", @LogGrwths=" + [string]$log
	$q = $q + ", @BlkProcs=" + [string]$blk
	$q = $q + ", @BatReqSec=" + [string]$brs
	$q = $q + ", @SQLCompSec=" + [string]$cmp
	$q = $q + ", @SQLRcmpSec=" + [string]$rcm
	$q = $q + "; select @InstanceID as InstanceID"
	#write-output $q
	$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
	}

After running this script for each of your perfmon log files you'll be able to query that data, do aggregate analysis, and more easily find where you're having performance problems, as well as get to know what your baseline is.

Allen