\nConsider the following scenario:<\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>You have Transactional Replication deployed <\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>Data is flowing, but just not as fast as you would like <\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>This scenario could apply to local\/remote distributors and push\/pull subscribers<\/font><\/font>\n<\/p>\n \nThere are several different techniques we can use to narrow down where the replication performance issue is happening. <\/span>Perhaps you’ve already found that the performance issue is happening for log reader reads or distribution database writes. <\/span>Or perhaps you suspect the issue is on the subscriber?<\/font> <\/font><\/span><\/font>\n<\/p>\n \nWhile the various replication techniques can help us narrow down the lagging member of the topology, I still would like more visibility into why a particular agent read or write process is performing more slowly. <\/span>Fortunately, you can do this in SQL Server 2008+…<\/font><\/font>\n<\/p>\n \nIn the following example, I’ll start by retrieving the session IDs of the log reader and distribution agents (and as an aside my replication topology is SQL Server instance version 10.50.2500):<\/font><\/font>\n<\/p>\n \n— Log Reader<\/font><\/font><\/span><\/span>\n<\/p>\n \nSELECT<\/font><\/font><\/span> <\/span>session_id<\/font><\/span>,<\/font><\/span> program_name<\/font><\/span><\/font>,<\/font><\/span><\/span><\/font>\n<\/p>\n \n <\/font><\/span>reads<\/font><\/span><\/font>,<\/font><\/span><\/font><\/span>\n<\/p>\n \n <\/font><\/span>writes<\/font><\/span><\/font>,<\/font><\/span><\/font><\/span>\n<\/p>\n \n <\/font><\/span>logical_reads<\/font><\/span><\/font><\/span>\n<\/p>\n \nFROM<\/font><\/font><\/span> sys<\/font><\/span>.<\/font><\/span><\/font>dm_exec_sessions<\/font><\/span><\/span><\/font>\n<\/p>\n \nWHERE<\/font><\/font><\/span> original_login_name<\/font><\/span> =<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n \n <\/font><\/span>'SQLSKILLS\\SQLskillsLogReaderAG'<\/font><\/span><\/font>;<\/font><\/span><\/font><\/span>\n<\/p>\n \n<\/font><\/font><\/span>\n<\/p>\n \n— Distribution Agent<\/font><\/font><\/span><\/span>\n<\/p>\n \nSELECT<\/font><\/font><\/span> <\/span>session_id<\/font><\/span>,<\/font><\/span> program_name<\/font><\/span><\/font>,<\/font><\/span><\/span><\/font>\n<\/p>\n \n <\/font><\/span>reads<\/font><\/span><\/font>,<\/font><\/span><\/font><\/span>\n<\/p>\n \n <\/font><\/span>writes<\/font><\/span><\/font>,<\/font><\/span><\/font><\/span>\n<\/p>\n \n <\/font><\/span>logical_reads<\/font><\/span><\/font><\/span>\n<\/p>\n \nFROM<\/font><\/font><\/span> sys<\/font><\/span>.<\/font><\/span><\/font>dm_exec_sessions<\/font><\/span><\/span><\/font>\n<\/p>\n \nWHERE<\/font><\/font><\/span> original_login_name<\/font><\/span> =<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n \n <\/font><\/span>'SQLSKILLS\\SQLskillsDistAGT'<\/font><\/span><\/font>;<\/font><\/span><\/font><\/span>\n<\/p>\n \n<\/font><\/font>\n<\/p>\n \nIn this example I’m using separate accounts to run the agent executables, however I could have also added a predicate on program_name based on the publication I was interested in evaluating. <\/span>For example, I could have said for the Log Reader agent – program_name = ‘Repl-LogReader-0-AdventureWorks2008R2-6’<\/em> and for the Distribution agent – that’s more interesting, as we have program_name = ‘CAESAR-AdventureWorks2008R2-Pub_AW_2008R2-AUGUSTUS-1’<\/em> (subscriber is AUGUSTUS, publisher is CAESAR). <\/span>But if you just used that program name, you won’t get Replication Distribution History session, which would be program_name = ‘Replication Distribution History’<\/em> and may also be interesting.<\/font><\/font>\n<\/p>\n \nSo in my example, I have 5 different sessions I’m interested in (and yours will vary based on the number of published databases, independent agents, server role, etc):<\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>The log reader agent was using sessions 55, 57, 59<\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>The distribution agent had two sessions (61 for history and 62 for the executable)<\/font><\/font>\n<\/p>\n \nNow that I have my session ids, I’m going to create an extended events session that I can run during the “slow performing” period to help illuminate where to investigate next (and for more general discussion on this technique, see Paul Randal’s post “<\/font><\/font>Capturing wait stats for a single operation<\/a>