CODEWITHSUNDEEP
sql-serverazure-sql-managed-instance
defect833
defect833

Reputation: 285

Performance of using multiple filegroups in SQL Managed Instance

Similar to Azure single database (PaaS), Azure SQL Managed Instance does not allow referencing or creating physical files on disk. However, it does still support filegroups when restoring an on-premise backup that used them or by using the ALTER DATABASE command.

This is great for migration compatibility for existing projects but are there performance benefits from using filegroups in managed instance (cloud hosted storage)?

Upvotes: 0

Views: 2068

Answers (1)

Martin Cairney
Martin Cairney

Reputation: 1767

There is a blog post from the SQLCAT team describing best practices. In general you want to have the largest file size that you can to increase the IOPS.

My interpretation is that if by splitting the database to have multiple files then the sum of the individual IOPS would need to be larger than the corresponding single file IOPS. It may be that you end up with less.

Upvotes: 1

Related Questions