SQL Server – Powershell Get-Counter Not Returning Values for SQL Counters

powershellsql server

I am attempting to use PowerShell to get some SQL counters but it doesn't seem to return the SQL specific counters anymore. I am not sure what could have changed on my server that would have caused this.

The script is pretty straight-forward, I am not sure what I am missing:

## Define some variables
$serverName = "ISOMORPH" # Server we are collecting from

# Define our list of counters
$counters = @(
    "\Memory\Available MBytes",
    "\Memory\Pages/sec",
    "\PhysicalDisk(_Total)\Avg. Disk sec/Read",
    "\PhysicalDisk(_Total)\Avg. Disk sec/Write",
    "\PhysicalDisk(_Total)\Current Disk Queue Length",
    "\PhysicalDisk(*)\Avg. Disk sec/Read",
    "\PhysicalDisk(*)\Avg. Disk sec/Write",
    "\PhysicalDisk(*)\Current Disk Queue Length",
    "\Process(sqlservr)\% Privileged Time",
    "\Process(sqlservr)\% Processor Time",
    "\Processor(*)\% Privileged Time",
    "\Processor(*)\% Processor Time",
    "\SQLServer:Buffer Manager\Buffer cache hit ratio",
    "\SQLServer:Buffer Manager\Buffer cache hit ratio base",
    "\SQLServer:Buffer Manager\Lazy writes/sec",
    "\SQLServer:Buffer Manager\Page life expectancy",
    "\SQLServer:Memory Manager\Memory Grants Pending" ,
    "\SQLServer:SQL Statistics\Batch Requests/sec",
    "\System\Context Switches/sec",
    "\System\Processor Queue Length" 
)


## Get our performance counter data
Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 1

The script will return all of the counters, but all of the SQL counters show 0 values. I am not getting any errors, it just shows all of the SQL counters as having a value of 0.

If I manually open Performance Monitor I can see these counters and they have values. I am running this on a Windows 7 laptop with SQL 2014 Developer Edition installed.

EDIT: Further testing leads me to believe this is an OS specific problem. Someone else tested this script with Windows 8.1 running SQL 2012 and it returns 0 for the SQL specific counters in that case as well.

Best Answer

The reason your code works when you run it as Administrator is that when you start the powershell console it's not elevated.

There are several options to elevate the script. One nice example from Ben Armstrong lets the script check if it's elevated and if it's not it's started in elevated mode and executes your code: http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/09/23/a-self-elevating-powershell-script.aspx

Related Question