sandy-833685 (12/1/2016)
AlertsSize Bytes Per Rowdbo.IRST6553.6
dbo.Ulog5461.33333
dbo.VAE8192
dbo.IRR8192
dbo.Rdata8192
dbo.InfoData81920
dbo.IRTrans49152
dbo.IRC16384
dbo.Transactions65536
dbo.ARPin16384
dbo.IRRPT32768
Total Bytes298734.9333
Above is the details size in bytes for per transactions which and total size comes approx - 300 KB, Yes Every table has clustered and Index along with one foreign key each, and there is no trigger on these tables.
This is too much. If it's measured fine than your tables are exceptional. My Transactions table has 89 columns and the average record size is about 809 Bytes, and yours is 80 times mine Transactions table.
Can you please run this query in a new query of your database and share the results:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select
cast(db_name(ps.database_id)+'.'+object_name(ps.object_id)+'.'+isnull(i.name,'heap') as varchar(60)) as db_table_index_name,
sum(ps.record_count) as sum_record_count,
avg(ps.max_record_size_in_bytes) as avg_record_size_in_bytes,
max(ps.max_record_size_in_bytes) as max_record_size_in_bytes
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') as ps --must DETAILED
left join sys.indexes as i on i.object_id = ps.object_id and i.index_id = ps.index_id
where object_name(ps.object_id) in ('Transactions')
group bydb_name(ps.database_id), object_name(ps.object_id), i.name
order bydb_name(ps.database_id), object_name(ps.object_id), i.name;
Igor Micev,My blog: www.igormicev.com