A little PowerShell syntax tip : dealing with SQL Server named instances

AaronBertrand — Tue, 03 May 2011 22:32:00 GMT

This afternoon I was working on a little PowerShell script to collect some PerfMon counters. I borrowed some code from myself, and had counters represented in strings like the following (this is a greatly simplified version):

Get-Counter -Counter "\SQLServer:Wait Statistics(Average wait time (ms))\Lock waits"

The above code is for a default instance, but in this case I was dealing with a named instance (SQL2008R2). So I simply replaced "SQLServer:" with "MSSQL$SQL2008R2":

Get-Counter -Counter "\MSSQL$SQL2008R2:Wait Statistics(Average wait time (ms))\Lock waits"

When I ran this code, I received the following error:

Get-Counter : The specified object was not found on the computer.
At line:1 char:12
+ Get-Counter <<<<  -Counter "\MSSQL$SQL2008R2:Wait Statistics(*)\Lock waits"
    + CategoryInfo          : InvalidResult: (:) [Get-Counter], Exception
    + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

The PowerShell gurus will spot the problem instantly, but play along, okay? I verified the format of the object, instance and counter name using Performance Monitor, going so far as validating exact case:

I even copied the properties to Notepad to make sure I wasn't being dumb and transcribing incorrectly:

I quickly searched for examples of folks using PowerShell to pull performance counter data from named instances of SQL Server, but either there aren't many out there, or my Google-fu was weak today.

I am not sure exactly why, but I looked at the Get-Counter line again, and the $ sign jumped out at me. Time is money; not sure if there is a correlation there. Anyway, when I properly escaped the dollar sign with a backward apostrophe (grave accent), it worked just fine, even though it looks funny:

Get-Counter -Counter "\MSSQL`$SQL2008R2:Wait Statistics(Average wait time (ms))\Lock waits"
#---------------------------^

Of course, looking back, it's blindingly obvious. I just won't get those 15 minutes back, and hope to save someone else the same 15 minutes.

