Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 354

PowerShell v2.0 from http://support.microsoft.com/kb/968929 .
F Download Performance Analysis of Logs (PAL) tool from http://pal.codeplex.
com/releases/view/6759 .
F Additionally, we will use references to the Performance Monitor tool, which is a
built-in tool in the Windows Server and desktop operating system.

How to do it...

The following are important steps that will help in managing storage systems for analyzing
bottlenecks. These are essential to act in a proactive manner when a performance
problem occurs:

1. Install the PAL tool by clicking on the PAL_Setup_V2.0.6_x64.msi ile.

2. Gather the storage system-related counters using PERFMON (Performance Monitor)

tool for the following counters:
Physical Disk: percent Disk Time
Physical Disk: percent Disk Read time
Physical Disk: percent Disk Write time
Physical Disk: Avg. Disk Queue Length
Physical Disk: Avg. Disk sec/Transfer
Physical Disk: Current Disk Queue Length
Physical Disk: Disk Bytes/sec
Physical Disk: Disk Transfers/sec
Paging File: Paging File percent Usage and percent Usage Peak

3. Store these performance monitoring data in a .blg or .csv file in order to analyze

the data for the PAL tool.

4. Copy the collected performance monitoring data to a folder where the PAL tool will be

directed to extract the required information.

5. Run the PAL tool from the desktop and choose a threshold file to perform further

analysis collection.

6. Click on the Analysis button to process the threshold file; the execution will be

resource-intensive, which can take several minutes to complete.

7. Once the PAL analysis is completed, a HTML-based report will be displayed on the

local browser. The report consists of an itemized table of contents that will show a
number of alerts raised for each analysis.

8. Each of that analysis information will present a chart of collected performance

counter, which contains analyses, description, references, and raised alerts.