Posted by

Posted on

March 28, 2024

Posted under

Azure Managed Instance, Azure SQL Database, Performance

Comments

Elevating SQL Server Performance with In-Memory OLTP Table Variables

In the realm of database management, optimizing performance while minimizing resource contention is a perennial challenge. One of the critical areas where contention can be notably reduced is in the utilization of temporary storage mechanisms like tempdb. SQL Server’s In-Memory OLTP feature offers a compelling solution to this challenge, particularly through the use of in-memory optimized table variables. This post explores how leveraging In-Memory OLTP table variables can significantly improve performance by alleviating tempdb allocation contention, especially when compared to traditional table variables, temporary tables, or common table expressions (CTEs).

Understanding tempdb Contention

Before diving into the solution, it’s crucial to understand the problem. The tempdb system database in SQL Server is a shared resource used for various temporary storage activities, including user-created temporary tables, temporary stored procedures, table variables, and CTEs. However, heavy tempdb usage, particularly in high-concurrency environments, can lead to contention issues, impacting overall database performance.

Enter In-Memory OLTP Table Variables

In-Memory OLTP, introduced in SQL Server 2014, revolutionized how data is stored and accessed by storing designated tables and table variables in memory rather than on disk. This feature not only speeds up data access but also significantly reduces contention by bypassing traditional disk-based storage mechanisms.

Defining an In-Memory OLTP Table Variable

Unlike regular table variables, an in-memory OLTP table variable is defined as a user-defined table type with the MEMORY_OPTIMIZED = ON option. Here’s how to create one:

CREATE TYPE JB_InMem_TableType AS TABLE (
    col1 INT,
    col2 DATETIME,
    col3 VARCHAR(255),
    INDEX IX_JB_InMem_TableType_Col1 NONCLUSTERED (Col1)
) WITH (MEMORY_OPTIMIZED = ON);
GO

Utilizing In-Memory OLTP Table Variables in Stored Procedures

In-memory OLTP table variables can be seamlessly integrated into stored procedures. Here’s a simple example:

CREATE PROCEDURE sp_proc1
    @Col1 INT
AS
BEGIN 
    DECLARE @jb_table1 JB_InMem_TableType;
    INSERT INTO @jb_table1 
    SELECT col1, col2, col3 FROM Table1
    SELECT col1, col2, col3 FROM @jb_table1 WHERE col1 = @Col1;
END
GO

In this example, @jb_table1 is populated from Table1 and then queried, all while residing entirely in memory.

Benefits Over Traditional Methods

The use of in-memory OLTP table variables offers several advantages over traditional table variables, temporary tables, or CTEs:

Reduced tempdb Contention: Since in-memory objects do not rely on tempdb, they avoid contributing to tempdb contention, a common bottleneck in high-concurrency scenarios.
Performance Improvement: Accessing data in memory is significantly faster than disk-based access, leading to improved query performance.
Transactional Consistency: In-memory OLTP supports fully ACID-compliant transactions, ensuring data integrity without compromising on performance.

Conclusion

In-memory OLTP table variables represent a powerful tool in the SQL Server performance tuning arsenal. By leveraging in-memory storage, developers and DBAs can achieve significant performance improvements and reduce contention issues associated with tempdb. Whether you’re building high-performance applications or optimizing existing database workloads, the use of in-memory OLTP table variables is certainly worth considering.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted by

Vivek Janakiraman

Posted on

February 28, 2024

Posted under

Uncategorized

Comments

Understanding and Resolving Replication Error 20598 in SQL Server

Introduction

In the realm of SQL Server replication, encountering errors can be a daunting aspect of database management. One such error that frequently perplexes database administrators is the replication error 20598. This error typically arises during transactional replication when an UPDATE command is applied to a subscriber that cannot find the corresponding row. This blog post aims to demystify this error, exploring its causes and providing a step-by-step resolution to help you maintain the integrity and performance of your SQL Server replication environment.

Issue

The error message in question is as follows:

Command attempted:

