USEFUL SITES :
Write for Us
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:
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:\PerfLogs\SQLperfmon_10271514.csv"> <PARAM NAME="EOFCommandFile" VALUE=""> <PARAM NAME="Counter00001.Path" VALUE="\Memory\Page Reads/sec"> <PARAM NAME="Counter00002.Path" VALUE="\Memory\Pages/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 Methods\Page Splits/sec"> <PARAM NAME="Counter00009.Path" VALUE="\SQLServer:Buffer Manager\Buffer cache hit ratio"> <PARAM NAME="Counter00010.Path" VALUE="\SQLServer:General Statistics\User Connections"> <PARAM NAME="Counter00011.Path" VALUE="\System\Processor 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>
