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.
12. Now Save file as SQLCounters.txt and make sure the to change encoding to ANSI (Windows 2003 command doesn’t like unicode).
13. Run following command from Command Prompt.
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!!).
The PAL Threshold File export allows exporting directly to a TXT file for Logman, XML for Windows Server 2008 and later, and HTM for Windows Server 2003 computers. No need to convert the XML to text manually.
I hoped you would comment on this, I remeber looking for this for Cx but gave up instead and did it manually. Lesson learned, thanks Clint :). I’ll make counter post to show how to do it easy way ;-). heh.