Work has started on new version with counter sets for SQL 2012 and upwards.
Counter results are stored in a database to enable comparisons with baselines/previous traces.
A graphical display of relogged perfmon files. PAL does a good job but it takes a while and you and up with a huge report. The standard Perfmon interface tends to be messy when many counters are involved. Thats why I choose the dashboard interface.
Since I have to evaluate a lot of perfmon traces on a daily basis I wanted a tool that would give me quick overview of the server without having to delve through a thick report or through a cluttered perfmon diagram with lots of counters. Obviously I do that
every now and then but only if I know where I have to look.
Originally I tried to analyze the files in a Windows Forms .Net application. Since the chart control is in the framework that seemed to be a safe choice. I ended up with quite a lot of code. I don't have time to maintain all that so I switched to Excel
2010. With only a few lines of code I managed to get what I wanted.
To give you an indication how it looks:
Above you see a CPU analysis. Thresholds are all dynamic. At the moment the sheet is able to analyze 32 cores.
In this picture I analyze some counters from the SQLServer:Databases object. I have an overview of sizes and load on every database in the instance. This sheet can handle 100 databases. Instances with more databases are rare.
The locking counters. The latch counters are collected as well, but on this particular set they were not collected.
Logical IO overview. Most of us work with SANs and we ussually use the logical disk object in that case. It'll give you a quick overview of latency, throughput and IOPS. All very necessary to know on a SQL box.
After IO one of the most important information about your instance. 80% of performance problems on SQL boxes are IO problems. Of the other 20%, something like 80% are memory problems.
The workload of the instance during the trace.
With this workbook, I can quickly analyze the trace files. It will only take a few minutes to go over it and spot problems. Thresholds are dynamic and adaptable to different workloads. It helps me a great deal!
This release is workable. Too upload the data to the workbook: read the manual below:
Manual Perfmon Analysis Workbook.docx
My email is in the manual in case you run into problems.
- Create a proper dashboard