How update works in transactional replication?

In a post about how update works I showed what happened when run an update with the same values. SQL Server is smart enough to see that and not changing anything and register minimum log.

Another day I saw a comment to avoid updating records when none of the values are changing.

Avoid updating records when none of the values are changing. This still does a write and, if the table is in replication or change tracking, still causes the row to be propagated out to other servers. If you are updating a potentially large number of records, make sure to only update the ones where the new value doesn’t equal the old value.

Let’s see how the update behavior when update 10 millions rows but without any change and see if will be any row propagation to another server.


My Lab contains two servers and a demo database replicated from SQL01 to SQL02.

It’s configured a transaction replication to send the articles and I’m using a table created with the code:

CREATE TABLE tblMillionsRows (
    smallColumn NVARCHAR(150) DEFAULT 'DEMO',
    dateColumn DATETIME

Inserting rows:

INSERT INTO dbo.tblMillionsRows (
GO 2000000

It will take a while to insert all rows. The next step is add this table in the replication.

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'SQL01_demo_tb01'; 
SET @table = N'tblMillionsRows';
SET @schemaowner = N'dbo';

EXEC sp_addarticle 
	@publication = @publication, 
	@article = @table, 
	@source_object = @table,
	@source_owner = @schemaowner, 
	@schema_option = 0x80030F3,
	@vertical_partition = N'true', 
	@type = N'logbased',
	@filter_clause = @filterclause;

EXEC sp_articlecolumn 
	@publication = @publication, 
	@article = @table;

EXEC sp_startpublication_snapshot 
	@publication = 'SQL01_demo_tb01', 
	@publisher =  'SQL01'   

With all set, let’s do some tests. First updating the heap table and seeing if something is replicated.

UPDATE tblMillionsRows SET smallColumn = N'DEMO' WHERE id < 10000

After the update, I ran the script below to see the transactions and commands available to replicate and nothing changed.

SELECT * FROM distribution.dbo.MSrepl_Commands 
SELECT * FROM distribution.dbo.MSrepl_Transactions 
EXEC sp_browsereplcmds 

But, when I change one row running the update below I could see a new command to replicate in the another server.

UPDATE tblMillionsRows SET smallColumn = N'DEMO1' WHERE id < 10000


In conclusion, SQL Server won’t replicate updates that don’t change the value. You can see more about updates in my post.

Be careful when doing updates, because if the table is replicated and you change millions rows the transaction replication will create one command for each updated row.


Moving the msdb, model, and tempdb databases files

All system databases, except the resource database, can be moved to new locations to help balance I/O load.

To move the msdb, model, and tempdb databases, perform the following steps:

  • For each file to be moved, execute the ALTER DATABASE … MODIFY FILE statement.
  • Stop the instance of SQL Server.
  • Move the files to the new location (this step is not necessary for tempdb, as its files are recreated automatically on startup).
  • Restart the instance of SQL Server.

The process for moving the master database is different from the process for other databases. To move the master database, perform the following steps:

  • Open SQL Server Configuration Manager.
  • In the SQL Server Services node, right-click the instance of SQL Server, click Properties, and then click the Startup Parameters tab.
  • Edit the Startup Parameters values to point to the planned location for the master database data (-d parameter) and log (-l parameter) files.
  • Stop the instance of SQL Server.
  • Move the master.mdf and mastlog.ldf files to the new location.
  • Restart the instance of SQL Server


TempDB summary


The new tempdb tab in SQL server

Tempdb is a special database available as a resource to all users of a SQL Server instance, you use it to hold temporary objects that users, or the database engine, create.

In many respects, tempdb files are identical to the files that make up other SQL Server databases. From the perspective of storage I/O, tempdb uses the same file structure as a user database one or more data files and a log file. The arrangement of data pages within tempdb data files is also based on the same architecture as user databases.
Unlike all other databases, SQL Server recreates the tempdb database each time the SQL Server service starts. This is because tempdb is a temporary store.
There are three primary ways that the organization of tempdb files can affect system performance:

  • Because users and the database engine both use tempdb to hold large temporary objects, it is common for tempdb memory requirements to exceed the capacity of the buffer pool in which case, the data will spool to the I/O subsystem. The performance of the I/O subsystem that holds tempdb data files can therefore significantly impact the performance of the system as a whole. If the performance of tempdb is a bottleneck in your system, you might decide to place tempdb files on very fast storage, such as an array of SSDs.
  • Although it uses the same file structure, tempdb has a usage pattern unlike user databases. By their nature, objects in tempdb are likely to be short-lived, and might be created and dropped in large numbers. Under certain workloads especially those that make heavy use of temporary objects this can lead to heavy contention for special system data pages, which can mean a significant drop in
    performance. One mitigation for this problem is to create multiple data files for tempdb; this is covered in more detail in the next topic.
  • When SQL Server recreates the tempdb database following a restart of the SQL Server service, the size of the tempdb files returns to a preconfigured value. The tempdb data files and log file are configured to autogrow by default, so if subsequent workloads require more space in tempdb than is currently available, SQL Server will request more disk space from the operating system. If the initial
    size of tempdb and the autogrowth increment set on the data files is small, SQL Server might need to request additional disk space for tempdb many times before it reaches a stable size.

Difference between Lock and Latch

Locks everywhere is a good start to understand how SQL Server provides logical consistency. Every operation has a lock and latch.

What does Latch mean? Latch protects memory on Buffer Pool, is a method that provides physical consistency.

SQL Server does operations in memory, that means, it read the page from disk and put that page on buffer pool to work there. If someone tries to update the data, the page is changed in memory and SQL Server writes the change in the transaction log file. (More about logging)

Basically, Latches are physical locks and hold the lock only for the duration of the physical operation, while the Locks are logical and maintain the lock until the transaction finishes. Both types guarantee data consistency.


Locks everywhere

LOCKSIn this post I’m going to talk about locks on SQL Server. Locks are necessary, they are used in all operations in the database. Don’t get confused about blocking, locking and blocking are totally different.

When we talk about lock, doing something in the database, like an update and select though will cause a type of lock. The select stantement has a lock operation called shared lock. This means you can share reads with someone else and that may not cause blocks.

SQL Server has different kinds of lock modes, such as (S) Shared, (U) Update, (X) Exclusive, (I) Intent (Sch) Schema, Bulk Update and Key-Range.

  • (S) Shared lock is used in read operations.
  • (U) Update to avoid potential deadlock problem.
  • (X) Exclusive prevent access to a resource by concurrent transactions.
  • (I) Intent prevent other transactions from modifying the higher-level resource and improve the efficiency of the Database Engine in detecting lock conflicts at the higher level.
  • (Sch) Schema uses schema modification (Sch-M) locks during a table data definition language (DDL) operation.

The following table shows the compatibility of the most commonly encountered lock modes.

Existing granted mode
Requested mode IS S U IX SIX X
Intent shared (IS) Yes Yes Yes Yes Yes No
Shared (S) Yes Yes Yes No No No
Update (U) Yes Yes No No No No
Intent exclusive (IX) Yes No No Yes No No
Shared with intent exclusive (SIX) Yes No No No No No
Exclusive (X) No No No No No No


VLF (Virtual Log Files)


To see how many VLFs you have solely look at the number of rows returned by DBCC LOGINFO.

The size and number of VLFs you’ll have depends largely on the size that the chunk is when it’s added to you transaction log.

There is no general rule how to determine the best values for the auto-growth option, as these vary from case to case. Having too many or too little virtual log files causes bad performance.

Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur.

Most of the time excessive VLF fragmentation is brought about by excessive file growth at small intervals. For example, a database that is set to grow a transaction log file by 5mb at a time is going to have a large number of VLFs should the log decide to grow.

Growth Number of VLFs created
<= 64Mb 4
>64 but <=1Gb 8
>1Gb 16

There is insufficient system memory in resource pool

Doing crash and recovery tests on my local machine I got the SQL Server instance not going online. After trying the third time to bring my instance online thinking was something else problem I saw the SQL Server errolog file and I could see the problem.

Not enough memory, but wasn’t on my machine, was in the resource pool. So, what is a resource pool?

A resource pool represents a subset of the physical resources of an instance of the Database Engine and in my case was insufficient memory. Let’s see the errorlog file:

2018-03-14 16:19:58.09 spid56s     [ERROR] Recovery failed with error 0x83000000 on database 18. This error will be mapped to 'HK_E_RESTORE_INSUFFICIENT_MEMORY' (0x8200002e). (sql\ntdbms\hekaton\runtime\src\hkruntime.cpp : 4805 - 'HkRtRestoreDatabase')
2018-03-14 16:19:58.09 spid34s     [INFO] HkCkptCtrlUninitialize(): Database ID: [18]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '158'
2018-03-14 16:19:58.09 Server      Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.09 Server      Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.09 Server      Error: 28709, Severity: 16, State: 19. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.09 spid37s     Error: 701, Severity: 17, State: 137.
2018-03-14 16:19:58.09 spid37s     There is insufficient system memory in resource pool 'default' to run this query.
2018-03-14 16:19:58.09 spid39s     Error: 701, Severity: 17, State: 137.
2018-03-14 16:19:58.09 spid39s     There is insufficient system memory in resource pool 'default' to run this query.
2018-03-14 16:19:58.11 spid55s     [ERROR] Recovery failed with error 0x83000000 on database 15. This error will be mapped to 'HK_E_RESTORE_INSUFFICIENT_MEMORY' (0x8200002e). (sql\ntdbms\hekaton\runtime\src\hkruntime.cpp : 4805 - 'HkRtRestoreDatabase')
2018-03-14 16:19:58.11 spid31s     [INFO] HkCkptCtrlUninitialize(): Database ID: [15]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '155'
2018-03-14 16:19:58.20 spid31s     SQL Server shutdown has been initiated
2018-03-14 16:19:58.21 spid31s     Error: 19032, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.28 spid34s     SQL Server shutdown has been initiated

After starting the service SQL Server was doing the redo and undo process, this means it was reading the log files, create the compensate log records if was found any uncommitted transaction.

SQL Server will need memory in buffer pool to complete the redo and undo process and I didn’t remember I changed any SQL Server memory configuration.

So, my approach was to connect SQL Server via command line while the instance was still up and run sp_configure to see how much memory was configured. I got only 512mb set for Max Server Memory and that was the problem. (Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR memory).

In my environment with 26 databases and my crash recovery tests, 512mb for my pool memory wasn’t enough and when I changed the configuration to 4096mb I could bring the instance online again.


First, read the errorlog file to have more information what SQL Server is doing and also know transaction log operations, log records, checkpoints and how crash  recovery works is fundamental.

SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually still represents the largest portion of memory consumed by SQL Server.


Wait wait wait…

Let’s talk about why we have to wait and how to understand the wait types.

Paul Randal in his post Wait statistics, or please tell me where it hurts said:

A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called being RUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it’s resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed

That’s explain a lot, because the SQL Server threads doesn’t run all in the same time. A good example is when our query is doing physical reads. The IO subsystem is the slowest part of our resources and probably will take some time if the query is reading gigabytes of data.


After the CPU request the data from the disk, the disk will run for it, but before send the data back. All data need to go to memory first and that may don’t have the necessary space.  The thread is going to wait until some resources been released first. There are many scenarios, for example, how many threads are running this query? How long will take to the application to show that data?

So, every time a thread needs to wait for a resource it will increase a wait time type, such as PAGEIOLATCH_XX , PAGELATCH_XX, ASYNC_NETWORK_IO, CXPACKET, RESOURCE_SEMAPHORE. I will talk more about waits in the next posts.

SQL Server Undocumented 1

SQL Server has many undocumented functions and commands. I will write series of posts with functions to have a library online.

The first, I’d like to write about %%PhysLoc%% and the function fn_PhyslocCracker

With this function, I can see my table physical location and use DBCC PAGE to see the data in each page.

FROM dbo.MyTable AS m
CROSS APPLY fn_PhyslocCracker(%%physloc%%) plc;