As I already mentioned before Replication is using replication procedures in order to replicate data changes Today I will focus on that how the changes can be send down to subscriber, especially for UPDATE.
By default these start with sp_MSins_%, sp_MSupd_%, sp_MSdel_%. You may already seen CALL or SCALL associated with these replication procedures. You can see these in GUI under articles properties:
or by just selecting from sysarticles on Publisher db or msarticles on distribution db:
You can change these either by drop down list in SSMS in article properties or by using sp_changearticle stored procedures (executed on Publisher)
The most interesting in that is the UPDATE as that DML you can send down in few different ways. I will focus on two most common ones CALL and SCALL.
Lets go into more detail…
SCALL is default setting when you setting up the publication and adding articles into it. The update will pass only columns that have been changed, including PK values and lastly bitmask with information about what columns has been changed. Here is an example how this looks like in distribution database (Browse replication commands).
Updated one column in TableD:
After browsing distribution db we can find this:
As you can see the first parameter is NULL, this is the column that has not been changed. Here is how the procedure looks like on Subscriber:
You can see how bitmask is used to determine if the column actual changed or no.
Firs thing lets change SCALL to CALL of TableD. You can do it in SSMS by going to Publication Properties and then to Article properties or just run T-SQL like that one:
After you run this you do not need to run snapshot. New update procedure will be delivered as normal replicated commands and now the code for update procedure looks like this:
As you can see no bitmap mask just simple update to ALL columns in tables and proc does not check if values has changed.
Now lets see how the update looks like in the distribution db. As before we just updating one column in that table:
If you check distribution database you will see this:
As you can see it does provide all columns and values even if these has not changed. It will always send everything.
CALL vs SCALL
Now you wonder why this is important? Imagine you have large table with 30 columns some big ones like varchar(max) etc. Your application updating only one column in that table…. How do you think this will affect your distribution db? Lets have a look.
I cleaned up distribution db and will populate 10 column table with 1 million rows. Then update that 1 million rows first with SCALL and then with CALL settings.
Table Structure looks like that:
After creating I will populate table with random data and then publish it. Once Snapshot of TableA wth 1 million rows delivered to Subscriber I am checking space of msrepl_commands on distribution database for that Publication (nothing else is getting into that distribution db at this point).
Right now msrepl_commands is 35 rows and 40KB reserved. It contains only initial snapshot. Article is set to use SCALL for updates. Lets send now 1 million updates just for one column, Col4 which is just INT. I will increase that column by number 100 that is it.
1 million SCALL updates sent down
After sending 1 million updates with SCALL my msrepl_commands table went to: 1000035 rows and 183368 KB reserved for that table.
Lets change article to normal CALL and lets send 1 million updates for Col5.
1 million CALL updates sent down
Now my msrepl_commands table went to 2000088 rows and reserved 759624 KB on drive. Just to explain why row count is not 2000035… it is because I changed article using sp_changearticle which added few rows still very small ones (should not take more than 40 KB)
As you can see using normal CALL will take way more space inside your distribution db
CALL = 576 256 KB
SCALL = 183 328 KB
Same transactions but with CALL we are using almost 3 times more of storage. Before you change default SCALL setting to CALL think twice and check storage on your distributor.
If you have any questions just use comments section.
Did you use CALL in some cases and if yes to what were these? What setting do you normally use for UPDATES?