Task Manager and Activity Monitor for SQL Server instances

Goal

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

\Memory\Available MBytes
\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)\Private Bytes
\Process(sqlservr)\ID Process
\Process(sqlservr)\IO Read Bytes/sec
\Process(sqlservr)\IO Write Bytes/sec
\Process(sqlservr)\Elapsed Time
\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
\SQLServer:Databases(_Total)\Backup/Restore Throughput/sec
\SQLAgent:Jobs(_Total)\Active jobs

is

perfmonamtm1

 

while the output for a cluster might be the one below

perfmonamtm

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

Leave a Reply

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