Querying Perfmon data
Posted by decipherinfosys on June 25, 2008
We have blogged before on topics related to perfmon (Performance Monitor) in some of our previous posts – you can search for perfmon on this site to get to those articles. In this post, we will cover how one can take that data, load it up in SQL Server and run simple SQL queries against it to make analysis even more easier. In the next post, we will look at one of the issues that was reported by one of our readers – some of the system counters were missing in her perfmon install and she was not able to figure out why that was the case. We will post the troubleshooting tips that we had provided to her in our next post on this topic.
We will just run a simple perfmon trace and save the results to help demonstrate how one can go about querying that data. Click on Start/Run and then type perfmon and press Ok. It will bring up the Performance Monitor window for you. You will see three counters by default: Pages/Sec., Avg. Disk Queue Length and % Processor Time. If you click on the + sign in the pane above, you can chose to add/remove more counters in this run. If you are not familiar with any of the counters, you can also clock on explain to understand what that particular really does.
We have set up a perfmon trace called CPU_HOG as you can see from the image below:
And before we start the trace, let’s make sure that we save this log as a csv file so that we can later on import this into SQL Server. The default is the *.blg extension which SQL Server won’t recognize while importing this data into the database. See the image below on how to go about changing the setting for the log file:
Now, let’s right click on the trace and start capturing the data. After running it for some time, we stop the trace. And now, we have the file: CPU_HOG_000001.csv which is a csv file that can easily be loaded into SQL Server using the Export/Import Wizard. One can even chose to write up a SSIS package to be able to nightly collect that information from the lightweight trace(s) to help set up the baselines for the servers and do reporting based on that data using simple SQL queries,
After you have stopped the trace, right click on the database in SQL Server Management Studio (SSMS) into which you want to load up this data and select Tasks/Import Data. That will bring up the Export/Import Wizard and then you can follow these simple instructions of loading up the file:
We are selecting that csv file as the source above. Note that you had to select Flat file as the data source and had to put “” (double quote) in the text qualifier section and check the box which stated that the column names are in the first data row. Now, click on next and select the destination:
After this, you can edit the mappings to dump this in a pre-defined table structure or even let it create the table for you. We would recommend creating your own table with meaningful column names otherwise, if you let the wizard take the column names from the source, you will end up with column names int his format: “Full Server Name Counter Name”. And then you can simply follow the next steps and either save this as a SSIS package (refer to our SSIS posts on the blog) or just execute it to copy over the data to a table in the database that you had chosen above.
Once you do that, you can then simply write and execute SQL queries against the data set, for example:
WHERE PAGE_IO_LATCH_WAITS < @PILW
AND MEM_AVAIL_BYTES < @MAB
You can create your own stored procedure with different parameters and check for those values every night after the load is done. This will help you to prepare a baseline version of the performance metrics for your servers as well as help troubleshoot performance issues quickly.