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",
    "\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.

  • With a freshly started SQL Server 2012 on my laptop (Win 7) I get 0 for the SQLServer counters except buffer cache hit ratio and page life expectancy. If I add –ComputerName $serverName to the Get-Counter line with $serverName pointing to one of my more active 2012 SqlServers I get non-zero values for everything except cache hit ratio base, lazy writes/sec, and memory grants pending. That last one could be a valid 0, I’d have to compare to PerfMon. How active is the 2014 Dev Edition you're checking?
    – Bruce
    May 13 '14 at 12:21
  • 1
    I found some of the problem. If I run powershell "as Administrator" it works fine. The user I was running it as had local admin on the box so I am not sure what caused this issue. To answer your question though, it has very little activity, but shows data in perfmon. May 13 '14 at 16:58

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

  • Thanks for the reply. I no longer have this instance running, but the coffee you linked to would likely solve this issue. Aug 18 '15 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.