This document describes the methodology to analyze whether your
Microsoft SQL server experiences a performance problem at the system level in a
Cisco Intelligent Contact Management (ICM) or IP Contact Center (IPCC)
Cisco recommends that you have knowledge of these topics:
The information in this document is based on these software and
Cisco ICM 4.6.x and later
Cisco IPCC Enterprise 4.6.x and later
Microsoft SQL Server 6.5 and 7.0
Microsoft Windows 2000
Microsoft Windows NT
The information in this document was created from the devices in a
specific lab environment. All of the devices used in this document started with
a cleared (default) configuration. If your network is live, make sure that you
understand the potential impact of any command.
Technical Tips Conventions for more information on document
The analysis methodology involves these steps:
Determine the Busy
Collect Performance Monitor Logs for the Busy
Apply Rules of
The Logger_Meters table contains performance information for the ICM
Logger process. The ICM Logger process on the Central Controller creates a new
Logger_Meters record in the central database every five minutes.
Enter the correct DateTime value, and run this SQL query against the
Logger database for a specific date:
SELECT CONVERT(char,DateTime,108) as Time, CONVERT(decimal(5,2),
RouteCallDetailTo5/300.0) as 'Calls/sec'
FROM Logger_Meters WHERE DateTime between 'MM/DD/YYYY 00:05' and 'MM/DD/YYYY 23:59'
ORDER BY Time
Note: This document displays the SQL query over multiples lines due to
In the SQL query:
RouteCallDetailTo5 represents the number of rows with route call
details, which are written during the five-minute interval. Here is the formula
to arrive at the value of 300:
60 seconds times 5 minutes = 300 seconds
MM represents the month, DD represents the date, and YYYY represents
Use Microsoft Excel to plot the results. Run the SQL query multiple
times on days that you know are busy. Compare the data you plot in Microsoft
Excel to determine the busy hour.
All performance counters, except the disk counters, are turned on by
default. You must use the diskperf command to turn
on the disk counters.
Complete these steps in order to turn on the disk
Open a command prompt on the Historical Data Server
Issue the diskperf -y command.
Diskperf is a tool that tests the performance of the disks
subsystem. The '-y' parameter sets the system to start all disk performance
counters when you restart the system.
Restart the system.
Until you restart the system, all the disk counter values,
including Avg. Disk Queue Length, always remain zero.
Use the diskperf -n command to shut off
the disk counters.
You must shut off disk counters only after you complete the
performance analysis and you are sure that you do not require any more
Performance Monitor (perfmon) logs.
The '-n' parameter sets the system to disable all disk performance
counters when the system is restarted.
Collect these counters in a perfmon log for a time frame that includes
a busy hour:
% Processor Time in the Processor object for all Processor instances
Processor Queue Length in the System object
Pages/sec in the Memory object
I/O - Page Reads/sec in the 6.5 SQL Server object
Page Reads/sec in the Buffer Manager object for Microsoft SQL Server
7.0 and 2000
Avg. Disk Queue Length in the Physical Disk object for all Physical
Use a Microsoft Excel spreadsheet to calculate averages, and to plot
and analyze the perfmon data. Based on your operating system, here is how you
must use Microsoft Excel for perfmon data:
On Windows 2000 SQL server, the perfmon log is usually a .csv file.
Microsoft Excel can directly read files in the .csv format.
On Windows NT 4.0, you need to export the perfmon log from the
Performance Monitor log format to a comma delimited file, and then read the
file in Microsoft Excel. You can use the export function in NT 4.0 perfmon to
Compare the persistent counter averages for the busy hour against the
acceptable values to determine which counter average causes a problem.
Here are the acceptable values:
%Processor Time < 80%
Processor Queue Length < 2
NT Pages/sec < 10
I/O - Page Reads/sec in SQL 6.5 Server <
Page Reads/sec in Buffer Manager for Microsoft SQL Server 7.0 and
2000 < 100
Avg. Disk Queue Length <
Any counter average that exceeds one of these rules of thumb can cause
the performance problem.
Note: In order to calculate the Avg. Disk Queue Length, divide the "Avg.
Disk Queue Length" counter for the Physical Disk instance by the number of
spindles that the Physical Disk contains. For example, 4 spindles in a typical
RAID array Physical Disk instance. Also, divide the Processor Queue Length by
the number of processors.