counters for SQL?

Apr 22, 2009 at 7:04 PM

I recently downloaded PAL for the first time, and I'm essentially flabbergasted at the wealth of info it generates.  However, I'm still trying to get my arms around the process.  My understanding is this:

  1. Install LogParser 2.2, Office 2003 Web Components, and PAL
  2. Kick off the data gathering using the supplied script:  CreateAndStartPerfmonLogs.vbs (along with one of the counter.txt files.)
  3. Run PAL to crunch the data and generate the analysis in HTML format.

The zip file I downloaded contained the following counter files:

  • CounterList_BizTalk2004.txt
  • CounterList_BizTalk2006.txt
  • Counterlist_IIS6.txt
  • CounterList_SystemOverview.txt

I was able to get the SystemOverview to work without any trouble, but I would also like to run the Exchange 2003/2007 and SQL 2005 analyses.  While it appears there are XML report files for these, where are the counter.txt files for them?  Am I missing something?

Thanks,

Jim Lancaster
Dallas, TX

 

Coordinator
Apr 28, 2009 at 12:01 AM

Hi Jim,

You have the right idea. Here is the full process:

1) Install Microsoft Log Parser v2.2 and Microsoft Office Web Components 2003.
2) Install and run the PAL tool.
3) Under the Threshold File tab, choose a role or product that closest matches the server you want to gather data from. For example, IIS.
4) Click the Export to Perfmon Template File button to create a Perfmon log template (HTM) file.
5) Convert the HTM file to TXT by removing all of the HTML tags. Only the counter paths should be there like this:
\LogicalDisk(*)\*
\Memory\*
\Network Interface(*)\*
\PhysicalDisk(*)\*
\Process(*)\*
\Processor(*)\*
\System\*
\TCPv4\*
...
6) Use the CreateAndStartPerfmonLogs.vbs script to create perfmon logs on many servers using the TXT file you created in the last step.
7) After the logs have gathered data, optionally use the StopPerfmonLogs.vbs to stop the logs otherwise, the logs will automatically stop after 24 hours of collecting.
8) Gather the logs into one location using the MovePerfmonlogs.vbs script.
9) Analyze the logs using the PAL Wizard tool.

The XML files in the PAL directory are the PAL threshold files - not for perfmon.
I don't have TXT files for Exchange and SQL. You would need to create them using the steps above.
Warning: Keep in mind that if you have SQL named instances, then the counter paths will alwasy be unique and will need to be manually added to the counter logs.

Apr 28, 2009 at 5:06 PM
I think you might be making the process more complicated than it actually needs to be.

There are already html files in the C:\Program Files\PAL\PAL v1.3.4.3\Perfmon_Templates folder which can be used in the Perfmon tool itself to create the log settings.
First of all, copy the template you want onto your server that will be monitored. Then go into the Perfmon tool, in the Counter Logs section, right click and select New Log Settings from...
Then select the html file from the first step. It selects all the correct counters, and if you want to make changes to the filename or other specifics you can, otherwise just click OK and you're done. Then you click Start and you're away.

By the way, my C:\Program Files\PAL\PAL v1.3.4.3\Perfmon_Templates folder already contains a SQL server template.

Hope that helps.

David.
Coordinator
Apr 29, 2009 at 6:08 AM
True, but in this case, we are trying to create perfmon logs on remote servers having them all log locally to themselves. Logman.exe will do this, but the "-cf" parameter doesn't accept HTM based perfmon log templates, so we have to strip out the HTML first. In any case, thank you for the reminder about the Perfmon_Templates folder. I had forgotten about that.
Apr 29, 2009 at 2:40 PM
See patch 2843 that I just uploaded for an simple way to setup a continuously running perfmon log that functions like an airplane's flight recorder. And a full set of counter lists.

http://pal.codeplex.com/SourceControl/PatchList.aspx

Apr 29, 2009 at 7:11 PM
OK - thanks for the clarification clinth. I didn't know about LOGMAN, so I'm a happy camper now.


