WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Monday, June 13, 2011

Trust but Verify (changing the Page_Verify to checksum)

Just a quick one, if you manage lots of servers and have noticed that the PAGE_VERIFY flag is set to NONE or TORN PAGE DETECTION and want to switch it to the generally accepted proper setting "CHECKSUM".  Here is a script to do it.  Just add the names of your SQL Servers to the $sqlInstanceList and hit the button. (Test it first, it's just something I whipped up today and tested quickly on a few servers).  Also, be sure you understand what you are changing, it makes sense in my environment, but may not in yours. 

$sqlInstanceList = ("SQLServerName","SQLServerName2")
    foreach($sqlInstance in $sqlInstanceList)
        $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance
        if($sqlServer.VersionMajor -ge 10){
            foreach($db in $sqlServer.Databases | Where-Object{$_.PageVerify -ne "Checksum"}){
                $db.PageVerify = "Checksum";

Thursday, June 2, 2011

Gathering physical and SQL counters with Powershell

Lately, I have been doing lots of load testing bursts with a trace and subsequent perfmon collections.  Since I like to keep things easy, I wrote a Powershell script to aid in the collection of data.  I push the files to a share so the application teams can grab them and look for themselves.  This is nice because it allows lesser experienced folks to use it and hand over results with a test.  The counters are some I picked based on a recent load test and are not a definite nor all encompassing list of counters.

$serverToMonitor = "computerName"
$sqlServerToMonitor = "SQLInstanceforcomputerName"
$file = "C:\PerfMonResults\PerfmonResults.csv"
$samplesToTake = 150
$sampleInterval = 5

$counters= ("\[instancekey]:Access Methods\Worktables Created/sec") `
,("\[instancekey]:Access Methods\Workfiles Created/sec")`
,("\[instancekey]:Buffer Manager\Page life expectancy") `
,("\Processor(_Total)\% Processor Time") `
,("\[instancekey]:SQL Statistics\Batch Requests/sec") `
,("\PhysicalDisk(*)\Avg. Disk sec/Read") `
,("\PhysicalDisk(*)\Avg. Disk sec/Write")

## Takes a counter array and replaces a token
[instancekey] with SQL Specific Name
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;

## Build SQLServer specific counter array
$counterArray = Get-SQLCounters -SQLServerToMonitor $sqlServerToMonitor -counters $counters

Get-Counter -ComputerName $serverToMonitor -Counter $counterArray -MaxSamples -$samplesToTake -SampleInterval $sampleInterval |
    %{$_.CounterSamples | Select-Object -Property Path, CookedValue, Timestamp; } |
    Export-Csv -Path $file -NoTypeInformation

#If Windows 7 - you can push to perfmon file and open in perfmon after collection is complete (read up on export-counter)
Invoke-Item $file