Automation for DBAs – PerfMon Presage

I”m a pretty big fan of PerfMon. Most seasoned DBAs are adept with ready it and scheduling out collector sets in order to grab graphically aesthetic globs (GAGs) of information. I’m also a pretty big fan of Alerts. Most of you (if not all) should be familiar with how and when to setup alerts on SQL Server, if not then review this article from Brent Ozar and get alerting!

I’m not a fan of alerts firing off and having to rush to a computer in order to record some GAGs. The last time this happened I had the choice of either ignoring the alert and finishing up my business, or attempting to fondle my phone with very unsanitary hands and hope that one of the hack RDP apps on the marketplaces would actually connect allowing me to gumply fumble around in an attempt to get PerfMon going. Needless to say, I finished what I was doing, washed my hands thoroughly and ran back to my desk…naturally…the alert was cleared and the system was running like normal. Damn…just missed it.

Seriously? A SQL Alert...NOW?!?

Seriously? A SQL Alert…NOW?!?

This situation prompted me to investigate a way to automate PerfMon collection without running it non stop. This specific task is focused on responding to an alert and having information available that is specific to that alter, for example, if I get an insufficient resources alert, then maybe I’d like to sit down and review the resources on the system for troubleshooting.

The Lab

This one is pure powershell and SQL Server with some xp_cmdshell. I know already some of you are gasping AMG I can’t believe he is using xp_cmdshell…the HORROR…the NUB SECURITY HOLE…well if that is what some of you are saying…shut up. I’d be very surprised if enabling xp_cmdshell isn’t the least of your worries when it comes to security (really have you ever sat through a security audit going over your servers?), and besides, we are only enabling it long enough to execute our batch scripts then disabling it 😉

You...telling me about security -_-

You…telling me about security -_-

First, I tried to hack this out using Windows Batch Scripting. Why you ask? I don’t have a good answer, actually, I don’t have an answer. I ended up going the route of powershell when I saw that I had way too many goto labels. Aaron Bertrand’s article helped out as I ran into the same problem he did with the multiple counters. Here is the finished code

$DateStamp = Get-Date -UFormat "%Y-%m-%d@%H-%M-%S"
$MachineName = "SANDBOX-PUB1"
$CounterSet = @(
"\Memory\Available MBytes",
"\Memory\Pool Nonpaged Bytes",
"\Memory\Pool Paged Bytes"

Get-Counter -Counter $CounterSet -MaxSamples 10 -sampleinterval 1 | Export-Counter -Force -Path C:\PerfMon\baseline-$DateStamp.blg

One thing I’ve done here is added the timestamp to the .blg file. This is pretty important since an existing .blg will cause PerfMon to complain.

Ok, so now you’ve created the powershell script, next you’ll need to whip up the tsql that will allow you to execute the batch file. For this we will need to enable xp_cmdshell. This is not as scary as it sounds, its a quick enable, do the job and then GTFO.

EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
-- To update the currently configured value for this feature.

EXEC master..xp_CMDShell 'powershell.exe -file "C:\PerfMon\RunPerfMon.ps1" -nologo'

-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 0;
-- To update the currently configured value for this feature.

The code is completed. Now the fun begins…

Testing it Out

First make sure your powershell script works. Run it a few times using Powershell ISE and crack open the blg to make sure things are being collected. This shouldn’t be too much of a problem (unless you don’t have local admin to the machine). Next test it out in SQL (I used management studio), and if it works then you are good to go!

Now that you have a way to capture the performance statistics, you can easily hook it up to an alert, so that way when it fires off, you can collect the goods. You’ll need to create a job that will run the TSQL code (or stored procedure if you like things tidy).

Create the job

Create the job

Once you’ve created and tested the job, you can go into the alert and [insert steps and processes here]

Configure the alert

Configure the alert

You can test your alert by using RAISERROR and your sev level

RAISERROR ('Testing email alert and job execution', 16, 1) WITH LOG


Leave a Reply

Your email address will not be published. Required fields are marked *