As good little DBAs, we should be gathering baseline statistics for our database servers. These stats can give us insight into what’s changed when performance is suddenly in the toilet. They can also provide a basis for determining what systems can be consolidated, downsized, or virtualized, which has become quite the topic of discussion with the announcement of the SQL 2012 licensing structure.

Obviously, we have a few choices as to what tool we use to gather our baselines. SQL Server has built-in DMVs that allow us to query the data through T-SQL. There are third-party tools aplenty. And there’s good old Perfmon. I’m an old-fashioned girl at heart, so when it came time to choose a tool, I opted for Performance Monitor, aka Perfmon.

But I don't want to point and click...

And therein lay the problem. We have quite a few servers to gather stats on. And (whining and stamping my foot) I don’t want to set up Perfmon counters on all those servers. So, like the lazy DBA I am, I set about to find a shortcut. My first thought, of course, was Powershell. I already use Powershell to gather other information on all my servers, how hard could it be to have Powershell set up Perfmon counters on each of those servers, too. Then, when I’m ready to analyze the logs, I can go to whatever server I’m interested in and there’s my baseline, just waiting for me, like a present on Christmas morning. Or something like that. So, again like the lazy DBA I am, I went online looking for someone else’s code.

I found a few candidate scripts, but they weren’t quite what I was looking for (meaning, I didn’t understand the code). Then I found one that might work for me. It was straightforward, I understood what it was doing, a few minor changes and I’d be good to go. Then I scrolled down to the comments section, and someone asked “why didn’t you just use logman?” Logman? I’d never heard of it. So I googled it. It was like an epiphany. All these years I’d been deploying Perfmon through the gui. Point and click. Point and click. Over and over. And we all know how much I adore those repetitive tasks. But logman.exe, so simple, so… scriptable.

So forget googling “powershell perfmon”, I started googling “powershell logman”, which brought me to this blog by Rasmus Sjoerslev. Not only did he provide a very good starting point for a script I could use, he also gave me an idea that, for some reason, hadn’t occurred to me yet, why not run all the Perfmon counters from a single server. That way, when I have to run them or collect them for analysis, everything’s right there. Brilliant.

The original script used two text files as input. The first contained a list of servers to monitor and the second held the Perfmon counters. Since I already have my servers listed in a table in my Inventory database, I changed the code to read from there, rather than the servers.txt file. I also modified the counters.txt file to include the counters I was interested in. Here’s a look at the counters.txt file I ended up with.

\LogicalDisk(*)\Avg. Disk sec/Read

\LogicalDisk(*)\Avg. Disk sec/Write

\LogicalDisk(*)\Disk Reads/sec

\LogicalDisk(*)\Disk Writes/sec

\Memory\Available MBytes

\Paging File(_Total)\% Usage

\Processor(_Total)\% Processor Time

\:Access Methods\Full Scans/sec

\:Access Methods\Index Searches/sec

\:Access Methods\Page Splits/sec

\:Buffer Manager\Page life expectancy

\:Databases(*)\Transactions/sec

\:General Statistics\User Connections

\:Memory Manager\Memory Grants Pending

\:Memory Manager\Target Server Memory (KB)

\:Memory Manager\Total Server Memory (KB)

\System\Processor Queue Length

Notice the and placeholders. For every SQL instance in my Servers table, the tag will be replaced with the hostname of the instance, and the tag will be replaced with the instance name, as appropriate. You can add or remove counters as needed.

The powershell script I ended up with is very straightforward. As with my other inventory scripts, I begin with 4 parameters. These tell the script where to find my SS_Inventory database, depending on what environment I’m running this in. I also go ahead and load the assemblies for connecting to the SQL database.

 # Based on script from http://it-experts.dk/blogs/rsj/archive/2009/10/19/performance-data-collection-made-easy-logman-and-powershell.aspx
param(
[string]$ssi_instance=$null,
[string]$ssi_userid=$null,
[string]$ssi_passwd=$null,
[string]$ssi_env=$null
     )
$ssi_database = "SS_Inventory"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null 

Next I read in the counters.txt file into a $COUNTERS variable.

$COUNTERS = Get-Content $pwd\counters.txt

The next thing I need to do is connect to my SS_Inventory database and get the list of servers I want to monitor.

$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()

Once I’ve got my servers, I now need to loop through them and do a little formatting, depending on whether this is a named or default instance. If the server name is not the same as the instance name, i.e. MyServer vs MyServer\MyInstance, then I’m looking at a named instance. In that case, I want to name the counter collection MyServer_MyInstance, and I need to change the tag in the counters.txt file to “MSSQL$MyInstance”. If the instance is a default instance, then the counter collection will just contain the server name, and will be replaced with “SQLServer”.

while($reader.Read()) {
$svr = $reader['ServerName']
$svrshort = $reader['ServerNameShort']
$inst = $reader['InstanceName']
$instshort = $reader['InstanceNameShort']
if ($svrshort -ne $instshort) {
$srvr = $instshort.Split("\")
$server = $srvr[0]
$instance = $srvr[1]
$cntrname = "$svrshort"+"_"+"$instance"
$sqlinstance="MSSQL$"+"$instance"
}
else {
$cntrname = $svrshort
$sqlinstance = "SQLServer"
}

Still inside my loop, I read through the counters in my $COUNTERS variable, make the necessary replacements for the and tags, and write out the results to a temporary file. Then it’s time to execute the logman command. You can use logman /? to find more information on all the available parameters, but basically here I’m setting up counter collections to pull data every 10 minutes for a week and write the output to a csv file in the d:\Perflogs directory. The last thing I do is delete the temporary counters file.

$COUNTERS |% {$_ -replace "<HOSTNAME>", "\\$svrshort"} | % {$_ -replace "<INSTANCE>", $sqlinstance} | Set-Content $pwd\$cntrname.txt
$strCMD = "C:\Windows\System32\logman.exe create counter $cntrname -si 00:10:00 -cf $pwd\$cntrname.txt -f csv -u MyDomain\MyLogin MyAwesomePassword  -v mmddhhmm -o D:\PerfLogs\$cntrname -rf 168:00:00"
Invoke-Expression $strCMD
Remove-Item $pwd\$cntrname.txt
} 

Now when I need to get a baseline for any of my SQL Server instances, I can open Perfmon on this server and start the collection. In a week I can come back and collect the file. Easy. And no temper tantrums.