The list of common DBA tasks includes setting up a server performance base line and comparing the performance of two or more servers. We often have to do this urgently when our customer calls and expects us to identify and remove a performance bottleneck right away. The following article includes code and hands-on instruction on how to automate such tedious tasks as reusing the preset monitor parameters and analyzing the results. Given the ever-increasing size of databases and the importance of database performance, this article can also help you monitor and tune the performance of your system, as well as improve your performance as a DBA.
The Performance Monitor console for Microsoft Windows 2000 Server can monitor both the operating system and the utilization of SQL Server resources. Unfortunately, it stores the results of the monitoring as a binary file or comma delimited text file, which is inconvenient for further analysis. Although Windows XP and Windows 2003 can write directly to the SQL Server database, the format of the stored data is also hard to use. However, we can work around this problem.
A typical monitoring setup includes the following scripts:
- A Performance Monitor Counter Log file containing a set of monitoring counters stored as an HTML file.
- The script creating the SQL Server database table containing proper fields for the Counter Log’s list of counters.
- A script for copying data from the performance log file to the table mentioned above.
- Scripts to analyze the resulting performance data.
The typical SQLperfmon Counter Logs I usually use contain the following counters:
Memory counters
– Pages/sec
– Page Reads/sec
Disk counters for every physical disk
– Avg. Disk Queue Length,
– Avg. Disk sec/Transfer
Processor counters
– % Processor Time – _Total
System counters
– Processor Queue Length
SQL Server counters
– Access Methods – Page Splits/sec
– General Statistics – User Connections
– Buffer Manager – Buffer Cash Hit Ratio
Observing the Counter Log HTML file, you’ll notice that a computer name is not mentioned anywhere in the script, thus this SQLperfmon.htm file can be installed on any machine running Windows 2000 Server with SQL Server 2000. Here is the script for SQLperfmon.htm:
<HTML>
<HEAD>
<META NAME=”GENERATOR” Content=”Microsoft System Monitor”>
<META HTTP-EQUIV=”Content-Type” content=”text/html; charset=iso-8859-1″>
</HEAD><BODY>
<OBJECT ID=”DISystemMonitor1″ WIDTH=”100%” HEIGHT=”100%” CLASSID=”CLSID:C4D2D8E0-D1DD-11CE-940F-008029004347″>
<PARAM NAME=”_Version” VALUE=”196611″>
<PARAM NAME=”LogName” VALUE=”SQLperfmon”>
<PARAM NAME=”Comment” VALUE=””>
<PARAM NAME=”LogType” VALUE=”0″>
<PARAM NAME=”CurrentState” VALUE=”1″>
<PARAM NAME=”LogFileMaxSize” VALUE=”-1″>
<PARAM NAME=”LogFileBaseName” VALUE=”SQLperfmon”>
<PARAM NAME=”LogFileSerialNumber” VALUE=”1″>
<PARAM NAME=”LogFileFolder” VALUE=”C:PerfLogs”>
<PARAM NAME=”LogFileAutoFormat” VALUE=”6″>
<PARAM NAME=”LogFileType” VALUE=”0″>
<PARAM NAME=”StartMode” VALUE=”0″>
<PARAM NAME=”StopMode” VALUE=”0″>
<PARAM NAME=”RestartMode” VALUE=”0″>
<PARAM NAME=”LogFileName” VALUE=”C:PerfLogsSQLperfmon_10271514.csv”>
<PARAM NAME=”EOFCommandFile” VALUE=””>
<PARAM NAME=”Counter00001.Path” VALUE=”MemoryPage Reads/sec”>
<PARAM NAME=”Counter00002.Path” VALUE=”MemoryPages/sec”>
<PARAM NAME=”Counter00003.Path” VALUE=”PhysicalDisk(0 C:)Avg. Disk Queue Length”>
<PARAM NAME=”Counter00004.Path” VALUE=”PhysicalDisk(0 C:)Avg. Disk sec/Transfer”>
<PARAM NAME=”Counter00005.Path” VALUE=”PhysicalDisk(1 D:)Avg. Disk Queue Length”>
<PARAM NAME=”Counter00006.Path” VALUE=”PhysicalDisk(1 D:)Avg. Disk sec/Transfer”>
<PARAM NAME=”Counter00007.Path” VALUE=”Processor(_Total)% Processor Time”>
<PARAM NAME=”Counter00008.Path” VALUE=”SQLServer:Access MethodsPage Splits/sec”>
<PARAM NAME=”Counter00009.Path” VALUE=”SQLServer:Buffer ManagerBuffer cache hit ratio”>
<PARAM NAME=”Counter00010.Path” VALUE=”SQLServer:General StatisticsUser Connections”>
<PARAM NAME=”Counter00011.Path” VALUE=”SystemProcessor Queue Length”>
<PARAM NAME=”CounterCount” VALUE=”11″>
<PARAM NAME=”UpdateInterval” VALUE=”60″>
<PARAM NAME=”SampleIntervalUnitType” VALUE=”2″>
<PARAM NAME=”SampleIntervalValue” VALUE=”1″>
</OBJECT>
</BODY>
</HTML>