Home > Performence Tuning > Performance Analysis of Logs (PAL) Tool

Performance Analysis of Logs (PAL) Tool


Logman

Manages and schedules performance counter and event trace log collections on local and remote systems.

Data Collection

First, we need to identify what Objects and Counters we need. We can automate creating one perfmon log file for each day. Then move the old files to a network share for later reference.

Cut and paste the contents below to a file name “SQL2005BaselineCounters.config” and save the file in “E:\perflogs”. You can choose any local drive/folder, make sure there is enough space in it. Try not to use the same storage that your SQL Server databases use, because this can slow down your I/O throughput.

Note in the below code I am collecting SQL Server data for an instance named “MSSQL$DEV”. This will need to be replaced for your server. If you just have a default instance this will just be “SQLServer

“\Memory\Available MBytes”
“\Memory\Free System Page Table Entries”
“\Memory\Pages Input/sec”
“\Memory\Pages/sec”
“\MSSQL$DEV:Access Methods\Full Scans/sec”
“\MSSQL$DEV:Access Methods\Page Splits/sec”
“\MSSQL$DEV:Access Methods\Workfiles Created/sec”
“\MSSQL$DEV:Access Methods\Worktables Created/sec”
“\MSSQL$DEV:Buffer Manager\Buffer cache hit ratio”
“\MSSQL$DEV:Buffer Manager\Checkpoint pages/sec”
“\MSSQL$DEV:Buffer Manager\Free pages”
“\MSSQL$DEV:Buffer Manager\Lazy writes/sec”
“\MSSQL$DEV:Buffer Manager\Page life expectancy”
“\MSSQL$DEV:Buffer Manager\Page reads/sec”
“\MSSQL$DEV:Buffer Manager\Page writes/sec”
“\MSSQL$DEV:Buffer Manager\Stolen pages”
“\MSSQL$DEV:General Statistics\Logins/sec”
“\MSSQL$DEV:General Statistics\Logouts/sec”
“\MSSQL$DEV:General Statistics\User Connections”
“\MSSQL$DEV:Latches\Average Latch Wait Time (ms)”
“\MSSQL$DEV:Locks(_Total)\Average Wait Time (ms)”
“\MSSQL$DEV:Locks(_Total)\Lock Requests/sec”
“\MSSQL$DEV:Locks(_Total)\Number of Deadlocks/sec”
“\MSSQL$DEV:Memory Manager\Target Server Memory (KB)”
“\MSSQL$DEV:Memory Manager\Total Server Memory (KB)”
“\MSSQL$DEV:SQL Statistics\Batch Requests/sec”
“\MSSQL$DEV:SQL Statistics\SQL Compilations/sec”
“\MSSQL$DEV:SQL Statistics\SQL Re-Compilations/sec”
“\Paging File(_Total)\% Usage”
“\Paging File(_Total)\% Usage Peak”
“\PhysicalDisk(_Total)\Avg. Disk Read Queue Length”
“\PhysicalDisk(_Total)\Avg. Disk sec/Read”
“\PhysicalDisk(_Total)\Avg. Disk sec/Transfer”
“\PhysicalDisk(_Total)\Avg. Disk sec/Write”
“\PhysicalDisk(_Total)\Avg. Disk Write Queue Length”
“\Process(sqlservr)\% Privileged Time”
“\Process(sqlservr)\% Processor Time”
“\Processor(_Total)\% Privileged Time”
“\Processor(_Total)\% Processor Time”
“\System\Context Switches/sec”
“\System\Processor Queue Length”

Fig 1

Next step is to create a counter log in perfmon which will use the above counters. From a command prompt, execute the statement below on your SQL Server box.

logman create counter SQL2005Perf -f bin -b 01/01/2009 00:00:05 -E 01/01/2009 23:59:00 -si 05 -v mmddhhmm -o “E:\perflogs\SQL2005Perf” -cf “E:\perflogs SQL2005BaselineCounters.config” -u domain\username *

You will be prompted for the password

Fig 2

This will create a new counter log named “SQL2005Perf”. To verify this, open perfmon and click “counter logs” under Performance logs and alerts.

Fig 3

Here is the notes for each option used above:

  • SQL2005Perf – name of the counter log
  • -f bin – binary format, with a .blg extension
  • -b 01/01/2009 00:00:05 – begin data and time, input a datetime to start the counter log at a later time, if you input a datetime that is past, the counter log will start immediately
  • -E 01/01/2009 23:59:00 – end date and time
  • -si 05 – 5 second interval
  • -v mmddhhmm – filename will be named as SQL2005Perf_mmddhhmm.blg
  • -o “E:\perflogs\SQL2005Perf” – output folder name
  • -cf “E:\perflogs\SQL2005BaselineCounters.config” – config file name with the counters
  • -u domain\username * – the * will prompt for the password for the domain user account.