[The error message does not seem to indicate that it tried to substitute the variable with anything, or that the counter wasn't found because the name wasn't complete. So I spent most of my time chasing other avenues. I'm going to leave that rabbit hole for another day, as I spent more time writing this post than solving the original issue.]

How I use PowerShell to collect Performance Counter data

AaronBertrand — Mon, 31 Jan 2011 12:31:00 GMT

In a current project, I need to collect performance counters from a set of virtual machines that are performing different tasks and running a variety of workloads. In a similar project last year, I used LogMan to collect performance data. This time I decided to try PowerShell because, well, all the kids are doing it, I felt a little passé, and a lot of the other tasks in this project (such as building out VMs and running workloads) were already being accomplished via PowerShell. And after all, I did include "to become better at PowerShell" as one of my goals for this year.

I searched around and quickly found this post from Rhys Cambell (blog | twitter). He was collecting a set of counter samples every n seconds and outputting each sample to its own CSV file. I thought this was a very useful starting point to where I wanted to be. But, I had some troubles getting the script to work correctly at first: if I combined -ComputerName and multiple counters, I would receive this error:

Get-Counter : Unable to connect to the specified computer or the computer is offline.
...
+ CategoryInfo          : InvalidResult: (:) [Get-Counter], Exception
+ FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

It would work fine if I either omitted -ComputerName *OR* only used one counter. So not sure exactly what the problem was there, as it certainly wasn't a connectivity issue. But a workaround I stumbled upon several hours later was to use the @(counter, counter) collection notation instead of the += syntax Rhys had presented. So instead of:

$listOfMetrics += ("\processor(0)\% processor time");            
$listOfMetrics += ("\processor(1)\% processor time");
...

I used something more like this:

$listOfMetrics = @(            
    "\processor(0)\% processor time",            
    "\processor(1)\% processor time",            
    ...            
);

Because I wanted to collect different sets of metrics from different computers (some are SQL Servers, some are not), I made some slight adjustments to Rhys' script. I made it parameterized and, based on server role, I would define a different set of counters. This would allow me to very easily call the same script with slightly different arguments for each of the servers I wanted to monitor. My script (CollectPerf.ps1) looks like this - for brevity, I'm not going to list out all of the roles and all of the counters I'm actually collecting, but it should give you an idea of how to collect different counters for different computers or roles:

param([string]$server, [string]$role, [string]$test, [int]$delay, [int]$count, [string]$path)            
            
function CollectPerf {            
  param(            
    [string]$server,            
    [string]$role,            
    [string]$test,            
    [int]$delay,            
    [int]$count,            
    [string]$path            
  )            
            
    if ($role -eq "app server")            
    {            
      $counters = @("\Processor(_Total)\% Processor Time",            
        "\System\Processor Queue Length"            
        # -- other counters            
      )            
    }            
            
    if ($role -eq "db server")            
    {            
      $counters = @("\PhysicalDisk(_Total)\Avg. Disk sec/Read",            
        "\SQLServer:SQL Statistics\Batch Requests/sec"            
        # -- other counters            
      )            
    }            
            
    # other roles...            
            
    $sequence = 1;            
            
    $metrics = Get-Counter -ComputerName $server -Counter $counters -SampleInterval $delay -MaxSamples $count            
            
    foreach($metric in $metrics)            
    {            
      $obj = $metric.CounterSamples | Select-Object -Property Path, CookedValue, Timestamp;            
      # add these columns as data            
      $obj | Add-Member -MemberType NoteProperty -Name Sequence -Value $sequence -Force;            
      $obj | Add-Member -MemberType NoteProperty -Name LoadTest -Value $test     -Force;            
      $obj | Add-Member -MemberType NoteProperty -Name Computer -Value $server   -Force;            
      # export with unique file name            
      $obj | Export-Csv -Path "$path$server.$test.$sequence.csv" -NoTypeInformation;            
      $sequence += 1;            
    }            
}            
CollectPerf -server $server -role $role -test $test -delay $delay -count $count -path $path

Now, the problem with the script approach is that if I wanted to simultaneously collect metrics from different servers involved in the workload, I would expect to be able to do this (let's pretend there are only two arguments, again for brevity):

C:\path\CollectPerf.ps1 "server1" "app server"            
C:\path\CollectPerf.ps1 "server2" "database server"

However this runs the set of counters for server1, and doesn't start collecting for server2 until server1 is finished. Because I needed these to run asynchronously, and I didn't want to open dozens of individual PowerShell windows, I realized I should use a PowerShell job. So I could instead do this:

StartJob -ScriptBlock { C:\path\CollectPerf.ps1 "server1" "app server" }             
StartJob -ScriptBlock { C:\path\CollectPerf.ps1 "server2" "database server" }

Which worked fine if I hard-coded the arguments. But another strange thing happened with this syntax (admittedly, it's not "strange" - just strange to me) when I tried to parameterize the server and role. If I used any of these conventions (and several other attempts I've omitted for their silliness):

Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 $server $role };             
Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 $server $role } -ArgumentList $server $role;             
Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 $server $role } -ArgumentList $server, $role;             
Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 $args[0] $args[1] } -ArgumentList $server $role;             
Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 $args[0] $args[1] } -ArgumentList $server, $role;

In each case the job would start fine (or return an error about params), but even though it would run, it was clear from the output that the parameters were not passed in correctly (they were treated as empty strings). So instead I tried this, once again switching to @(arg, arg) notation...

Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 $args[0] $args[1] } -ArgumentList @($server, $role);

...and suddenly all was well with the world. (I talked about this in a recent blog post, entitled, "PowerShell, Start-Job, -ScriptBlock = sad panda face.") So, if you're trying to use Start-Job with -ScriptBlock, keep this convention in mind. Once I had the syntax down, I was able to collect metrics from dozens of servers using one command line call, by creating a second PowerShell script (CollectPerfAcrossServers.ps1) with this code and a command for each of the servers I was interested in:

