Category: SQL Server

by Fran Lens 2020-04-02

Improve Managed Instance performance even more

In the company we have been working with Azure SQL Database Managed Instance for more than 1 year and in general terms we can say that we are very happy with the technology, because as a PaaS tool we no longer have to worry about hardware incidents or updates, only for issues that may occur in the databases.

Before starting to talk about how to improve the performance of Managed Instance, I tell you that if you do not know this tool well, you can take a look at the post by my colleague Nacho Cotanda in which he explains how it works and teaches how to create it in Azure.

As I mentioned in the introduction, Managed Instance is a PaaS tool and that allows us to forget about much of the configuration, so let’s start by listing the features and options that are already fully optimized and that we will not have to worry about.

In Managed Instance you don’t have to worry about …

• Shared hardware, as in the entire Azure platform, all resources are reserved for you, that is, vCore, ram memory or disks will only be used by your Managed Instance.
• Updates, you will never have to worry about new versions of SQL Server, Service Pack or Cummulative Update, it will update automatically and without a cut in the service.
• Backups, you will have automatic backups of up to 35 days old and you can restore them with millisecond precision (In future updates they will incorporate Long-Term Backups).
• High Availability, all the resources of your Managed Instance are replicated in several nodes, which ensures 99.99% availability.
• TempDB, you no longer have to be aware of the number of files or size because it is automatically managed.
• Max Server Memory, this value that always needed to be adjusted is now automatically adjusted as well.
• Fill Factor, you won’t have to worry about the fill factor of your indexes either, it adjusts automatically.
• Trace Flags, all the trace flags that you would normally activate are already activated, and many more, you can check it by running DBCC TRACESTATUS.
• And I stop here because I could fill another blog post with just this.

In many of these cases and so you don’t touch too much, if you try to change the settings, you will get an error:

In Managed Instance you have (and should) worry about …

Despite the fact that much of the configuration that was normally done in Server and Instance is now done automatically, there are still many things that we have to be aware of and configure, especially at the level of the database itself.

Instance

In the Instance configuration there are two options that it is always recommended to adjust, or at least know them in case we ever need to modify them:

• Optimize for Ad Hoc Workloads, in the properties of our instance, in the Advanced tab. This option is recommended to activate it if our database receives many queries of Ad-Hoc type (single use), since it will prevent the execution plan for these queries that are only made once from being saved in memory, with that we will achieve that our memory will not be filled with execution plans that are not going to be reused. More information here.

• Parallelism, in the properties of our instance, in the Advanced tab. A quick explanation of parallelism would be the maximum number of cores that a query can use, and within this, the two options to consider are Max Degree of Parallelism and Cost Threshold for Parallelism. More information here.
  • Max Degree of Parallelism allows setting the maximum cores that can be used, by default 0 (all). The Best Practices of some applications such as SAP, AX or Sharepoint recommend setting it to 1, but this value will depend a lot on your application and especially on the type of queries you make on the database.
  • Cost Threshold for Parallelism allows you to set the number of seconds from which a query can start using parallelism, by default it’s 5 seconds.

Database

At the database level, it is where we will have to do the most work and there are many possibilities to improve performance.

Starting with the configuration values of the database itself:

• Growth value, it is necessary to avoid growth values of few mb in the data files and logs, since especially at the level of the Log file it can generate a large number of Virtual Log Files, VLFs. A large number of VLFs can cause us problems in many aspects such as greatly increasing the restoration time of a database.
• Compatibility Level, make sure to change (having tested before) the Compatibility Level value to the highest so that our database can use the latest features available in the SQL Server engine.
• Auto Create and Auto Update Statistics, these options should ALWAYS be activated unless the manufacturer of the application recommends otherwise, as is the case with Sharepoint. More information about why they must be activated here.
• Query Store, by default is activated, but it does not hurt to check it because this option keeps a history of execution plans and statistics of our queries, and this information is very valuable for many aspects.

Automatic Tuning: Also from SQL Server 2017 we have this option available, which, as its name suggests, will make automatic modifications to improve the performance of our queries if it detects that the most optimal execution plan is not being used. More information here.

With the following query we will activate it on the selected database:

ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Largest file size, Largest IO: A VERY IMPORTANT fact that we have to know is that each database file in Managed Instance is saved on a separate SSD disk, and in Azure the maximum IOs depend on the size of the disk.

The disks to be used will vary from the smallest of 128Gb (P10) to the largest of 4Tb (P50), the following table shows the IOs and the transfer of each one:

An example, if we have a database with a 10Gb data file then it will be hosted on a P10 disk with 500 IOs, but if we expand that file to 550Gb then it will change the file to a P20 disk with 2300 IOs.

So, if at any time we detect that our database is having an IO problem, by increasing the size of its files we will achieve a considerable performance improvement.

This information and a study with a complete IO test can be found in this post by Jovan Popovic, one of the greatest Managed Instance experts today.

Maintenance plans, always

As in any version of SQL Server, we will need maintenance plans that keep our instance up to date, and if we talk about maintenance plans, the gold medal is for Ola Hallengren Maintenance Plans that you can download here. In our case we will not need the backup plan, but the IndexOptimize and IntegrityCheck plans will be necessary.

In the next post…

I will talk about the panels that we have in SQL Server Management Studio and Azure Data Studio that will help us obtain very valuable metrics and that will even detect performance improvements that we can apply in our Managed Instance.

If you have performance issues in Managed Instance or SQL Server, or if you are planning to migrate to Azure, we have the experience to help and advise you, contact us at info@aleson-itc.com or call us at +34 962 681 242