StarQuest Technical Documents

SQDR - SQL Server Performance Tips

Last Update: 08 August 2021
Product: SQDR & SQDR Plus
Version: 5.22 and later
Article ID: SQV00PL057

Abstract

This technical document provides tips on identifying and correcting performance issues when using Microsoft SQL Server as a destination.

Contents:

Symptom: An existing replication to a SQL Server destination suddenly became very slow, and SQDR Control Center showed that a backlog of incremental changes was growing on tier 2 (SQDR Plus). Data Replicator Manager showed that changes were being processed, but very slowly. All of the SQDR components appeared healthy.

Solution: The following steps were taken to determine why tier 4 (SQL Server destination) was having problems.

General Tips

  • On the destination system, check that CPU & RAM usage are at expected limits.
  • Verify that there are no locks on the destination table. If locks are a potential issue, verify that the database is configured for allow_snapshot_isolation ON and read_committed_snapshot ON. See Avoiding Locks for SQL Server Destination.

SSMS Activity Monitor

The steps below can be performed using SQL Server Management Studio:

  • Connect to the target instance
  • Right-click on the instance name and select Activity Monitor
  • Select Active Expensive Queries or Recent Expensive Queries
  • Wait for a few minutes for some activity to occur.
  • Look for any expensive statements that involve SQDR (e.g. UPDATE destination-table).

You should be able to identify SQDR-related statements if they are run by a userID that is used only by SQDR, either a SQL Server user or a Windows user that is being used as a service account.

  • Right click on the statement and select Show Execution Plan and try to identify which part of the execution plan can be improved (e.g. with index creation)
  • If no SQDR-related statements appear, then select Processes and look for any SQDR-related processes that are experiencing one of the following condtions (and remedy that condition):
    • Blocked by another process (there will be a value in the Blkid column) - i.e. another process has a lock on the table.
    • Task State is TRACEWAIT state. This state may occur if someone is tracing SQL Server activity, or if the SQL Server has encountered resource exhaustion e.g. out of buffers. The former issue can be resolved by ending the trace; resolving the latter issue may require restarting SQL Server or the operating system.

Using IR_SubscriptionStats.sql

SQDR provides a script IR_SubscriptionStats.sql to examine the incremental throughput of SQDR. This script is located in C:\Program Files\StarQuest\SQDR\Tools\mssql for a SQL Server control database. A similar script for a Db2 LUW control database is provided in the db2luw directory and can be used with IBM Data Studio.

Using SQL Server Management Studio:

  • Connect to the SQL Server instance where the SQDR Control database (typically named ControlDB) resides. This is typically located on the tier 3 system (where SQDR is running).
  • Select File/Open and open IR_SubscriptionStats.sql.
  • If your control database is named something other than ControlDB, modify the Use ControlDB statement.
  • Execute

The column RPS shows the historical information for Rows per Second.

To view the current status:

  • In Data Replicator Manager, select the I/R group of interest and Delete Statistics.
  • Wait a while.
  • Execute the query again - now RPS shows recent information.

If the destination database is experiencing performance problems, the value of RPS will be lower than previously experienced. Examine the RPS value again to confirm that the performance problem has been resolved.

Azure and other cloud services

When using a cloud-based service such as Azure SQL Managed Instance, the I/O governor may be throttling performance when the provisioned limits are exceeded.

The following views can be used to diagnose performance for an Azure SQL Database database or an Azure SQL Managed Instance.

sys.resource_stats - View CPU usage and storage data (coarse view: data is collected and aggregated within five-minute intervals; retained for 14 days)

sys.dm_db_resource_stats - View CPU, I/O, and memory consumption (finer view: captures data every 15 seconds and maintains historical data for 1 hour)

sys.dm_db_wait_stats - Returns information about all the waits encountered by threads that executed during operation.



DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.