Special DB Query Performance Boost up technique for Azure SQL Managed Instance General Purpose tier
- Soumen Das
- Dec 22, 2024
- 1 min read
Here, we are going to discuss about a special database query performance improvement technique, which is applicable only for General Purpose tier of Azure SQL Managed Instance.
As we are aware of many query performance boost up techniques like proper indexing, index defragmentation, query tuning, partitioning, increasing compute (v-cores), caching etc., applicable for almost all relational databases. However, due to cost & size restrictions and features like cross-database query, if you are bound to choose General Purpose tier of Azure SQL Managed Instance, the following special query performance technique will help.
The database IOPS and throughput for General Purpose tier of Azure SQL Managed Instance depends on the size of data and log files. The more data and log file size increases, the IOPS & throughput rises as well as per the following table.
File size | >=0 and <=129 GiB | >129 and <=513 GiB | >513 and <=1025 GiB | >1025 and <=2049 GiB | >2049 and <=4097 GiB | >4097 GiB and <=8 TiB |
IOPS per file | 500 | 2300 | 5000 | 7500 | 7500 | 7500 |
Throughput per file | 100 MiB/s | 150 MiB/s | 200 MiB/s | 250 MiB/s | 250 MiB/s | 250 MiB/s |
Let's assume , you have to design a database with maximum size of 4 TB. To maximize performance, you may create 2 data files with 2 TB (> 1025 GB) size each and one log file size 1.1 TB (> 1025 GB). This way, the maximum, 7500 IOPS and 250 MiB/s throughput per file can be achieved.
Comentários