The first step in SQL Server performance tuning usually starts with finding bottleneck at the server side. It includes monitoring to some suspicious symptoms on CPU, Memory, Disk, and various SQL Server counter. Yes, Performance Monitor tool is already available on Windows machine. It is a very valuable tool for detecting the server bottleneck, and it is free. Just type perfmon and we are ready to start to do monitoring.
Usually I create counter log and let the monitoring runs at the background based on specific schedule and sampling interval. With this way, I can minimize the performance impact by running Perfmon inside the box. Once I got the log data, then I am ready to start the analysis. There are thousands or even millions rows of logs inside. How do I analyze and finding the system behavior, detect the pattern and plan a possible actions? Obviously I can import the CSV log file to SQL table or Excel and then use my T-SQL knowledge to summarize the data. But actually there is easier way to do it, Clint Huffman of Microsoft already created PAL.
The PAL does query to the counter log data using Log Parser, and display it in a nice HTML format. It is a graphical chart data analysis, so that’s why PAL requires Office Web Component installed to view the report. We can download PAL from CodePlex, and don’t forget to install Log Parser and OWC11 as pre-requisites. So the steps to do analysis with PAL are:
Collect counter log data with Perfmon
Use counter log as input to PAL
Analyze the report with PAL
The first screen of PAL will ask a location of counter log file that was created by Perfmon.
At the next screen, there are several options of system monitoring template. It depends of what kind of servers installed in the system.
The good things is PAL already comes with predefined template and threshold, so we do not need to think about the threshold for various counter. We con modify this template as well to suit our needs.
Here are several reports generated by PAL, very nice analysis with graphical trends.