Key causes of performance differences between SQL managed instance and SQL Server

Migrating to a Microsoft Azure SQL Database managed instance provides a host of operational and financial benefits you can only get from a fully managed and intelligent cloud database service. Some of these benefits come from features that optimize or improve overall database performance. After migration many of our customers are eager to compare workload performance with what they experienced with on-premises SQL Server, and sometimes they're surprised by the results. In many cases, you might get better results on the on-premises SQL Server database because a SQL Database managed instance introduces some overhead for manageability and high availability. In other cases, you might get better results on a SQL Database managed instance because the latest version of the database engine has improved query processing and optimization features compared to older versions of SQL Server.

This article will help you understand the underlying factors that can cause performance differences and the steps you can take to make fair comparisons between SQL Server and SQL Database.

If you're surprised by the comparison results, it's important to understand what factors could influence your workload and how to configure your test environments to ensure you have a fair comparison. Some of the top reasons why you might experience lower performance on a SQL Database managed instance compared to SQL Server are listed below. You can mitigate some of these by increasing and pre-allocating file sizes or adding cores; however, the others are prerequisites for guaranteed high availability and are part of the PaaS service.

Simple or bulk recovery model

The databases placed on the SQL Database managed instance are using a full database recovery model to provide high availability and guarantee no data loss. In this scenario, one of the most common reasons why you might get worse performance on a SQL Database managed instance is the fact that your source database uses a simple or bulk recovery model. The drawback of the full recovery model is that it generates more log data than the simple/bulk logged recovery model, meaning your DML transaction processing in the full recovery model will be slower.

You can use the following query to determine what recovery model is used on your databases:

select name, recovery_model_desc from sys.databases

If you want to compare the workload running on SQL Server and SQL Database managed instances, for a fair comparison make sure the databases on both sides are using the full recovery model.

Resource governance and HA configuration

SQL Database managed instance has built-in resource governance that ensures 99.99% availability, and guarantees that management operations such as automated backups will be completed even under high workloads. If you don’t use similar constraints on your SQL Server, the built-in resource governance on SQL Database managed instance might limit your workload.

For example, there's an instance log throughput limit (up to 22MBs on the general purpose and up to 48MBs on the business critical tier) that ensures you can't load more data than the instance can backup. In this case, you might see higher INSTANCE_LOG_GOVERNOR wait statistics that don’t exist in your SQL Server instance. These resource governance constraints might slow down operations such as bulk load or index rebuild because these operations require higher log rates.

In addition, the secondary replicas in business critical tier instances might slow down the primary database if they can't catch-up the changes and apply them, so you might see additional HADR_DATABASE_FLOW_CONTROL or HADR_THROTTLE_LOG_RATE_SEND_RECV wait statistics.

If you're comparing your SQL Server workload running on local SSD storage to the business critical tier, note that the business critical instance is an Always On availability group cluster with three secondary replicas. Make sure that your source SQL Server has an HA implementation similarly using Always On availability groups with at least one synchronous commit replica. If you're comparing the business critical tier with a single SQL Server instance writing to the local disk, this would be an unrealistic comparison due to the absence of HA on your source instance. If you are using async always on replicas, you would have HA with better performance, but in this case you are making the trade-off between the possibility of data-loss in favor of performance, and you will get the better results on the SQL Server instance.

Automated backup schedule

One of the main reasons why you would choose the SQL Database managed instance is the fact that it guarantees you will always have backups of your databases, even under heavy workloads. The databases in a SQL Database managed instance have scheduled full, incremental, and log backups. Full backups are taken every seven days, incremental every twelve hours, and log backups are taken every five to ten minutes. If you have multiple databases on the instance there's a high chance there is at least one backup currently running.

Since the backup operations are using some instance resources (CPU, disk, network), they can affect workload performance. Make sure the databases on the system that you compare with the managed instance have similar backup schedules. Otherwise, you might need to accept that you're getting better results on your SQL Server instance because you're making a trade-off between database recovery and performance, which is not possible on a SQL Database managed instance.

If you're seeing unexpected performance differences, check if there is some ongoing full/differential backup either on the SQL Database managed instance or SQL Server instance that can affect performance of the currently running workload, using the following query:

SELECT r.command, query = a.text, start_time, percent_complete,
      eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
 WHERE r.command IN ('BACKUP DATABASE','BACKUP LOG')

If you see currently running full or incremental backup during the short-running benchmark, you might pause your workload and resume it once the backup finishes.

Connection and App to Database proximity