You can manually start and stop the above counter logs with below commands:

Logman start SQL2005Perf 

Logman stop SQL2005Perf

To get a list of all your counter logs you can run this command.

Logman query

Automate Data Collection

 

It would be nice if we have performance data for each day. This can be done by stopping the counter log at 11:59:59 PM and again starting it at 12:00:01 AM daily. Once you stop the counter log the log file is closed and starting the counter log creates a new log file.

Lets create a scheduled task which will do this.

Lets first create a batch file “StopCopyStart.bat” that will be executed by the scheduled task. Copy the contents below and save it in the folder “E:\perflogs”. Make sure to change the “Servername”, “SourceServerName” to match your servers.

logman stop sql2005perf
sleep 1
move E:\perflogs\SQL2005Perf\*.blg \\Servername\E$\perflogs\SourceServerName
sleep 1
logman start sql2005perf

Fig 4

From the command prompt execute this statement to create the scheduled task.

schtasks /create /tn “Stop Copy And Start SQL2005Perf Perfmon Counter Log” /tr E:\perflogs\StopCopyStart.bat /sc daily /st 23:59:58 /ed 01/01/2099 /ru system

Fig 5

Here are the notes for each option used above:

  • schtasks /create – create a new scheduled task
  • /tn “Stop Copy And Start SQL2005Perf Perfmon Counter Log” – scheduled task name
  • /tr E:\perflogs\StopCopyStart.bat – run this batch file
  • /sc daily /st 23:59:59 – schedule time
  • /ed 01/01/2099 – end date
  • /ru system user account

A new scheduled task with name “Stop Copy And Start SQL2005Perf Perfmon Counter Log” will now be created. From the command prompt type “schtasks”, this will list all the scheduled tasks.

Next Steps

  • The idea is to move the performance data .blg file from each of the SQL Server to a common share. Create one folder for each server and move the .blg file there. So, the user account to execute the scheduled task should have permissions on the share where we move the performance log file.
  • You should do some housekeeping on this network share to delete files older than 30 or 60 days.
  • Always have one week ( one file for each day of week ) performance data for each server to be used as a baseline. If you have any performance problems you can compare with the baseline.

Performance Analysis of Logs (PAL) Tool

PAL (Performance Analysis of Logs) tool is a powerful tool that reads in a performance monitor counter log and analyzes it using known thresholds.

Features

Thresholds files for most of the major Microsoft products such as IIS, MOSS, SQL Server, BizTalk, Exchange, and Active Directory.

An easy to use GUI interface which makes creating batch files for the PAL.ps1 script.

A GUI editor for creating or editing your own threshold files.

Creates an HTML based report for ease of copy/pasting into other applications.

Analyzes performance counter logs for thresholds using thresholds that change their critieria based on the computer’s role or hardware specs.

For more information

http://pal.codeplex.com/

Once the PAL is installed on your local system or shared server you can analyze the perfmon countests.

  • Copy all the perfmon files to C:\PAL location.(any folder)
  • Edit the PAL_reports_creation.bat and make shore all the file names should be same as the files which you have copied to c:\PAL.
  • Run the batch file bat1.bat and all the reports will generate and copied to output_path.

Create batch file and paste the script for each server.

Below script is for server1 if you have 5 servers then you need to have 5 times below script and set the parameters as per the server configurations.

Batch file:-

——————————————————————————————————————-

CScript “C:\Program Files\PAL\PAL v1.3.5\PAL.vbs” /LOG:”server1_perfmon_log_path.csv” /THRESHOLDFILE:”C:\Program Files\PAL\PAL v1.3.5\SQLServer2005.xml” /INTERVAL:”AUTO” /ISOUTPUTHTML:True /ISOUTPUTXML:False /OUTPUTDIR:”output_path” /HTMLOUTPUTFILENAME:”[LogFileName]_PAL_ANALYSIS_[DateTimeStamp]_[GUID].htm” /NumberOfProcessors:12 /ThreeGBSwitch:False /SixtyFourBit:True /TotalMemory:16 /KernelDumpOnCDrive:True

————————————————————————————————————————–

Advertisements
Categories: Performence Tuning
  1. Abe
    March 3, 2017 at 12:38 am

    It’s an amazing paragraph in support of all the web visitors; they will get advantage from
    it I am sure.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: