PAL designed by Clint Huffman, available on Codeplex.com. This tool analyzes the performance counters logs for you to identify if anything is outside a threshold. These thresholds are predefined in the program, thus you don’t have to remember stuff like minimum “Page Life Expectancy” suppose to be 300 (all though you really should remember at least that one :P). But it has many-many good counters to look at for system and SQL Server. So instead of me providing a list of all the counters you can look at there is functionality in PAL to export an XML file from which you can create Windows 2008 based Perfmon capture (will blog about it another time). But what I want to do is use the same counters on Windows 2003, however the 2003 version does not support XML so we have to use command line tool called logman.exe.
1. Start PAL.
2. Go to Threshold File.
3. Click on Threshold file Drop Down select “Microsoft SQL Server 2005/2008”.
4. Click Export to Perfmon Template File.
5. It’ll ask for Instance name, click OK (assuming we are running it on Default instance).
6. Close PAL.
7. Open the XML file in Notepad.
SQLCounters.XML File XML To Delete @ TOP
SQLCounters.XML File XML To Delete @ BOTTOM
8. Delete the Highlighted Part at the Top of the XML file and at the Bottom of the XML file.
9. Search for and replace ” ” with (nothing).
10. Search for and replace “” with (nothing).
11. So you are left with the above image.
logman create counter SQLPerfCollection –cf “C:\Temp\counters.txt” -si 00:10
14. You should see a collection in perfmon called SQLPerfCollections now.
Credit for this goes to a colleague of mine, Microsoft Platforms PFE (Thanks!!).