• sandy-833685 (12/1/2016)


    AlertsSize Bytes Per Row

    dbo.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