November 30, 2016 at 6:05 am
Hi,
I am having an transaction database in SQL 2012 which is under UAT, Initially there use to 10-15 insert in transaction table along with 7 referencing tables in per second i.e 7x15=105 records suddenly now the transactions has increased to 1k/sec and for a day there is approx 50K transactions per day, If I execute sp_spaceused to get the size of data for each transactions it gives me some approximately 300KB/transaction data, and if i calculate the size for 50k transactions it gives me approx 14 GB i.e 50000 X 300 for a day, if am looking at data retention for 6 months it will come to approx 2.4 tera, can some please help me know how to get the IOPS calculations for processing 1K transaction/sec considering 80% write and 20% read. And also what should be consider in DB while designing.
November 30, 2016 at 6:13 am
First, is your calculation exact? You should re-check it, or correct me if I'm wrong.
300 KB of data for one transaction (with referencing data in 7 other tables) is too much. Check out this again.
For example, I have a table Transactions with transaction record which is about 1/10 of page size (8060 bytes) or around 800 Bytes per transaction/record.
Igor Micev,My blog: www.igormicev.com
November 30, 2016 at 6:27 am
I'm with Igor. Using sp_spaceused to determine OLTP insert size seems improper, although i supposed it could work out.
What type of data? What does indexing look like? Any triggers (auditing comes to mind here due to making copies of data)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2016 at 1:12 am
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.
December 1, 2016 at 4:51 am
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.
Exactly how did you get those numbers? I would be willing to bet my last month's revenue as a SQL Server consultant that they do not actually represent the actual average length of a row in each table.
Now, if they are the TOTAL SIZE of each table, WITH INDEXES, and you have loaded a known number of "transactions" into the tables, NOW you can get something useful - which is the average length of each transaction that is created. With that number in hand you can multiple it by the expected number of transactions for whatever period you want to get a good estimate of the total database size at the end of the period.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2016 at 7:52 am
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
December 6, 2016 at 1:43 am
Hi Igor Micev,
I had run the script, it does not give any output
December 6, 2016 at 6:54 am
sandy-833685 (12/6/2016)
Hi Igor Micev,I had run the script, it does not give any output
Did you run in the database context? The query runs at my servers.
Igor Micev,My blog: www.igormicev.com
December 7, 2016 at 12:18 am
Yes i had used Database context
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply