PowerShell: Get SQL Server Performance Counters

function getSqlPerformanceCounter($server=$env:computername,$sampleInterval=10,$maxSamples=1){
    $PerformanceCounterSampleSet=$result=@()
    $counters=@(
        '\Memory\Available MBytes',
        '\Memory\Pages/sec',
        '\SQLServer:Buffer Manager\Buffer cache hit ratio',
        '\SQLServer:Buffer Manager\Lazy writes/sec',
        '\SQLServer:Buffer Manager\Page life expectancy'
        )
    $sampleSet = Get-Counter -ComputerName $server -Counter $counters -SampleInterval $sampleInterval -MaxSamples $maxSamples
    #$collections.gettype()
    #IsPublic IsSerial Name                                     BaseType
    #-------- -------- ----                                     --------
    #True     False    PerformanceCounterSampleSet              System.Object
 
    $sampleSet|%{$PerformanceCounterSampleSet+=$_.CounterSamples|Select-Object -Property TimeStamp, Path, @{name='Cookedvalue';e={[math]::round($_.CookedValue,2)}} }
 
    $computer=Get-Ciminstance Win32_OperatingSystem
    $physicalRam=$computer.TotalVisibleMemorySize
    $physicalRamMb=$physicalRam/1024
    
    #$timeStamp=(get-date).ToString("M/dd/yyyy h:mm:ss tt")
    $timeStamp=$PerformanceCounterSampleSet[4].Timestamp
    $cpuLoad=(Get-WmiObject -computername $server win32_processor|select LoadPercentage|Measure-Object -property LoadPercentage -Average).Average
    $cpuBenchmark=80
    $cpuStats=[pscustomobject]@{
        Timestamp=$timeStamp
        Path='CPU Load Percentage'
        Cookedvalue=$cpuLoad
        Benchmark="<$cpuBenchmark"
        Comment=if($cpuLoad -lt $cpuBenchmark){'OK'}else{'CPU resource utilization is high'}
    }

    $result+=$PerformanceCounterSampleSet[0]|Select-Object *,@{name='Benchmark';e={">$([math]::round(($physicalRamMb * 0.049)))"}},@{name="Comment"; e={if($_.CookedValue/$physicalRamMb -gt 0.049){'OK'}else{"$([math]::round(($_.CookedValue/$physicalRamMb)*100,2))% is NOT OK"}}}
    $result+=$PerformanceCounterSampleSet[1]|Select-Object *,@{name='Benchmark';e={"<50"}},@{name="Comment"; e={if($_.CookedValue -lt 50){'OK'}else{'too much paging'}}}
    $result+=$PerformanceCounterSampleSet[2]|Select-Object *,@{name='Benchmark';e={">95"}},@{name="Comment"; e={if($_.CookedValue -gt 0.95){'OK'}else{'insufficient buffer cache'}}}
    $result+=$PerformanceCounterSampleSet[3]|Select-Object *,@{name='Benchmark';e={"<2"}},@{name="Comment"; e={if($_.CookedValue -lt 2){'OK'}else{'not enough memory to retain buffer'}}}
    $result+=$PerformanceCounterSampleSet[4]|Select-Object *,@{name='Benchmark';e={">300"}},@{name="Comment"; e={if($_.CookedValue -gt 300){'OK'}else{'check index designs and SQL RAM allocation'}}}
    $result+=$cpuStats
    return $result
}
 
getSqlPerformanceCounter|ft -autosize
#Sample Output:
#
# Timestamp             Path                                                          Cookedvalue Benchmark Comment
# ---------             ----                                                          ----------- --------- -------
# 1/15/2021 12:25:01 PM \\dynamics02\\memory\available mbytes                                1810 >1606     OK
# 1/15/2021 12:25:01 PM \\dynamics02\\memory\pages/sec                                        1.6 <50       OK
# 1/15/2021 12:25:01 PM \\dynamics02\\sqlserver:buffer manager\buffer cache hit ratio         100 >95       OK
# 1/15/2021 12:25:01 PM \\dynamics02\\sqlserver:buffer manager\lazy writes/sec                  0 <2        OK
# 1/15/2021 12:25:01 PM \\dynamics02\\sqlserver:buffer manager\page life expectancy          4509 >300      OK

Interpretation:

  1. Available MBytes is a pass-through value queried from the OS. It should be more than 5% of total system memory to indicate sufficient allocation, although auto configured SQL servers tend to ‘push the limit’ up to that threshold.
  2. The pages/sec counter “Shows the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It is the sum of Memory\ Pages Input/sec and Memory\ Pages Output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\ Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) and noncached mapped memory files.” (Microsoft Documentation). This value should remain below 50 to indicate healthy activities.
  3. SQL server should read all pages from its buffer cache. Hence, this value should remain above 95% to indicate memory sufficiency.
  4. Lazy write is defined as the “number of times per second SQL Server relocates dirty pages from buffer pool (memory) to disk.” According to Microsoft, “For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.” Hence, the term ‘lazy.’ The ideal values for this metric is between 0 and 2.
  5. Page life expectancy is the duration, in seconds, that a page resides in the buffer pool. As a rule of thumb, 300 seconds (5 minutes) or higher is the expected value for healthy databases. Besides memory as that a la carte indicator of low page life counters, lack of indexes or misconfigured indexes could lead to degradation of page life expectancy.

Leave a Reply

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