if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00TAd26800029780003600000000, Command ID: 2)
Error messages:
The row was not found at the Subscriber when applying the replicated UPDATE command for Table ‘[dbo].[Table1]’ with Primary Key(s): [Col1] = 45852653, [Col2] = FT_GT_DF (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated UPDATE command for Table ‘[dbo].[Table1]’ with Primary Key(s): [Col1] = 45852653, [Col2] = FT_GT_DF (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598

This error surfaces during the replication process, specifically when an UPDATE operation is being replicated from the publisher to the subscriber, and the system cannot locate the target row on the subscriber side to apply the update.

Understanding the Error

Error 20598 is a symptom of a larger issue where data synchronization between the publisher and subscriber has been compromised. This discrepancy can occur for several reasons:

Manual Changes: Direct modifications made to the subscriber that bypass replication can lead to inconsistencies.
Failed Replication Commands: Previous commands that failed to replicate successfully can result in missing data on the subscriber.
Initial Snapshot Issues: Problems with the initial snapshot or schema changes that were not correctly replicated can cause missing rows.
Data Cleanup Scripts: Scripts that unintentionally remove data from the subscriber can also contribute to this error.

Identifying the root cause is crucial for applying the correct resolution strategy.

Resolution

Resolving error 20598 involves ensuring that the data between the publisher and subscriber is synchronized. Here are the steps to troubleshoot and fix the issue:

Validate Subscriptions: Use the Replication Monitor or execute the sp_validatemergepublication stored procedure to check for data inconsistencies between the publisher and the subscriber.
Identify the Missing Row: Confirm that the row indicated in the error message is indeed missing from the subscriber. This verification helps in understanding whether it’s a singular issue or part of a larger synchronization problem.
Reinitialize the Subscription: If the data discrepancy is extensive or the missing row is part of a larger issue, consider reinitializing the subscription. This process involves generating a new snapshot of the publication and reapplying it to the subscriber. Be cautious, as this can be resource-intensive for large datasets.
Manually Apply Missing Data: For isolated incidents where only a few rows are affected, manually inserting or updating the missing data on the subscriber can be a quick fix. Ensure the replication agent jobs are stopped while making manual changes to avoid conflicts.
Investigate and Address the Root Cause: After resolving the immediate error, investigate the root cause of the data inconsistency. This may involve reviewing data modification practices, ensuring all changes go through replication, and setting up alerts for failed replication commands.

To address this challenge comprehensively, including identifying the cause of missing rows on the subscriber, implementing a monitoring strategy is paramount. We will employ a trigger on the publisher’s table to monitor write operations. This approach aids in determining whether rows are being deleted or otherwise modified in a manner that could lead to replication errors. Here’s how to set up the monitoring:

Monitoring Setup

First, create a tracking table to log changes:

CREATE TABLE table1_Tracking
(
    TrackingID INT IDENTITY(1,1) PRIMARY KEY,
    OperationType VARCHAR(10),
    ChangedData NVARCHAR(MAX),
    PreviousData NVARCHAR(MAX),
    ExecutedBy SYSNAME,
    ApplicationName NVARCHAR(128),
    HostName NVARCHAR(128),
    ChangeTime DATETIME DEFAULT GETDATE()
);

Next, establish a trigger to capture insert, update, and delete operations:

CREATE TRIGGER trg_table1_WriteOperations
ON table1
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ExecutedBy SYSNAME = SUSER_SNAME();
    DECLARE @ApplicationName NVARCHAR(128) = APP_NAME();
    DECLARE @HostName NVARCHAR(128) = HOST_NAME();

    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        IF EXISTS (SELECT * FROM deleted)
        BEGIN
            -- Handle UPDATE
            INSERT INTO table1_Tracking(OperationType, ChangedData, PreviousData, ExecutedBy, ApplicationName, HostName)
            SELECT 'UPDATE', 
                   (SELECT * FROM inserted FOR JSON AUTO), 
                   (SELECT * FROM deleted FOR JSON AUTO), 
                   @ExecutedBy, @ApplicationName, @HostName;
        END
        ELSE
        BEGIN
            -- Handle INSERT
            INSERT INTO table1_Tracking(OperationType, ChangedData, ExecutedBy, ApplicationName, HostName)
            SELECT 'INSERT', 
                   (SELECT * FROM inserted FOR JSON AUTO), 
                   @ExecutedBy, @ApplicationName, @HostName;
        END
    END
    ELSE
    BEGIN
        -- Handle DELETE
        INSERT INTO table1_Tracking(OperationType, PreviousData, ExecutedBy, ApplicationName, HostName)
        SELECT 'DELETE', 
               (SELECT * FROM deleted FOR JSON AUTO), 
               @ExecutedBy, @ApplicationName, @HostName;
    END
END;

This trigger captures the nature of the operation (insert, update, delete), the data before and after the change, who executed the operation, from which application, and from which host. This comprehensive tracking enables administrators to analyze patterns that could lead to replication errors, such as unauthorized deletions or updates that bypass replication mechanisms.

Conclusion

Error 20598 in SQL Server replication highlights the challenges of maintaining data consistency across distributed databases. By understanding the underlying causes and meticulously applying the appropriate resolution steps, database administrators can ensure the integrity and reliability of their replication setup. Remember, the key to effectively managing replication errors lies in proactive monitoring, thorough investigation, and understanding the intricacies of SQL Server’s replication mechanisms.

Thank You,
Vivek Janakiraman

Posted by

Vivek Janakiraman

Posted on

February 17, 2024

Posted under

Replication

Comments

Proactively Managing Transactional Replication Latency with SQL Server

Transactional replication is a critical component of many SQL Server environments, providing high availability, load balancing, and other essential benefits. However, managing replication latency, the delay between an action occurring on the publisher and it being reflected on the subscriber, is vital for ensuring system performance and data integrity. In this blog post, we’ll explore a proactive approach to monitor and alert on replication latency, helping database administrators (DBAs) maintain optimal system health.

The Issue:

Replication latency can sometimes go unnoticed until it impacts the system performance or data accuracy, leading to potential data loss or business disruptions. Traditional monitoring techniques may not provide real-time alerts or may require significant manual intervention, making them less effective for immediate latency identification and resolution.

The Script:

To address this challenge, we introduce a SQL script designed by Vivek Janakiraman from JBSWiki, specifically crafted to monitor transactional replication latency in SQL Server environments. This script efficiently posts tracer tokens to specified publications and measures the time taken for these tokens to move through the replication components, providing a clear picture of any latency present in the system.

/*
Author: Vivek Janakiraman
Company: JBSWiki
Description: This script is used to alert in case there is Transactional replication Log reader or distribution agent latency.
             It posts tracer tokens to specified publications and measures the latency to the distributor and subscriber.
*/

-- Switch to the publisher database to insert tracer tokens.
USE [Publisher_Database_Here]   -- Use your publisher database name here.
-- Insert tracer tokens into the specified publications.
EXEC sys.sp_posttracertoken @publication = 'Publication_Name' -- Change appropriate Publication that should be monitored.
EXEC sys.sp_posttracertoken @publication = 'Publication_Name1' -- Change appropriate Publication that should be monitored.
-- Wait for 5 minutes to allow the tokens to propagate.
WAITFOR DELAY '00:05:00'

-- Switch to the distribution database to query latency information.
USE distribution
;WITH LatestEntries AS (
       -- Select the latest entries for each publication and agent.
       SELECT publication_id, agent_id, MAX(publisher_commit) AS MaxDate       
       FROM MStracer_tokens t
       JOIN MStracer_history h ON t.tracer_id = h.parent_tracer_id
       GROUP BY publication_id, agent_id
)
-- Select latency information for the latest tokens.
SELECT c.name, t.publication_id, h.agent_id, t.publisher_commit,
       ISNULL(DATEDIFF(s,t.publisher_commit,t.distributor_commit), 299) as 'Time To Dist (sec)',
       ISNULL(DATEDIFF(s,t.distributor_commit,h.subscriber_commit), 299) as 'Time To Sub (sec)'
INTO #REPL_LATENCY
FROM MStracer_tokens t 
JOIN MStracer_history h ON t.tracer_id = h.parent_tracer_id
JOIN distribution.dbo.MSdistribution_agents c ON h.agent_id = c.id
JOIN LatestEntries le ON t.publication_id = le.publication_id AND h.agent_id = le.agent_id AND t.publisher_commit = le.MaxDate
ORDER BY t.publisher_commit DESC

-- Check if there is any latency beyond acceptable limits and select those records.
IF EXISTS (SELECT 1 FROM #REPL_LATENCY WHERE ([Time To Dist (sec)] > 30 OR [Time To Sub (sec)] > 30))
BEGIN
    SELECT name, publication_id, agent_id, publisher_commit, [Time To Dist (sec)], [Time To Sub (sec)]
    INTO #REPL_LATENCY_Email 
    FROM #REPL_LATENCY 
    WHERE ([Time To Dist (sec)] > 30 OR [Time To Sub (sec)] > 30)
END

-- Prepare the HTML body content for the email alert.
DECLARE @body_content NVARCHAR(MAX);
SET @body_content = N'
<style>
table.GeneratedTable {
  width: 100%;
  background-color: #D3D3D3;
  border-collapse: collapse;
  border-width: 2px;
  border-color: #A9A9A9;
  border-style: solid;
  color: #000000;
}
table.GeneratedTable td, table.GeneratedTable th {
  border-width: 2px;
  border-color: #A9A9A9;
  border-style: solid;
  padding: 3px;
}
table.GeneratedTable thead {
  background-color: #A9A9A9;
}
</style>
<table class="GeneratedTable">
  <thead>
    <tr>
         <th>name</th>
         <th>publication_id</th>
         <th>agent_id</th>
         <th>publisher_commit</th>
         <th>[Time To Dist (sec)]</th>
         <th>[Time To Sub (sec)]</th>
    </tr>
  </thead>
  <tbody>' +
CAST(
          (SELECT td = name, '',
                               td = publication_id, '',
                               td = agent_id, '',
                               td = publisher_commit, '',
                               td = [Time To Dist (sec)], '',
                               td = [Time To Sub (sec)], ''
        FROM [dbo].#REPL_LATENCY_Email
        FOR XML PATH('tr'), TYPE   
        ) AS NVARCHAR(MAX)
    ) +
  N'</tbody>
</table>';

-- Send an email alert if there is any latency issue found.
IF EXISTS (SELECT TOP 1 * FROM [dbo].#REPL_LATENCY_Email) 
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'JBSWIKI',
                                 @body = @body_content,
                                 @body_format = 'HTML',
                                 @recipients = 'jvivek2k1@yahoo.com',
                                 @subject = 'ALERT: Transactional Replication Latency Alert'; 
END

-- Cleanup temporary tables.
DROP TABLE #REPL_LATENCY
DROP TABLE #REPL_LATENCY_Email

The Solution:

The script works by first posting tracer tokens to the specified publications within the publisher database. It then waits for a predetermined amount of time (defaulted to 5 minutes in the script) to allow the tokens to propagate through the system. Following this, the script measures the latency to the distributor and subscriber, providing a detailed report of the time taken in each stage of the replication process.

This information is then used to generate an HTML-formatted email alert if the latency exceeds predefined thresholds (30 seconds in the provided script), allowing for immediate action to be taken. The use of HTML formatting in the email ensures that the information is presented in an easily digestible format, facilitating quick understanding and response by the DBA.

Conclusion:

Proactive monitoring and management of transactional replication latency are paramount for maintaining the health and performance of SQL Server environments. The script provided offers a straightforward and effective solution for DBAs to stay ahead of potential replication issues. By automating the process of latency detection and alerting, this approach not only saves valuable time but also helps in preventing the negative impact of replication latency on business operations.

Remember, while this script serves as a valuable tool in your monitoring arsenal, it’s also important to tailor the solution to your specific environment and requirements. Regularly reviewing and adjusting the latency thresholds and monitoring frequency will ensure you continue to get the most out of your replication setup.

Thank You,
Vivek Janakiraman