John Liu Blog

Every drop counts

SQL Server Extended Events

Following query provides the name mapping between trace and extended events. Not all event classes have equivalent Extended Events events. SELECT te.name ,xe.package_name ,xe.xe_event_name FROM sys.trace_events AS te LEFT JOIN sys.trace_xe_event_map AS xe ON te.trace_event_id = xe.trace_event_id WHERE xe.trace_event_id IS NOT NULL

Posted by John Liu Wednesday, March 1, 2023

SQL Server Session Settings

Following SQL Server session settings impact on if an existing plan in cache can be used or not. Only if the session settings are identical to the settings for the cached plan, the plan can then be reused; otherwise, it will be recompiled. Different setting may impact performance at execution. ANSI_DEFAULTS ANSI_NULL_DFLT_ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT For stored procedure, the ANSI_NULLS and QUOTED_IDENTIFIER settings will be used are the settings used when the stored procedure is created and runtime settings are irrelevant.

Posted by John Liu Tuesday, January 24, 2023

SQL Server Replication

There a 4 type of replications: Snapshot replication Transactional replication Peer-to-Peer replication Merge replication Peer-to-Peer Replication Column/FK/Constraint changes are replicated but trigger/index changes are not replicated. Changes are replicated almost instantly. Table with timestamp column is not supported, no work around. Identity column needs to be managed carefully. When setup replication, identity column will automatically have the NOT FOR REPLICATION option set to YES. Don’t set the option to NO (even though you can via script after replication setup).

Posted by John Liu Tuesday, September 6, 2022

Remove SQL Server Replication

Following script helps to cleanly remove all replication objects configured on the publisher server. ONLY run this script if you want to completely remove entire replication set. DECLARE @distributionDB SYSNAME = N'distribution' -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. default is distribution ,@publisher SYSNAME = N'' -- CHANGE THIS TO THE PUBLISHER NAME. ,@publicationDB SYSNAME = N'' -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. ,@subscriber SYSNAME = N''; -- CHANGE THIS TO THE SUBSCRIBER NAME, comma separated if more than one subscriber.

Posted by John Liu Monday, September 5, 2022

Linked Server DTC

When run query involve transactions using Linked Server, you will need to enable Network DTC Access for MS DTC transaction on both servers. If not configured, you may receive following error message: “The partner transaction manager has disabled its support for remote/network transactions” (if not enabled on remote server) “The transaction manager has disabled its support for remote/network transactions” (if not enabled on the local server) “The operation could not be performed because OLE DB provider “MSOLEDBSQL” for linked server “LinkedServer” was unable to begin a distributed transaction”

Posted by John Liu Friday, July 22, 2022