The application accessing the databases and executing the benchmark queries on the SQL Database managed instance and SQL Server instance must be in a similar network proximity range in both cases. If you are placing your application and SQL Server database in the local environment (or running an app like HammerDB from the same machine where the SQL Server is installed) you will get better results on SQL Server compared to the SQL Database managed instance, which is placed on a distributed cloud environment with respect to the application. Make sure that in both cases you're running the benchmark application or query on separate virtual machines in the same region as SQL Database managed instance to get the valid results. If you're comparing an on-premises environment with the equivalent cloud environments, try to measure bandwidth and latency between the app and database and try to ensure they are similar.

SQL Database managed instance is accessed via proxy gateway nodes that accept the client requests and redirect them to the actual database engine nodes. In order to provide the results closer to your environment, enable ProxyOverride mode on your instance using Set-AzSqlInstance PowerShell command to enable direct access from the client to the nodes currently hosting your SQL Database managed instance.

In addition, due to compliance requirements, a SQL Database managed instance enforces SSL/TLS transport encryption which is always enabled. Encryption can introduce overhead in case of a large number of queries. If your on-premises environment does not enforce SSL encryption you will see additional network overhead in the SQL Database managed instance.

Transparent data encryption

The databases on SQL Database managed instance are encrypted by default using Transparent Data Encryption. Transparent Data Encryption encrypts/decrypts every page that is exchanged with the disk storage. This spends more CPU resources, and introduces additional latency in the process of fetching and saving the data pages to or from disk storage. Make sure that both databases on SQL Database managed instance and SQL Server have Transparent Data Encryption either turned on or off, and that database encryption/decryption operations have completed before starting performance testing.

You can use the following query to determine whether the databases are encrypted:

select name, is_encrypted from sys.databases

