Guest

Cisco Unified Intelligent Contact Management Enterprise

Methodology to Analyze the System Level Performance of SQL Server

Document ID: 65187

Updated: Feb 08, 2006

   Print

Introduction

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) Enterprise environment.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

  • Cisco ICM

  • Cisco IPCC

  • Microsoft SQL

Components Used

The information in this document is based on these software and hardware versions:

  • 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.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Background Information

The analysis methodology involves these steps:

  1. Determine the Busy Hour.

  2. Collect Performance Monitor Logs for the Busy Hour.

  3. Apply Rules of Thumb.

Determine the Busy Hour

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 space limitations.

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 the year.

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.

Collect Performance Monitor Logs for 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 counters:

  1. Open a command prompt on the Historical Data Server (HDS).

  2. 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.

  3. Restart the system.

    Until you restart the system, all the disk counter values, including Avg. Disk Queue Length, always remain zero.

  4. 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:

  1. % Processor Time in the Processor object for all Processor instances

  2. Processor Queue Length in the System object

  3. Pages/sec in the Memory object

  4. I/O - Page Reads/sec in the 6.5 SQL Server object

  5. Page Reads/sec in the Buffer Manager object for Microsoft SQL Server 7.0 and 2000

  6. Avg. Disk Queue Length in the Physical Disk object for all Physical Disk instances

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 do so.

Apply Rules of Thumb

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 < 100

  • Page Reads/sec in Buffer Manager for Microsoft SQL Server 7.0 and 2000 < 100

  • Avg. Disk Queue Length < 2

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.

Related Information

Updated: Feb 08, 2006
Document ID: 65187