May 1, 2009 at 12:52 AM
Thanks guys.  You've been a tremendous help.

Is it possible to overload one of the counter files with counters from both SQL and IIS for example.  Then run the SQL report against the logs in one pass and the IIS report in another?

Jim

Perhaps others might find this batchfile useful:
----------------------------------------------

@echo off
rem Silently install PAL components and start gathering data for System Overview.
rem    1. Download LogParser 2.2, the Office 2003 Web Components, and PAL and save
rem       together under the same directory.
rem    2. Unzip PAL_v1.3.4.3.zip into the same directory.
rem    3. Run this batch file from the same directory

rem Perform silent installs of all components.
msiexec /package logparser.msi /quiet
owc11.exe /quiet
msiexec /package pal_setup.msi /quiet

rem Start data gathering
cd "C:\Program Files\PAL\PAL v1.3.4.3\PerfmonLogScripts"
CreateAndStartPerfmonLogs.vbs %computername% Computer: CounterList_SystemOverview.txt

 

Coordinator
May 11, 2009 at 10:31 PM

Sure, you can add as many counters to a perfmon counter log as you want. If you want PAL to analyze the both at the same time, then it's a bit trickier. You would need to merge the IIS and SQL xml threshold files. I used to have an all in one threshold file, but it took too long to run and was becoming difficult to maintain with all of the changes in all of the other thresholds, so I removed it. Regarding merging of PAL threshold files, copy and paste the ANALYSIS nodes.

May 13, 2009 at 8:20 AM

I must be doing something wrong.  I exported the SQL template and removed the HTML tags.  The result was this:

\LogicalDisk(*)\*
\Memory\*
\Network Interface(*)\*
\Paging File(*)\*
\PhysicalDisk(*)\*
\Process(*)\*
\Process(sqlservr)\*
\Processor(*)\*
\SQLServer:Access Methods\*
\SQLServer:Buffer Manager\*
\SQLServer:General Statistics\*
\SQLServer:Latches\*
\SQLServer:Locks(*)\*
\SQLServer:Memory Manager\*
\SQLServer:SQL Statistics\*
\System\*

I get the following result when I try to run it:

___________________________________________________________________________

C:\Program Files\PAL\PAL v1.3.4.3\PerfmonLogScripts>CScript CreateAndStartPerfmo
nLogs.vbs sagiss-ts SQL2005V2 CounterList_SQL2005.txt
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

logman create counter HealthCheck_SQL2005V2_sagiss-ts -s sagiss-ts -o c:\perflog
s\HealthCheck_SQL2005V2_sagiss-ts.blg -f bin -v nnnnnn -cf CounterList_SQL2005.t
xt -si 00:01:00 -b 5/13/2009 2:11:01AM -e 5/14/2009 2:11:01AM


Error:
Invalid counter path: <_"\LogicalDisk(*)\*

_____________________________________________________________________________

If I delete the LogicalDisk counter from the counterlist file, I get this:

Error:
Invalid counter path: <_"\Memory\*

______________________________________________________________________________

There is something wrong with the file format I'm using or something.  This has to be incredibly simple, but I can see it.

Jim

 

Coordinator
May 14, 2009 at 2:11 AM

Make sure you save the text file as ANSI encoding. Any other text encoding will make it fail. Yes, this has happended to me *too* many times. ;-)

Jun 18, 2009 at 12:11 AM
Edited Jun 19, 2009 at 10:31 PM

 

I just created a Powershell one-liner to export the counters from the XML, I thought I would share.

Get-ChildItem *.xml | ForEach {([xml](Get-Content $_)).PAL.ANALYSIS | ForEach { $_.ANALYZECOUNTER } | Out-File "Counters_$($_.BaseName).txt" -encoding ascii}

Jun 24, 2009 at 11:38 AM

If you are looking for Exchange Performance monitor counter file definitions, you can grab them from http://blogs.technet.com/mikelag/archive/tags/Perfwiz/default.aspx for each flavor of Exchange.