info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



T-SQL Column Data Values placed into Bucket Ranges and Aggregated Totals
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-02-08







Solution:


Create Procedure dbo.spaTRepTRXinDistroReport
as

Declare @Total decimal(18,3)
set @Total = (Select count(*) from [dbo].[LOG_TRANSACTIONAL_REPLICATION_TRXINDISTRO])

select t.TrxinDistRange as [Transactions in Distriution], count(*) as [Number of Occurences], convert(decimal(18,3),count(*) / @Total) * 100 as '%'
from (
select
case when trxindistro = 0 then '[Group 1]: 0 TRX'
when trxindistro > 0 and trxindistro < 101 then '[Group 2]: 1-100 TRX'
when trxindistro > 100 and trxindistro < 501 then '[Group 3]: 100-500 TRX'
when trxindistro > 500 and trxindistro < 1001 then '[Group 4]: 500-1000 TRX'
when trxindistro > 1000 and trxindistro < 5001 then '[Group 5]: 1000-5000 TRX'
when trxindistro > 5000 and trxindistro < 10001 then '[Group 6]: 5000-10000 TRX'
when trxindistro > 10000 and trxindistro < 50001 then '[Group 7]: 10000-50000 TRX'
when trxindistro > 50000 and trxindistro < 100001 then '[Group 8]: 50000-100000 TRX'
when trxindistro > 100000 and trxindistro < 1000001 then '[Group 9]: 100000-1000000 TRX'
else 'Over 1000000 TRX' end as TrxinDistRange
from [dbo].[LOG_TRANSACTIONAL_REPLICATION_TRXINDISTRO]
) t
group by t.TrxinDistRange
order by TrxinDistRange



Image #1:





i.e. 88.5% of the time there are 0 Transactions in Distribution.
Note that this is based on a 5-min logging interval for the Distribution database compared to the Subscriber database.