Here is a tip I found while troubleshooting a Distribution Agent latency problem.  We were trying to see why on some days the Distribution Agent was "getting behind".  We suspected it was volume related.  Below are a few queries to help uncover workload in the Distribution database.

The query below returns count of transactions.  It's not fancy, but it helps identify days with higher then average counts.

--Return count of transactions by day by publication

select datepart(dayofyear,entry_time) as 'day of year',

publisher_database_id as 'pub db id',

count(*) as 'count of trans' from MSrepl_transactions

where entry_time < getdate() - 5 --count trans older then 5 days

group by datepart(dayofyear,entry_time), publisher_database_id

order by datepart(dayofyear,entry_time), publisher_database_id

 

day of year pub db id   count of trans

----------- ----------- --------------

302         7           2291

303         7           319369

304         7           420476

305         7           4633313

306         7           411236

307         7           459866

308         7           755705

309         7           937608

310         1           57326

310         2           18950

310         7           462553

310         8           122002

311         1           38203

311         2           24254

311         7           257801

311         8           105436

--Chris Skorlinski