Illness

It is a quiet Tuesday morning at work, you just got a fresh cup of coffee and are sitting down to catch up on email when a developer shows up at your cube and says, "the development server that contains the muffler bearing sales data is not working". The immediate response is typically "what error are you getting". This is usually followed by the developer returning to their desk to send you the pertinent information while you start augering through logs and perfmon counters. This can take quite a bit more time, especially if you have five SQL instances on one physical server to check for issues.

Diagnosis

Figuring out the problem is usually a result of previous encounters with the server or users on it. In my experience, a developer usually is editing data inside a transaction and forgets that there is a COMMIT required and wanders off to post messages on someone's social networking site. If the issue isn't a blocking transaction, where do you look next? The event log, perfmon, etc are all tools we might use. When you have several instances, tracking down the issue can take longer than it should.

Treatment in 11 steps

Enter Dr. Powershell, with its ability to read logs on the server and SQL instances, query each SQL Server and aggregate Perfmon information all into one easy to use script. This article will walk through a PowerShell script I have been modifying for quite some time that really helps pinpoint issues, especially when you have multiple SQL Instances to check.

1. Load the SMO library, declare some variables and setup the two arrays of performance counters we are going to collect. Notice the use of a token in the SQL Counter array, which is used to support default and named instances on the same server. The here string($sysprocessQuery) is the SQL command that will be called, which returns any spids being blocked from that instance.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[int]$numOfPerfmonCollections = 2
[int]$intervalOfCollections = 2
$sqlCounters = $null # placeholder for counter values
## Counters to collect ##
$physicalcounters = ("\Memory\Available MBytes") `
 ,("\PhysicalDisk(_Total)\Avg. Disk sec/Read")`
 ,("\PhysicalDisk(_Total)\Avg. Disk sec/Write") `
 ,("\Processor(_Total)\% Processor Time") 
## SQL Counter template ([instancekey] is replaced with instance name in Get-SQLCounters Function)
$sqlCounterTemplate = ("\[instancekey]:SQL Statistics\Batch Requests/sec") `
 ,("\[instancekey]:Access Methods\Workfiles Created/sec")`
 ,("\[instancekey]:Buffer Manager\Page life expectancy")
[string]$sysprocessQuery = @"
SELECT spid,blocked,open_tran,waittime,lastwaittype,waitresource,dbid
,cpu,physical_io,memusage,hostname 
FROM master..sysprocesses 
WHERE blocked != 0  
order by spid
"@

2. Create two functions to help out. The first function, Get-SQLCounters takes the SQL Counter array from above, and creates a new array with the token replaced with the SQL instance (parameter $SQLServerToMonitor). The Invoke-Sqlcmd3 is something I saw or read from a Chad Miller presentation last year. I keep it in my profile for use all the time, it takes a SQL query and stores the results in a data table.

# Creates an new array with a SQLInstance specific list of counters 
# "\[instancekey]:Buffer Manager\Page life expectancy"
function Get-SQLCounters{
 param([string] $SQLServerToMonitor, $counters)
 $counterArray = @() # holds the instance specific counters array to pass into get-counter
 # Generate a counter path friendly name (SQLServer (default instance) or MSSQL$InstanceName)
 [int]$instPos = $SQLServerToMonitor.IndexOf("\");
 if($instPos -gt 0){ 
 $instPos += 1;
 $instancekey = "MSSQL$" + $SQLServerToMonitor.Substring($instPos,($SQLServerToMonitor.Length - $instPos))
 } else { # Default Instance
 $instancekey = "SQLServer"
 }
 ## Rebuilds Counter array with SQL Specific counters
 foreach($cnter in $counters) {
 $counterArray += $cnter.Replace("[instancekey]",$instancekey)
 }
 return $counterArray;
}
## Based on a Chad Miller script
function Invoke-Sqlcmd3
{
 param(
 [string]$ServerInstance,
 [string]$Query
 )
 $QueryTimeout=30
 $conn=new-object System.Data.SqlClient.SQLConnection
 $constring = "Server=" + $ServerInstance + ";Integrated Security=True"
 $conn.ConnectionString=$constring
 $conn.Open()
 if($conn){
 $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
 $cmd.CommandTimeout=$QueryTimeout
 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 [void]$da.fill($ds)
 $conn.Close()
 $ds.Tables[0]
 }
}

3. This is the only interaction with the script you have at run time, tell it which physical server to check and how many hours to look back in the logs for.

$server = Read-Host -Prompt "Specify a server"
# Get a DateTime to use for filtering
[int]$hourThreshold = Read-Host -Prompt "Number of Hours to Check"
[datetime] $DatetoCheck = (Get-Date).AddHours(-1 * $hourThreshold)

4. The first step in our health check is a simple "are you alive?" ping.

if(Test-Connection -ComputerName $server)
{
 Write-Host "$server pinged successfully"
} else {
 Write-Host "$server could not be pinged!"
 break;
}

5. Next, get all the SQL Services on the server and push them to Out-Gridview for easy inspection.

#Grab SQL Services
$SQLServices = Get-WmiObject -ComputerName $server win32_service | 
 Where-Object {$_.name -like "*SQL*" } |
 Select-Object Name,StartMode,State,Status 
 if($SQLServices.Count -gt 0) {
 $SQLServices | Out-GridView -Title "$server SQL Services Information"
 }

6. Grab physical server performance counters (stored in $physicalcounters) and push them to the custom array $sqlCounters for viewing later.

# Grab OS counters and add to SQL Counter array for single grid output.
 Write-Host "Reading OS Perf Counters...."
 try{
 $sqlCounters = Get-Counter -ComputerName $server -Counter $physicalcounters `
 -MaxSamples $numOfPerfmonCollections -SampleInterval $intervalOfCollections
 } catch {
 Write-Host "Problem Reading Perf Counters" + $Error
 }

