Share via

Azure SQL Managed Instance - Scaling Up

Rohan Gray 1 Reputation point
2021-11-15T20:41:20.947+00:00

We are using an Azure SQL Managed Instance that is currently configured on Pricing Tier "General Purpose".
I've attempted to scale up the Azure SQL Managed Instance to the "Business Critical" pricing tier so we can benefit from the local SSD storage. After completing the scale up process, I compared performance baselines from before the change and didn't notice any difference in performance.

Question: After the "Scale Up" pricing tier change states it is complete, does it take additional time before the local SSD storage is available for use?

Azure SQL Database
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 33,421 Reputation points
    2021-11-15T22:45:58.467+00:00

    Please use a tool like HammerDB to test the performance of the storage subsystem on that managed instance. Try increasing the data file size as explained here, and you will see the performance gains.

    How you determined storage IO performance (IOPS) was an issue on your Azure Managed Instance? Do you recently migrated from a SQL Server instance on-premises?

    0 comments
  2. Rohan Gray 1 Reputation point
    2021-11-16T21:29:19.55+00:00

    Hi Alberto,

    Thank you for sharing this information, I wasn't aware of HammerDB but will get it setup so I can measure some baseline information before making any changes to the data file size.

    I wasn't using anything sophisticated to determine performance (and certainly wasn't measuring IOPS), I just had some basic metrics within my application when it came to database interaction.

    We did recently migrate from SQL Server on-premises to Azure SQL Managed Instance and were expecting to see better performance, but the opposite seems to be true. I'm guessing its misconfiguration or some error on our part.

    I will review the results from HammerDB and see if I can identify the bottleneck.

    Thanks again for your help.

    0 comments
  3. Alberto Morillo 33,421 Reputation points
    2021-11-16T22:59:00.403+00:00

    Rohan,

    My personal experience with customers that just migrated to Azure SQL Managed Instance. They forgot the basics. Update statistics and defragment existing indexes as soon as you migrate, and then create a daily maintenance plan covering those topics. Make sure query plans are not missing indexes. All these will reduce IO and CPU consumption, and reduce execution time of instructions. You can use Ola Hallengreen scripts.

    After doing all steps oulined, then you can test performance of the Azure Managed Instance.

    0 comments
  4. Rohan Gray 1 Reputation point
    2021-11-17T22:02:30.757+00:00

    Hi Alberto,

    Thank you for all your assistance and advise, I greatly appreciate it. I will look into the Ola Hallengreen scripts.

    After working with Azure Support, I think there is some issues with the disk subsystem attached to my Azure SQL Managed Instance. When I check sys.dm_db_wait_stats two heavy hitters are PAGEIOLATCH_SH and PAGEIOLATCH_EX and both have a description that includes this text "Long waits may indicate problems with the disk subsystem.".

    https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-wait-stats-azure-sql-database?view=azuresqldb-current

    I will continue to research and track down the issue, thanks again.