To obtain the same information that Activity Monitor in SQL Server and Task Manager in the Windows provides for all the SQL Server Instances running on our server.
My way of achieving it
Using powershell and registry we can find out information about the sql server services that are running on our server and with that information we can construct the counter names that we want to gather. The counters might be added or removed as needed but I tried to put what Activity Monitor and Task Manager display. The main benefit is that we can have these for all the instances and taking advantage of the out-gridview cmdlet we can sort and order values as we like without worrying that the information displayed after sort will contains processes that we don’t want. For example the output created for the performance counters below
\Processor(_Total)\% Processor Time
\LogicalDisk(C:)\Current Disk Queue Length
\LogicalDisk(C:)\Disk Read Bytes/sec
\LogicalDisk(C:)\Disk Write Bytes/sec
\LogicalDisk(D:)\Current Disk Queue Length
\LogicalDisk(D:)\Disk Read Bytes/sec
\LogicalDisk(D:)\Disk Write Bytes/sec
\Process(sqlservr)\% Processor Time
\Process(sqlservr)\IO Read Bytes/sec
\Process(sqlservr)\IO Write Bytes/sec
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:SQL Statistics\Batch Requests/sec
while the output for a cluster might be the one below
Of course that for some counters depending on the SQL Server Edition we might receive these type of errors which are normal and I display them.
get-counter : The \SQLAgent:Jobs(_Total)\Active jobs performance counter path is not valid.
The script can be downloaded from here