Enhance Database Performance for Contact Center UCCE/PCCE

Introduction

This document describes how to enhance the database performance after an upgrade in Cisco Unified Contact Center Enterprise (UCCE).

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

• Cisco Unified Contact Center Enterprise (UCCE)
• Package Contact Center Enterprise (PCCE)

Components Used

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

• UCCE/PCCE Release 12.6
• Microsoft SQL Server 2017 (Standard and Enterprise editions) with cumulative updates
• Microsoft SQL Server 2019 (Standard and Enterprise editions) with cumulative updates

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, ensure that you understand the potential impact of any command.

Background Information

After you perform a Common Ground or a Technology Refresh upgrade, complete the procedures described in this section to enhance the performance of the database. This is a one-time process and must be run only on the Logger and AW-HDS databases during a maintenance window.

Note: AW-HDS - Admin Workstation Historical Data Server, HDS-DDS - Admin Workstation Detail Data Server, AW-HDS-DDS - Admin Workstation Historical Data Server/Detail Data Server.

Performance Enhancement of TempDB

Note: You can skip this section when you perform a Technology Refresh upgrade.

Perform this procedure on Logger, Rogger, AW-HDS-DDS, AW-HDS and HDS-DDS machines to get the benefits of TempDB features for SQL Server. For more information about the SQL Server TempDB Database and its use, see the Microsoft SQL Server documentation for TempDB Database.

Note: This procedure applies to the Common Ground upgrade process only. If the Performance Enhancement of TempDB procedure is already completed on 12.5(1), then do not repeat the same procedure while upgrade to 12.5(2).

Procedure

1. Use Unified CCE Service Control to stop the Logger and Distributor services.
2. Log in to SQL Server Management Studio and run the mentioned queries on the primary database.
   
   To modify the TempDB Initial size to the recommended value:
   
   

   ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'tempdev', SIZE = 800, FILEGROWTH = 100)
   ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'templog', SIZE = 600, FILEGROWTH = 10%)

   
   To add multiple TempDB files:
   
   

   USE [primary];
   GO
   ALTER DATABASE [tempdb] ADD FILE 
        (NAME = N'tempdev2', FILENAME = N'' , SIZE = 800 , FILEGROWTH = 100);
   ALTER DATABASE [tempdb] ADD FILE 
        (NAME = N'tempdev3', FILENAME = N'' , SIZE = 800 , FILEGROWTH = 100);
   ALTER DATABASE [tempdb] ADD FILE 
        (NAME = N'tempdev4', FILENAME = N'' , SIZE = 800 , FILEGROWTH = 100);
   GO

   Note: For example, = C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf
   Make sure that you modify the values in the query based on the machines. For more information, see Increase Database and Log File Size for TempDB.

3. Restart the SQL Services.
4. Start the Logger and Distributor services.
   
   
   

Performance Enhancement of Logger Database

Procedure

Perform this procedure on Side A and Side B of the Logger database.

1. Use the Unified CCE Service Control to stop the Logger service.
2. From the command prompt, run the RunFF.bat file which is located in the <SystemDrive>:\icm\bin directory.
3. Proceed with the application of fill factor to ICM databases.
   

   Note: Based on the size of the database, it takes several minutes to several hours to apply fill factor to the database. For example, it takes anywhere between 2 to 3 hours for a 300-GB Logger. After the process is completed, the log file is stored in <SystemDrive>\temp\<DatabaseName>_Result.txt.

4. Use the Unified CCE Service Control to start the Logger service.
   To Troubleshoot Issues:
   See the RunFF.bat/help file for more information.

Performance Enhancement of AW-HDS Database

Procedure

1. Use the Unified CCE Service Control to stop the Distributor service.
2. From the command prompt, run the RunFF.bat file which is located in the <SystemDrive>:\icm\bin directory.
3. Proceed with the application of fill factor to ICM databases.
   

   Note: Based on the size of the database, it takes several minutes to several hours to apply fill factor to the database. For example, it takes anywhere between 2 to 3 hours for a 300-GB HDS. After the process is completed, the log file is stored in <SystemDrive>\temp\<DatabaseName>_Result.txt.

4. Use the Unified CCE Service Control to start the Distributor service.
   To Troubleshoot Issues:
   See the RunFF.bat/help file for more information.

Improve Reporting Performance

To improve the performance of the reporting application, modify the mentioned Windows settings on the database servers (AW-HDS, AW-HDS-DDS, HDS-DDS).

1. Increase the Paging File Size to 1.5 times the server memory.

   To change the Paging File Size, from the Control Panel search for Virtual Memory. In the Virtual Memory dialog box, select Custom size. Set both Initial size and Maximum size to 1.5 times the server memory.

2. Set the server Power Options to High Performance.

   From the Control Panel, select Power Options. By default, the Balanced plan is selected. Select Show additional plans and select High performance.

3. In SQL Server, disable Auto Update Statistics for AW and HDS databases.

   In the SQL Server Management Studio, right-click the database name in the Object Explorer and select Properties. Select the Options page. In the Automatic section of the page, set Auto Create Statistics and Auto Update Statistics to False.

Reduce Reserved Unused Space for HDS

Enable trace flag 692 on HDS database server to reduce the growth of reserved unused space on the AW-HDS, AW-HDS-DDS, HDS-DDS database servers, after you upgrade or migrate to Microsoft SQL 2017. For more information about the trace flag 692, see the Microsoft Documentation.

Procedure

1. Run the mentioned command to enable trace flag 692 on HDS database server.
   
   

   DBCC traceon (692, -1);
   GO
   

Note:An increase in the unused space can lead to unexpected purge trigger in HDS , trace flag 692 helps in mitigate unexpected purge issue. After you enable the trace flag, there be an increase of 10% to 15% CPU for a short duration. If the trace flag needs to be retained, the server startup options has to be updated with the -T(upper case) option. For more information, see Database Engine Service Startup Options.

Related Information

UCCE/PCCE Install Upgrade Guide

Technical Support & Documentation - Cisco Systems