Have two partitioned tables (32 partitions / 16 files / 8 file groups). First table with 56 columns and Second table with 5 columns. One of requirment of application is to test procedures for future workload. I did it in Jmeter application. Put all procedures in one transaction controller
:
GetSomeDataFromNonPartitioneTable1
GetSomeDataFromNonPartitioneTable2
InsertNewTransaction
(insert new row in two partitioned tables)GetOneRecordFromPartitionedTable
UpdatePartitionedTable
At the beggining of the test (0 rows in paritioned tables) got performance around 1300
operations per second ("one operation" means above set of procedures running one after the other). I tried same test (with same number of virtual users) when got around 4 000 000
of records in partitioned tables (4 000 000 per table
). Get performance around 1050
operations per second. Going deeper I have got average response times:
1 test (0 rows in partitoned table)
GetSomeDataFromNonPartitioneTable1
- 22 msGetSomeDataFromNonPartitioneTable2
- 22 msInsertNewTransaction
(insert new row in two partitioned tables) - 160 msGetOneRecordFromPartitionedTable
- 22 msUpdatePartitionedTables
- 136 ms
2 test (around 4 000 000 rows in partitoned tables)
GetSomeDataFromNonPartitioneTable1
- 25 msGetSomeDataFromNonPartitioneTable2
- 25 msInsertNewTransaction
(insert new row in two partitioned tables) - 217 msGetOneRecordFromPartitionedTable
- 23 msUpdatePartitionedTables
- 177 ms
My questions:
- Why bigger table decrease performance so much ?
- What can I do to avoid it ? Bigger partitioned table got 2 non clustered indexes and cluster index, and smaller got only cluster index.