StarQuest Technical Documents

SQDR - SQL Server Performance Tips

Last Update: 25 March 2022
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:

• General Tips
• SSMS Activity Monitor
• Using sp_who, sp_who2, and sp_WhoIsActive stored procedures
• Using IR_SubscriptionStats.sql
• Always On Availability Groups
• Azure and other cloud services

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.
    

Additional information about using SSMS Activity Monitor, SSMS Query Store, and other tools can be found at How to identify slow running queries in SQL Server.

Using sp_who, sp_who2, and sp_WhoIsActive stored procedures

Microsoft provides a system stored procedure called sp_who to retrieve information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

sp_who2 is similar to sp_who but it is not documented nor supported but it returns more information and performance counter from the current processes such as the program name executing the command, Disk IO, CPU Time, last batch execution time.

A more powerful stored procedure called “sp_whoisactive” has been developed by Adam Machanic to fill in the gap between the actual needs of DBAs and the currently provided procedures (sp_who and sp_who2). For details, see Monitoring activities using sp_WhoIsActive in SQL Server.

 

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.

Invoking IR_SubscriptionStats.sql in 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.

Always On Availability Groups

You may encounter slow commit behavior (e.g. there are occasionally long periods where available updates are not being applied, or a long delay after completion of a baseline) if the destination SQL Server is part of an Availability Group and sync mode is set to synchronous commit - i.e. transactions are committed on the secondary replicas before the primary.

Check the activity monitor for processes in the state of HADR_SYNC_COMMIT.

Tuning a SQL Server Availability Group for your environment and data may requires special skills and possibly trial and error.

Possible Solutions:

• Consider using asynchronous mode
  
• Tune or upgrade the network between the replicas
  
• Tune or upgrade the systems running SQL Server
  
• Remove unnecessary indexes
  

Resources

• AboutSQLServer: The danger of HADR_SYNC_COMMIT wait and synchronous replication in AlwaysOn Availability Groups
• SQLShack: HADR_SYNC_COMMIT

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 issues for an Azure SQL 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.

Also see Overview of Azure SQL Managed Instance resource limits.