param([string]$test, [int]$delay, [int]$count, [string]$path)             
function CollectPerfAcrossServers {            
  param(            
      [string]$test,            
      [int]$delay,            
      [int]$count,             
      [string]$path            
  )            
            
  Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 "VM1" "db server"  $args[0] $args[1] $args[2] $args[3] } -ArgumentList @($test, $delay, $count, $path);            
  Start-Job -ScriptBlock { C:\path\CollectPerf.ps1 "VM2" "app server" $args[0] $args[1] $args[2] $args[3] } -ArgumentList @($test, $delay, $count, $path);            
            
  # ...more servers, more servers, more servers...            
  # arguably, I should probably be pulling the list of servers and roles from a config file            
}            
CollectPerfAcrossServers -test $test -delay $delay -count $count -path $path;

Now that I have dozens of servers configured this way, I can log onto a central monitoring server and collect all of their role-based performance counters simultaneously, every 30 seconds for an hour, with this single command line:

C:\path\CollectPerfAcrossServers.ps1 "Jan.31.Baseline" 30 120 "C:\csv\"

And when I want to run a different test, all I have to change is the first argument. Once I run this command, it fills the directory up with CSV files - it can add up quickly (one file, per server, per sample). If I have 20 servers and collect a sample every 30 seconds for an hour, that's 2,400 files. However, even if I am collecting 50 counters for a specific role, the files are quite small and very easy to consume. (Note that the files won't be dumped into the folder until the last sample is collected, so to verify it's working, start with a small number of samples.) The output is not as pretty as it could be, but it works:

"Path","CookedValue","RawValue","Timestamp","Sequence","LoadTest","Computer"
"\\vm1\\network interface(_total)\bytes received/sec","19150.7500680961","17636201111", "1/31/2011 3:43:24 AM","58","Jan.31.Baseline","VM1"
"\\vm1\\network interface(_total)\bytes sent/sec","65151.1215576817","12987234638", "1/31/2011 3:43:24 AM","58","Jan.31.Baseline","VM1"
"\\vm1\\network interface(_total)\output queue length","0","0", "1/31/2011 3:43:24 AM","58","Jan.31.Baseline","VM1"

There are a couple of things worth noting here. One is that everything is output as a string and has delimiters, so in order to perform any calculations or to get it into a table with numeric / datetime data types, you need to first strip the quotes. The computer name is redundantly included in both the Path column and the Computer column - I could have left the extra Computer column out of the CSV, but it's just as easy to parse it out of the Path after load - however the Path value is exported as lower case to the CSV file, so that needs to be accounted for as well. Another thing to watch out for is that the output for the Timestamp property obeys regional settings of the collecting machine, so if you are pulling counters on a server with mm/dd/yyyy and loading the data into a SQL Server that is using dd/mm/yyyy, you're going to need to manually convert the original string to the correct regional format.

Once I sorted those variables out, I created a table that would house all this data, and whipped up the T-SQL that would generate BULK INSERT commands for all of the files and then move everything to a table with proper datatypes and with string delimiters removed:

CREATE TABLE dbo.PerfCounters_Staging
(
    [Path]      NVARCHAR(255),
    CookedValue VARCHAR(32),
    [Timestamp] VARCHAR(32),
    [Sequence]  VARCHAR(12),
    LoadTest    VARCHAR(32),
    Computer    VARCHAR(32)
);

CREATE TABLE dbo.PerfCounters
(
    [Path]      NVARCHAR(255),
    CookedValue DECIMAL(18, 5),
    [Timestamp] DATETIME,
    [Sequence]  SMALLINT,
    LoadTest    VARCHAR(32),
    Computer    VARCHAR(32)
);

-- generate bulk insert commands

CREATE TABLE #c(x VARCHAR(2048));

SET NOCOUNT ON;
INSERT #c EXEC MASTER..xp_cmdshell 'dir /b "C:\csv\"';

SELECT 'SET NOCOUNT ON;';

SELECT 'BULK INSERT dbo.PerfCounters_Staging FROM "C:\csv\' + x + '"
    WITH (FIRSTROW = 2, ROWTERMINATOR=''\n'', FIELDTERMINATOR='','');'
    FROM #c WHERE x IS NOT NULL;

DROP TABLE #c;