7. Looping through every SQL instance on the machine that is running, get the proper $sqlServerName from the service and create an SMO SQL object. The first check queries the SQL instance to get any blocked processes. Given this tends to be a show stopper, the results are sent to Out-GridView right away for each instance where there is a blocked process.

Foreach($sqlService in $SQLServices | 
 Where-Object{$_.name -like "MSSQL$*" -or $_.name -eq "MSSQLSERVER"} |
 Where-Object{$_.State -eq "Running" } )
{
 [string]$sqlServerName = $sqlService.Name
 $sqlServerName = $sqlServerName.Replace("MSSQL$","$server\")
 $sqlServerName = $sqlServerName.Replace("MSSQLSERVER","$server")
 Write-host "Checking $sqlServerName"
 $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlServerName 
 # Grab any blocking processes
 try{
 $tbl = Invoke-Sqlcmd3 -Query $sysprocessQuery -ServerInstance $sqlServerName |
 Where-Object {$_.blocked -ne "0"} | 
 Out-GridView -Title "$sqlServerName Blocked Processes"
 }
 catch{
 Write-Host "Problem Reading SysProcesses" + $Error
 }

8. The SQL Server error log is then queried for errors in the number of hours specified in $hourThreshold.

 # Write-Host "Reading SQL Log for $sqlServerName"
 try{
 $sqlServer.ReadErrorLog() | Where{$_.LogDate -is [datetime] } | 
 Where-Object{$_.LogDate -gt $DatetoCheck } | 
 Where-Object{$_.Text -like "*Error*" -or $_.Text -like "*Fail*"} |
 Select-Object LogDate,Text |
 Out-GridView -Title "$sqlServerName Log Errors"
 } catch {
 Write-Host "Error Reading $sqlServer.Name"
 }

9. The last operation that is run for each SQL Instance, is collecting SQL specific performance counters. The array of SQL performance counters template creates a new SQL instance specific list of counters. This new list of counters for the instance are then passed to Get-Counter and the results stored in the SQL counter array($sqlCounters).

 # Get SQL Instance specific counter array and build up array $sqlCounters to store for all instances
 try{
 $sqlInstanceCounters = Get-SQLCounters -SQLServerToMonitor $sqlServerName -counters $sqlCounterTemplate
 } catch {
 Write-Host "Error Building SQL Counter Template $_"
 }
 # Append the SQL counter values to the physical server counters
 try{
 $sqlCounters += Get-Counter -ComputerName $server -Counter $sqlInstanceCounters `
 -MaxSamples $numOfPerfmonCollections -SampleInterval $intervalOfCollections 
 } catch {
 Write-Host "Error getting SQL Counters $_"
 } 
} # end of SQL instances loop

10. Now that every SQL instance has been checked over, push the counter values to the screen. While reviewing those counters, the server event logs (System and Application) will be read and values returned to the screen if any conditions are found.

# Push counters to grid 
 $sqlCounters | ForEach-Object{ $_.CounterSamples | Select-Object Path, CookedValue } |
 Out-GridView -Title "$sqlServer Perfmon Counters"

11. While you are reviewing the performance counters, the query continues on and reads the System and Application event logs on the server for issues. This query tends to be a bit slower but the information in the logs can be critical to catching a drive before it fails, or some other issue that is not immediately apparent.

 try{
 Write-Host "Reading Event Logs..."
 # Check Server System and Application Event Logs
 $systemLog = Get-EventLog -ComputerName $server `
 -EntryType "Error" -LogName "System" -After $DatetoCheck |
 Select-Object TimeGenerated,Source,Message 
 $appLog = Get-EventLog -ComputerName $server `
 -EntryType "Error" -LogName "Application" -After $DatetoCheck |
 Select-Object TimeGenerated,Source,Message 
 if($systemLog.Count -gt 0) {$serverLogs += $systemLog} 
 if($appLog.Count -gt 0) {$serverLogs += $appLog} 
 if($serverLogs.Count -gt 0) { $serverLogs | Out-GridView -Title "$server Event Logs" }

} catch {
 Write-Host "Problem Reading Server Event Logs:" + $Error
}

Cure

At this point, hopefully you can utilize this in your environments for quick troubleshooting by checking logs, blocked processes and perfmon counter values. The commandlets utilized in this script are useful in many other scenarios as well. Many of them I use in active monitoring scripts each day to check critical servers for issues. There are opportunities for enhancing this script by checking for disk space utilization and doing the SQL instance collections utilizing a job in PowerShell so it is asynchronous. Please note any other ideas that you might have in the forums so all can benefit.

The Powershell General's warning

No, I don't think Powershell will cause cancer, but be cautious when using this script. I have only tried this on a few of my machines, all running Powershell 2.0 against SQL 2000 and SQL 2008 instances. Please post questions in the forum or utilize the great help out there, including the #PowerShell hash tag on Twitter.