Another important factor that might affect your performance is encrypted TempDB. TempDB is encrypted if at least one database on your SQL Server or SQL Database managed instance is encrypted. As a result, you might compare two databases that are not encrypted, but due to some other SQL Database managed instance being encrypted (although it's not involved in the workload) the TempDB will also be encrypted. The unencrypted databases will still use encrypted TempDB and any query that creates temporary objects or uses spills would be slower. Note that TempDB will only get decrypted once all user databases on an instance are decrypted, and the instance restarts. Scaling a SQL Database managed instance to a new pricing tier and back is one way to restart it.

Database engine settings

Make sure the database engine setting such as database compatibility levels, trace flags, system configurations (‘cost threshold for parallelism’, ’max degree of parallelism’), database scoped configurations (LEGACY_CARDINALITY_ESTIMATOR, PARAMETER_SNIFFING, QUERY_OPTIMIZER_HOTFIXES, etc.), and database settings (AUTO_UPDATE_STATISTICS, DELAYED_DURABILITY) on the SQL Server and SQL Database managed instances are the same on both databases.

The following sample queries can help you to identify setting on SQL Server and Azure SQL Database managed instance:

select compatibility_level, snapshot_isolation_state_desc, is_read_committed_snapshot_on,

  is_auto_update_stats_on, is_auto_update_stats_async_on, delayed_durability_desc
from sys.databases;
GO

select * from sys.database_scoped_configurations;
GO

dbcc tracestatus;
GO

select * from sys.configurations;

Compare the results of these queries on the SQL Database managed instance and SQL Server and try to align the differences if you identify some.

Note: The list of trace flags and configurations might be very long so we recommend filtering them or lookng only on the trace flags you've changed or know are affecting performance. Some of the trace flags are pre-configured on SQL Database managed instance as part of PaaS configurations and they are not affecting performance.

You might experiment with changing the compatibility level to a higher value, turning on the legacy cardinality estimator, or the automatic tuning feature on the SQL Database managed instance, which might give you better results than your SQL Server database.

Also note that SQL Database managed instance might provide better performance even if you align all parameters because it has the latest improvements, or fixes that are not bound to compatibility level, or some features, like forcing last good plan, that might improve your workload.

Hardware and environment specification

SQL Database managed instance runs on standardized hardware with pre-defined technical characteristics that are probably different than your environment. Some of the characteristics you might need to consider when comparing your environment with the environment where the SQL Database managed instance is running are:

1. Number of cores should be the same both on SQL Server and the SQL Database managed instance. Note that a SQL Database managed instance uses 2.3-2.4 GHz processors, which might be different than your processor speed. It might consume more or less CPU for the same operation due to the CPU differences. If possible, check if hyperthreading is used on the SQL Server environment when comparing to the Gen4 and Gen5 hardware generations on a SQL Database managed instance. One on Gen4 hardware does not use hyperthreading, while on Gen5 it does. If you are comparing SQL Server running on a bare-metal machine with a SQL Database managed instance or SQL Server running on a virtual machine you'll probably get better results on a bare-metal instance.

2. Amount of memory including memory/core ratio (5.1 GB/core on Gen5, 7 GB/core on Gen4). Higher memory/core ratio provides bigger buffer pool cache and increases cache hit ratio. If your workload does not perform well on a managed interface with the memory/core ratio 5, then you probably need to choose a virtual machine with the appropriate memory/core ratio instead of a SQL Database managed instance.

3. IO characteristics – You need to be aware that performance of the storage system might be very different compared to your on-premises environment. A SQL Database managed instance is a cloud database and relies on Azure cloud infrastructure.

◈ The general purpose tier uses remote Azure Premium disks where IO performance depends on the file sizes. If you reach the log limit that depends on the file size, you might notice WRITE_LOG waits and less IOPS in file statistics. This issue might occur on a SQL Database managed instance if the log files are small and not pre-allocated. You might need to increase the size of some files in the general purpose tier to get better performance.

◈ A SQL Database managed instance does not use instant file initialization, so you might see additional PREEMPTIVE_OS_WRITEFILEGATHER wait statistics since the date files are filled with zero bytes during file growth.

4. Local or remote storage types – Make sure you're considering local SSD versus remote storage while doing the comparison. The general purpose tier uses remote storage (Azure Premium Storage) that can't match your on-premises environment if it uses local SSD or a high-performance SAN. In this case you would need to use the business critical tier as a target. The general purpose tier can be compared with other cloud databases like SQL Server on Azure Virtual Machines that also use remote storage (Azure Premium Storage). In addition, beware that remote storage used by a general purpose instance is still different than remote storage used by a SQL Virtual Machine because:

◈ The general purpose tier uses a dedicated IO resource per each database file that depends on the size of the individual files, while SQL Server on Azure Virtual Machine uses shared IO resources for all files where IO characteristics depend on the size of the disk. If you have many small files, you will get better performance on a SQL Virtual Machine, while you can get better performance on a SQL Database managed instance if the usage of files can be parallelized because there are no noisy neighbors who are sharing the same IO resources.

◈ SQL Virtual Machines use a read-caching mechanism that improves read speed.

If your hardware specs and resource allocation are different, you might expect different performance results that can be resolved only by changing the service tier or increasing file size. If you are comparing a SQL Database managed instance with SQL Server on Azure Virtual Machines, make sure that you are choosing a virtual machine series that has memory/cpu ratio similar to SQL Database managed instance, such as DS series.

Azure SQL Database managed instance provides a powerful set of tools that can help you troubleshoot and improve performance of your databases, in addition to built-in intelligence that could automatically resolve potential issues. 

Continue reading

Network Performance Monitor’s Service Connectivity Monitor is now generally available

Network Performance Monitor’s (NPM) Service Connectivity Monitor, previously in preview as Service Endpoint Monitor, is making general availability sporting a new name. With Service Connectivity Monitor, you can now monitor connectivity to services such as applications, URIs, VMs, and network devices, as well as determine what infrastructure is in the path and where network bottlenecks are occurring.

As services and users are becoming more dispersed across clouds, branch offices, and remote geographies, it is becoming more difficult to determine the cause of a service outage or performance degradation. These can be due to an issue with the application, stack, or cluster as well as network issues in the cloud, the carrier network, or in the first-mile. Service Connectivity Monitor integrates the monitoring and visualization of the performance of your internally hosted and cloud services with the end-to-end network performance. You can create connectivity tests from key points in your network to your services and identify whether the problem is due to the network or the application. With the network topology map, you can locate the links and interfaces experiencing high loss and latencies, helping you identify external and internal troublesome network segments.

Determine if it’s an application or a network problem

You can determine whether the application connectivity issue is due to the application or the network by corelating the application response time with the network latency.

The example image below illustrates a scenario where spikes in the application response time are accompanied with corresponding spikes in the network latency. This suggests that the application degradation is due to an increase in network latency, and therefore, the issue is due to the underlying network, and not the application.

The example snippet below demonstrates another scenario where there is a spike in the application response time whereas the network latency is consistent. This suggests that the network was in a steady state, when the performance degradation was observed. Therefore, the problem is due to an issue at the application end.

Identify network bottlenecks

You can view all the paths and interfaces between your corporate premises and application endpoint on NPM’s interactive topology map. You not only get end-to-end network visibility from your nodes to the application, but you can also view the latency contributed by each interface to help you identify the troublesome network segment. The image below illustrates a scenario where you can identify the highlighted network interface as the one causing most latency.

Monitor service connectivity from multiple vantage points from a central view

You can monitor connectivity to your services from your branch offices, datacenters, office sites, cloud infrastructure, etc. from a central view. By installing the NPM agents at the vantage points in your corporate perimeter, you can get the performance visibility from where your users are accessing the application.

The below example image illustrates a scenario where you can get the network topology from multiple source nodes to www.msn.com in a single pane of view and identify the nodes with connectivity issues from the unhealthy paths in red.

Monitor end-to-end connectivity to services

Monitor the total response time, network latency, and packet loss between the source nodes in your corporate perimeter and the services you use, such as websites, SaaS, PaaS, Azure services, file servers, SQL servers, among others. You can setup alerts to get proactively notified whenever the response time, loss, or latency from any of your branch offices crosses the threshold. In addition to viewing the near real-time values and historical trends of the performance data, you can use the network state recorder to go back in time to view a particular network state in order to investigate the difficult-to-catch transient issues.

Monitor connectivity to Microsoft services using built-in tests for Microsoft Office 365 and Dynamics 365

Service Connectivity Monitor provides built-in tests that allow a simple one-click setup experience to monitor connectivity to Microsoft’s Office 365 and Dynamics 365 services, without any pre-configuration. Since the capability maintains a list of endpoints associated with these services, you do not have to enter the various endpoints associated with each service. 

Create custom queries and views

All data that is exposed graphically through NPM’s UI are also available natively in Log Analytics search. You can perform interactive analysis of data in the repository, correlate data from different sources, create custom alerts and views, and export the data to Excel, PowerBI, or a shareable link.

Continue reading

Monitor network connectivity to applications with NPM’s Service Endpoint Monitor - public preview

As more and more enterprises are hosting their applications in cloud and are relying more on SaaS and PaaS applications to provide services, they are increasingly becoming dependent on multiple external applications and the networks in between. The traditional network monitoring tools work in silos and do not provide end-to-end visibility. Therefore, if an application is found to be running slow, it becomes difficult to identify whether the problem is because of your network, the service provider or the application. Network Performance Monitor (NPM) introduces Service Endpoint Monitor that integrates the monitoring and visualization of the performance of your internally hosted & cloud applications with the end-to-end network performance. You can create HTTP, HTTPS, TCP and ICMP based tests from key points in your network to your applications, allowing you to quickly identify whether the problem is due to the network or the application. With the network topology map, you can locate the links and interfaces experiencing high loss and latencies, helping you identify external & internal troublesome network segments.

Some of the capabilities of NPM’s Service Endpoint Monitor are listed below:

Monitor end-to-end connectivity to applications

Service Endpoint Monitor monitors total response time, network latency and packet loss between your resources (branch offices, datacenters, office sites, cloud infrastructure) and the applications you use, such as websites, SaaS, PaaS, Azure services, File servers, SQL etc. By installing the NPM agents at the vantage points in your corporate perimeter, you can get the performance visibility from where your users are accessing the application. You can setup alerts to get proactively notified whenever the response time, loss or latency from any of your branch offices crosses the threshold. In addition to viewing the near real-time values and historical trends of the performance data, you can use the network state recorder to go back in time to view particular network state in order to investigate the difficult-to-catch transient issues.

Correlate application delivery with network performance

The capability plots both the response time as well as the network latency trends on the same chart. This helps you easily correlate the application response time with the network latency to determine whether the performance degradation is due to the network or the application.

The following snippet demonstrates one such scenario. The chart shows a spike in the application response time whereas the network latency is consistent. This suggests that the network was in a steady state, when the performance degradation was observed - therefore, the problem is due to an issue at the application end.

The example image below illustrates another scenario where spikes in the application response time are accompanied with corresponding spikes in the network latency. This suggests that the increase in response time is due to an increase in network latency, and therefore, the performance degradation is due to the underlying network.

Once You’ve established the network to be the problem area, you can then use the network topology view to identify the troublesome network segment.

Identify troublesome network interfaces and links

NPM’s interactive topology view provides end-to-end network visibility from your nodes to the application. You can not only view all the paths and interfaces between your corporate premises and application endpoint, but also view the latency contributed by each interface to help you identify the troublesome network segment. The below example image illustrates one such scenario where most of the latency is because of the highlighted network interface.

The below example image illustrates another scenario where you can get the network topology from multiple nodes to www.msn.com in a single pane of view and identify the unhealthy paths in red.

When you are using external services such as Office 365, several intermediate hops will be outside of your corporate network. You can simplify the topology map by hiding the intermediate hops using the slider control in filters. You can also choose to view only the unhealthy paths.

Built-in tests for Microsoft Office 365 and Microsoft Dynamics 365

NPM provides built-in tests that monitor connectivity to Microsoft’s Office 365 and Dynamics 365 services, without any pre-configuration.  The built-in tests provide simple one-click setup experience where you only have to choose the Office 365 and Dynamics 365 services you are interested in monitoring. Since the capability maintains a list of endpoints associated with these services, you do not have to enter the various endpoints associated with each service.

Create custom queries and views

All data that is exposed graphically through NPM’s UI is also available natively in Log Analytics search. You can perform interactive analysis of data in the repository, corelate data from different sources, create custom alerts, create custom views and export the data to Excel, PowerBI or a shareable link.

Continue reading

ExpressRoute monitoring with Network Performance Monitor (NPM) is now generally available

We are excited to share the general availability of ExpressRoute monitoring with Network Performance Monitor (NPM). Since then, we’ve seen lots of users monitor their Azure ExpressRoute private peering connections, and working with customers we’ve gathered a lot of great feedback. While we’re not done working to make ExpressRoute monitoring best in class, we’re ready and eager for everyone to get their hands on it. In this post, I’ll take you through some of the capabilities that ExpressRoute Monitor provides.

Monitor connectivity to Azure VNETs, over ExpressRoute

NPM can monitor the packet loss and network latency between your on-premises resources (branch offices, datacenters, and office sites) and Azure VNETs connected through an ExpressRoute. You can setup alerts to get proactively notified whenever the loss or latency crosses the threshold. In addition to viewing the near real-time values and historical trends of the performance data, you can use the network state recorder to go back in time to view particular network state in order to investigate the difficult-to-catch transient issues.

Get end-to-end visibility into the ExpressRoute connections

Since an ExpressRoute connection comprises of various components, it is extremely difficult to identify the bottleneck when high latency is experienced while connecting to an Azure workload. Now, you can get the required end-to-end visibility through NPM’s interactive topology view. You can not only view all the constituent components, your on-premises network, circuit provider edge, ExpressRoute circuit, Microsoft edge, and Azure VMs, but also the latency contributed by each hop to help you identify the troublesome segment.

The following snippet illustrates a topology view where the Azure VM on the left is connected to the on-premises VM on the right, over primary and secondary ExpressRoute connections. The Microsoft router at the Azure edge and the service provider router at the customer edge are also depicted. The nine on-premises hops (depicted by dashed lines) are initially compressed.

You can also choose to expand the map to view all the on-premises hops and understand the latency contributed by each hop.

Understand bandwidth utilization

This capability lets you view the bandwidth utilization trends for both the primary and secondary ExpressRoute circuits, and as a result, helps you in capacity planning. Not only can you view the aggregated bandwidth utilization for all the private peering connections of the ExpressRoute circuit, but you can also drill-down to understand the bandwidth utilization trend for each VNET. This will help you identify the VNETs that are consuming most of your circuit bandwidth.

You can also setup alerts to notify when the bandwidth consumed by a VNET crosses the threshold.

Diagnose ExpressRoute connectivity issues

NPM helps you diagnose several circuit connectivity issues. Below are examples of possible issue.

Circuit is down - NPM notifies you as soon as the connectivity between your on-premises resources and Azure VNETs is lost. This will help you take proactive action before receiving user escalations and reduce the downtime.

Traffic not flowing through intended circuit - NPM can notify you whenever the traffic is unexpectedly not flowing through the intended ExpressRoute circuit. This can happen if the circuit is down and the traffic is flowing through the backup route, or if there is a routing issue. This information will help you proactively manage any configuration issues in your routing policies and ensure that the most optimal and secure route is used.

Traffic not flowing through primary circuit - The capability notifies you when the traffic is flowing through the secondary ExpressRoute circuit. Even though you will not experience any connectivity issues in this case, proactively troubleshooting the issues with the primary circuit will make you better prepared.

Degradation due to peak utilization - You can correlate the bandwidth utilization trend with the latency trend to identify whether the Azure workload degradation is due to a peak in bandwidth utilization, or not, and take action accordingly.

Continue reading