When I was done with a load test, I would run the above command (with SSMS set Results to Text), focus on the results pane, press Ctrl+A, press Ctrl+C, then paste the lot to a new SSMS window, and then finally run that script. Then I would run this command to clean it up and put it into the real table:

DELETE dbo.PerfCounters
WHERE LoadTest IN 
(
    SELECT DISTINCT LoadTest
        FROM dbo.PerfCounters_Staging
        WHERE LoadTest > ''
);

INSERT dbo.PerfCounters
(
    [Path],
    CookedValue,
    [Timestamp],
    [Sequence],
    Computer,
    LoadTest
)
SELECT
    [Path]      = REPLACE(REPLACE([Path], '\\' 
                  + RTRIM(LOWER(Computer)) + '\', ''), '"', ''),
    CookedValue = REPLACE(CookedValue, '"', ''),
    [Timestamp] = REPLACE([Timestamp], '"', ''),
    [Sequence]  = REPLACE([Sequence], '"', ''),
    [Computer]  = REPLACE(Computer, '"', ''),
    [LoadTest]  = REPLACE([LoadTest], '"', '')
FROM
    dbo.PerfCounters_Staging;

Then I would empty out the folder manually (I could automate that too, but I wasn't running 500 tests a day, so it seemed to be of little benefit). Now I had the data loaded for that specific test; I just had to be careful to make my test names unique - so I would include the date and, if necessary, some other identifier in the test name. This kept tests unique but, if necessary, allowed me to re-run a specific test later, without having to go back and manually delete anything from the database.

Now I could query away. The reason I included the [Sequence] column is so that I could correlate the event order across multiple tests, without worrying about specific datetime information, rounding, etc. This let me easily compare any given metric between tests run on different days. I wrote two generic stored procedures that would allow me to compare anywhere from 2-5 different tests on a given system; one would give me the average and maximum values for a certain counter, and the other would give me the series:

CREATE PROCEDURE [dbo].[Perf_AvgMax]
   @counter  VARCHAR(255) = '\processor(_total)\% processor time',
   @computer VARCHAR(32)  = 'VM1',
   @test1    VARCHAR(32)  = 'Jan.31.Baseline',
   @test2    VARCHAR(32)  = 'Jan.31.TPCLoadA',
   @test3    VARCHAR(32)  = NULL,
   @test4    VARCHAR(32)  = NULL,
   @test5    VARCHAR(32)  = NULL
AS
BEGIN
   SET NOCOUNT ON;
   
   SELECT computer = @computer, [counter] = @counter;

   SELECT test1 = @test1, test2 = @test2, test3 = @test3, 
          test4 = @test4, test5 = @test5;

   ;WITH x AS 
   (
       SELECT CookedValue, LoadTest, Sequence
         FROM dbo.PerfCounters
         WHERE [Path] = @counter
         AND Computer = @computer
   )
   SELECT 
       AVG(a.CookedValue), 
       AVG(b.CookedValue),
       AVG(c.CookedValue),
       AVG(d.CookedValue),
       AVG(e.CookedValue),
       MAX(a.CookedValue), 
       MAX(b.CookedValue),
       MAX(c.CookedValue),
       MAX(d.CookedValue),
       MAX(e.CookedValue)      
   FROM x AS a
   INNER JOIN x AS b
   ON a.Sequence = b.Sequence
   AND a.LoadTest = @test1
   AND b.LoadTest = @test2

   LEFT OUTER JOIN x AS c
   ON b.Sequence = c.Sequence
   AND c.LoadTest = @test3
   
   LEFT OUTER JOIN x AS d
   ON c.Sequence = d.Sequence
   AND d.LoadTest = @test4

   LEFT OUTER JOIN x AS e
   ON d.Sequence = e.Sequence
   AND e.LoadTest = @test5;
END
GO

CREATE PROCEDURE [dbo].[Perf_Series]
   @counter  VARCHAR(255) = '\processor(_total)\% processor time',
   @computer VARCHAR(32)  = 'VM1',
   @test1    VARCHAR(32)  = 'Jan.31.Baseline',
   @test2    VARCHAR(32)  = 'Jan.31.TPCLoadA',
   @test3    VARCHAR(32)  = NULL,
   @test4    VARCHAR(32)  = NULL,
   @test5    VARCHAR(32)  = NULL
AS
BEGIN
   SET NOCOUNT ON;

   SELECT computer = @computer, [counter] = @counter;

   SELECT test1 = @test1, test2 = @test2, test3 = @test3, 
          test4 = @test4, test5 = @test5;
   
   ;WITH x AS 
   (
       SELECT CookedValue, LoadTest, Sequence
         FROM dbo.PerfCounters
         WHERE [Path] = @counter
         AND Computer = @computer
   )
   SELECT 
       a.[Sequence],
       test1 = a.CookedValue, 
       test2 = b.CookedValue,
       test3 = c.CookedValue,
       test4 = d.CookedValue,
       test5 = e.CookedValue
   FROM x AS a
   INNER JOIN x AS b
   ON a.Sequence = b.Sequence
   AND a.LoadTest = @test1
   AND b.LoadTest = @test2

   LEFT OUTER JOIN x AS c
   ON b.Sequence = c.Sequence
   AND c.LoadTest = @test3
   
   LEFT OUTER JOIN x AS d
   ON c.Sequence = d.Sequence
   AND d.LoadTest = @test4

   LEFT OUTER JOIN x AS e
   ON d.[Sequence] = e.[Sequence]
   AND e.LoadTest = @test5
   ORDER BY a.Sequence;
END
GO

So to compare seconds per disk write on VM1 during three different load tests, I could run this:

EXEC dbo.Perf_Series
    @computer = 'VM1',
    @counter  = '\Processor(_Total)\% Processor Time',
    @test1    = 'Jan.31.Baseline',
    @test2    = 'Jan.31.TCPLoadA',
    @test3    = 'Jan.31.TCPLoadB';

Results:

Now, I could take those results, paste them into Excel, and build nice pretty charts with very little effort (click to embiggen):

I have my 120 samples in a chart (regardless of when I ran each test) and, while I don't see any obvious trends in this specific case, I can try various metrics and combinations to see where any performance outliers may be. If nothing else I've proven to myself that my load tests are not CPU-bound in any way. For this project I am interested in the ways each specific computer's counters respond to changes in input variables (most importantly, the type of load put on the system). Of course you could write other queries around this data that compared how a certain counter responded on two servers for the same test, or several other analytic variations. What you do with the data is a different exercise altogether; I just thought I'd share how I collect the data in the first place.

There are probably 20 ways to do this, and I am by no means suggesting this is the way you should do it. I could have told PowerShell to load the data directly to the database, I could have easily generated and executed the BULK INSERT commands from PowerShell, I could have automated the reporting better using SSRS or charting tools - and there are likely 100 other ways this process could be further automated. This is just the method I settled on after a lot of trial and error, with a healthy balance of automation and manual labor. I'll get to full PoSH automation when I'm good and ready - in the meantime, I'm all ears for suggestions, but am unlikely to change much about how I'm using this process for the project at hand. :-) I find it fairly straightforward and easily extensible for adding servers, counters and tests.

For some other ways to collect performance counters with PowerShell, check out these posts by Dave Ballantyne (blog | twitter), Laerte Junior (blog | twitter), and esteemed PowerShell guru Allen White (blog | twitter):

Dave Ballantyne : My first useful powershell script – capturing performance counters

Laerte Junior : Gathering Perfmon Data with Powershell

Allen White : Performance Data Gathering

Allen White : SQL Connections Presentations (download Allen's PowerShell decks and sample code)

What I like about PowerShell so far is that it seems, unlike in T-SQL, there is no "one way" or "best way" to do something - there may be a dozen ways to skin the cat, and you can use whichever one is most intuitive or works best for you, and you can even determine what reasons help you arrive at that decision.

In a future post, I'll talk about how I run load tests in this environment using remote sessions and jobs.

Search results matching tags 'PowerShell' and 'performance counters'

A little PowerShell syntax tip : dealing with SQL Server named instances

How I use PowerShell to collect Performance Counter data