Monday, June 21, 2010

Perfmon - useful links


I have couple of useful links on Perfmon.

1) How to correlate Perfmon data with Profiler Data?

Perfmon data gives us the high level picture of what is causing a performance slowdown.Profiler shows what queries exactly running. Correlating both, tells which processes/queries were exactly responsible for a performance slowdown. The article describes neat and clean way provided by Microsoft tools to correlate perfmon and profiler data. Refer here.
for more details. Note that one needs SQL Server 2005 tools to use correlate functionality.

2) How to capture perfmon data directly into table?

Perfmon counter data can be automatically saved to SQL Servers table instead of binary file (blg) or csv file.Saving Perfmon counter into sql server table allows one to check perfmon counter values like CPU, memory, Disk usage % remotely by just using SSMS/ Query analyzer to connect to the server, without actually logging into the server. The above mentioned technique is one method which works in SQL 2000 to check the CPU of monitored instance remotely. Note that the method explained in last post doesn't work for SQL 2k. Refer here for detailed explanation.

The Perfmon data gets captured into two tables namely CounterDetails,CounterData. The details can be viewed using the following query.


SELECT counterdetails.objectname,
       CASE
         WHEN Isnull(instancename, '!@#$') = '!@#$' THEN ''
         ELSE instancename
       END                        AS instance_name,
       counterdetails.countername AS counter_name,
       counterdata.counterdatetime,
       counterdata.countervalue
FROM   counterdata,
       counterdetails
WHERE  counterdetails.counterid = counterdata.counterid
ORDER  BY counterdata.counterdatetime 

No comments: