\nThis post idea was prompted by a discussion I had this week with Jonathan Kehayias about an environment that had multiple transactional replication publications defined with overlapping table articles.  <\/span><\/font><\/font>\n<\/p>\n

\n<\/span>In other words, a table was defined as an article in more than one publication.<\/font><\/font>\n<\/p>\n

\nWhile I can think of some cases where you would want to leverage different article options or filters, in this particular case the articles had no<\/em> differences in how they were defined.  <\/span>I’ve seen this in other environments in the past – and as I recall it wasn’t a conscious decision, but rather a lack of coordination across application teams and projects. <\/font><\/font>\n<\/p>\n

\nFor small databases with lower volumes of modifications, this overlap could likely go unnoticed.   <\/span>For larger tables with high amounts of data modifications, well, consider the following scenario:<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>You have two transactional replication publications that each reference the same table as an article.  <\/span>No other article properties are changed between the two publications and articles.<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>Each publication maps to a single subscriber.<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>Your table article setting for this scenario use the default – propagating INSERTs, UPDATEs and DELETEs via the default statement delivery method (spMSins_ \/ sp_MSupd_ sp_MSdel) etc. (And while we are propagating changes made directly to the table, we’re not using stored procedure execution articles.<\/em>)<\/font><\/font>\n<\/p>\n

\nSo let’s say we execute the following single statement batch update against the redundantly published table.  <\/span>This is one statement that updates 3,120 rows:<\/font><\/font>\n<\/p>\n

\n<\/font>\n<\/p>\n

\nUPDATE<\/font><\/font><\/span> dbo.<\/font><\/span>charge<\/font><\/span><\/font>\n<\/p>\n

\nSET<\/font><\/font><\/span> charge_amt =<\/font><\/span> charge_amt *<\/font><\/span> .97<\/font><\/span><\/font>\n<\/p>\n

\nWHERE<\/font><\/font><\/span> provider_no =<\/font><\/span> 386;<\/font><\/span><\/font>\n<\/p>\n

\n<\/font><\/font>\n<\/p>\n

\n<\/font>\n<\/p>\n

\nIf we used sp_replcmds in the publisher database (I had the log reader agent job stopped in order to step through the scenario), how many command transactions would you expect to see marked for replication?<\/font><\/font>\n<\/p>\n

\nThe answer is – 6,240<\/u><\/em>.  <\/span>One call per row updated, multiplied by two separate publications (and we’re still only in the publication database):<\/font><\/font>\n<\/p>\n

\n<\/font>\n<\/p>\n

\n<\/a><\/span>\n<\/p>\n

\n<\/span>\n<\/p>\n

\nAnd as you may expect, those 6,240 rows move on to the distribution database (you can validate via  <\/span>sp_browsereplcmds or MSrepl_commands):<\/font><\/font>\n<\/p>\n

\n<\/font>\n<\/p>\n

\n<\/font>\n<\/p>\n

\n<\/a><\/span>\n<\/p>\n

\n<\/span>\n<\/p>\n

\nNow had you instead just created ONE publication with that article sent to the two different subscribers, you would see just 3,120 in the publication database for the original update – and 3,120 as well at the distributor prior to multicasting the update to the two subscribers.<\/font><\/font>\n<\/p>\n

\nCoupled with the already “chatty” nature of transactional replication – you can imagine scenarios where performance rapidly degrades for large batch updates, particularly on already-constrained topologies. <\/font><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

This post idea was prompted by a discussion I had this week with Jonathan Kehayias about an environment that had multiple transactional replication publications defined with overlapping table articles.  In other words, a table was defined as an article in more than one publication. While I can think of some cases where you would want […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,30],"tags":[],"class_list":["post-506","post","type-post","status-publish","format-standard","hentry","category-performance","category-replication"],"yoast_head":"\n