SALG : 1-800-867-1389
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

Performance Considerations with Azure SQL Database

Updated: January 15, 2014

This article covers application best practices that improve the performance of applications that use a database that has been migrated to Microsoft Azure SQL Database. When a database server shares the same rack as the application server, these best practices may not have much impact. However, when the database server moves to a remote data center, these same best practices are critical for maintaining good performance. Also, is a shared environment in that all resources are shared with other applications, roles, and databases. Through network load balancing and gateway components, maintains economy of scale, and provides computing and network resources most efficiently to the environment as a whole. Take these factors into account when designing applications for and deploying applications to .

This article covers design and implementation best practices to optimize performance for the Microsoft Azure SQL Database environment. Specifically, this article reviews best practices to address two areas that can cause perceived performance issues when on-premises databases are migrated to Microsoft Azure SQL Database:

There are a few other articles related to performance for Azure database as described below:

Azure SQL Database offers the ability to reserve resources for your database through the Premium Edition currently available through the preview program. The Premium Preview for SQL Database Guidance paper provides guidance to help you determine if the Premium database that is available through the preview program is right for your application and provides recommendations for tuning your application to get the most from this feature.

The Azure SQL Database and SQL Server -- Performance and Scalability Compared and Contrasted article describes some performance patterns for SQL Database, several techniques to properly evaluate the performance, and includes several SQL scripts that can help you evaluate and troubleshoot your SQL Database instances.

  • Connection management

  • Network latency between the application-tier and the database tier

Authors: Silvano Coriani, Steve Howard
Reviewers: Mark Simms, Valery Mizonov, Kun Cheng, Paolo Salvatori, Jaime Alva Bravo

Connection Management in Microsoft Azure SQL Database

Database connections may be terminated more frequently when a database is hosted in Microsoft Azure SQL Database than an on-premises environment. Users may perceive these terminations as a performance issue if applications do not quickly detect the loss of a connection and reconnect in the case of a temporary, or transient, error. As a provider of a large-scale, multitenant database service on shared resources, Microsoft Azure SQL Database clusters each database across three nodes, and balances resources between cluster nodes to provide a good experience to all tenants. An example of a transient error is when Microsoft Azure SQL Database detects heavy usage of a server that hosts multiple databases. In this case, Microsoft Azure SQL Database may fail over one of the databases to a secondary cluster node that has a lower processing load. The failover terminates all open connections to the database, and rolls back outstanding transactions. The applications must quickly detect the error, reconnect to the database, and retry their last transaction.

The following list outlines some of the reasons why Microsoft Azure SQL Database may terminate connections:

  • The overall Microsoft Azure SQL Database network topology involves firewalls, load balancers, and Tabular Data Stream (TDS) gateways. Each of these topology components adds a layer between the data access code and the database node. Errors in these additional layers can terminate connections.

  • Microsoft Azure SQL Database continuously gathers and analyzes database usage statistics. Based on those statistics, Microsoft Azure SQL Database may terminate connections when necessary to keep the service in a healthy state.

  • Denial-of-service attacks result in Microsoft Azure SQL Database blocking connections from a specific IP address for a period of time.

  • Some failover events may cause Microsoft Azure SQL Database to abruptly terminate a session. (Note that any connections opened on a node prior to a failover event will not be available on the new node after failover.)

The previous list is just some of the reasons for connection terminations. For more information about connection errors and details on how to manage connections in Microsoft Azure SQL Database, see the following documents:

Options for Handling Connection Management in Code

To help manage Microsoft Azure SQL Database connection issues, Microsoft has worked to provide the following functionality:

  • A consistent approach on how to specify basic information, like server names and security credentials, or full fidelity using tools like bulk copy program (bpc.exe). For example, starting with SQL Server Native Client 11, JDBC version 4.0, and .NET Framework Data Provider for SQL Server (System.Data.SqlClient) from the .NET Framework version 4.0, you do not need to specify username@server when accessing Microsoft Azure SQL Database.

  • A focus on ensuring that all connection technologies can maintain a connection, even during idle periods.For example, unlike Windows, the Java platform does not natively manage “keep alive” intervals for database connections. Hence, JDBC components that connect to Microsoft Azure SQL Database require some changes to the registry setting to ensure that idle connections are not dropped.
    For more information, see the MSDN Library topic, Connecting to a Database on Azure SQL Database.

In addition, Microsoft has continuously deployed a number of updates in most common data access libraries and Microsoft Azure SQL Database service releases. Perhaps the most significant of these updates is The Transient Fault Handling Application Block, an application library that provides robust transient fault handling logic. (Transient faults are errors that occur because of some temporary condition such as network connectivity issues or service unavailability.) The next section provides a high-level look at how to apply the Transient Fault Handling Application Block to an application.

A Quick Look at How to Use the Transient Fault Handling Application Block

The Transient Fault Handling Application Block encapsulates information about the transient faults that can occur when you use the following services in your application:

  • Microsoft Azure SQL Database

  • Azure Service Bus

  • Azure Storage

  • Azure Caching Service

Each of these services can have different transient faults. Thus, the Transient Fault Handling Application Block uses specific error detection policies for each service. Similarly, different applications require different fault handling strategies. To accommodate these differences, the Transient Fault Handling Application Block provides different retry logic approaches that deal with the various transient fault scenarios. These out-of-the-box policies can be extended by creating custom classes that expose well-defined interfaces.

Using the Transient Fault Handling Application Block inside existing applications has very little impact. Based on its design, the Transient Fault Handling Application Block offers several classes and extension methods that mimic the behavior of a typical ADO.NET data access layer.

To demonstrate the ease of use of this application library, the next few paragraphs show how to apply the Transient Fault Handling Application Block to some existing code. The following example shows a simple method that queries a database and consumes the result set:

        public static void ReadFromDB()
        {

            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    conn.Open();

                    SqlCommand selectCommand = 
new SqlCommand(@"SELECT SOH.SalesOrderID
                         FROM SalesLT.SalesOrderHeader SOH 
                         JOIN SalesLT.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
                         JOIN SalesLT.Product P ON SOD.ProductID = P.ProductID
                         JOIN SalesLT.Customer C ON SOH.CustomerID = C.CustomerID
                         JOIN SalesLT.CustomerAddress CA on C.CustomerID = CA.CustomerID
                         JOIN SalesLT.Address A on CA.AddressID = A.AddressID
                         WHERE A.City=@City", conn);

                    selectCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, "London"));
                    selectCommand.CommandType = CommandType.Text;

                    IDataReader dataReader = selectCommand.ExecuteReader();

                    while (dataReader.Read())
                    {
                        Console.WriteLine("OrderID: {0}", dataReader["SalesOrderID"]);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception: {0}",e.Message);
                }
            }

To make this code more robust, first define an appropriate retry strategy. An incremental retry strategy is best. To implement this strategy, first use the Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure.SqlAzureTransientErrorDetectionStrategy class. This class intercepts error codes that are related to transient fault conditions. Then, substitute the System.Data.SqlClient SqlConnection class with the Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure.ReliableSqlConnection class, as shown in the following code example:

        public static void ReadFromDBWithReliableConnection()
        {

            // Define retry Strategy and Policy
            var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2));
            var retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(retryStrategy);

            // Receive notifications about retries.
            retryPolicy.Retrying += new EventHandler<RetryingEventArgs>(retryPolicy_Retrying);

            using (ReliableSqlConnection conn = new ReliableSqlConnection(connString,retryPolicy))
            {
                try
                {
                    conn.Open();

                    SqlCommand selectCommand = new SqlCommand(@"SELECT SOH.SalesOrderID
                                        FROM SalesLT.SalesOrderHeader SOH 
                                        JOIN SalesLT.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
                                        JOIN SalesLT.Product P ON SOD.ProductID = P.ProductID
                                        JOIN SalesLT.Customer C ON SOH.CustomerID = C.CustomerID
                                        JOIN SalesLT.CustomerAddress CA on C.CustomerID = CA.CustomerID
                                        JOIN SalesLT.Address A on CA.AddressID = A.AddressID
                                        WHERE A.City=@City");

                    selectCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, "London"));
                    selectCommand.CommandType = CommandType.Text;

                    IDataReader dataReader = conn.ExecuteCommand<IDataReader>(selectCommand);

                    while (dataReader.Read())
                    {
                        Console.WriteLine("OrderID: {0}", dataReader["SalesOrderID"]);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception: {0}", e.Message);
                }
            }
        
        }

The previous code example, while adding appropriate retry logic, also replaced the existing SQLConnection code with the ReliableSQLConnection code. To minimize the amount of code to be rewritten, there is an alternative approach—using the extension methods supplied with the Transient Fault Handling Application block. This approach not only minimizes the amount of rewriting needed, but also offers a generic way to add retry capabilities to an ADO.Net application. To use the extension methods, replace the Open() and various Execute methods (such as ExecuteScalar(), ExecuteReader(), or ExecuteNonQuery()) with their retry-capable equivalents, such as OpenWithRetry() or ExecuteScalarWithRetry(). This is shown in the following code example:

        public static void ReadFromDBWithExecute()
        {

            // Define retry Strategy and Policy
            var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2));
            var retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(retryStrategy);

            // Receive notifications about retries.
            retryPolicy.Retrying += new EventHandler<RetryingEventArgs>(retryPolicy_Retrying);

            try
            {
                retryPolicy.ExecuteAction(
                  () =>
                  {
                      using (SqlConnection conn = new SqlConnection(connString))
                      {
                          conn.OpenWithRetry();

                          SqlCommand selectCommand = new SqlCommand(@"SELECT SOH.SalesOrderID
                                                FROM SalesLT.SalesOrderHeader SOH 
                                                JOIN SalesLT.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
                                                JOIN SalesLT.Product P ON SOD.ProductID = P.ProductID
                                                JOIN SalesLT.Customer C ON SOH.CustomerID = C.CustomerID
                                                JOIN SalesLT.CustomerAddress CA on C.CustomerID = CA.CustomerID
                                                JOIN SalesLT.Address A on CA.AddressID = A.AddressID
                                                WHERE A.City=@City",conn);

                          selectCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, "London"));
                          selectCommand.CommandType = CommandType.Text;

                          // Execute the above query using a retry-aware ExecuteCommand method which will
                          // automatically retry if the query has failed (or connection was dropped)
                          IDataReader dataReader = selectCommand.ExecuteReaderWithRetry(retryPolicy);
                          
                            while (dataReader.Read())
                            {
                                Console.WriteLine("OrderID: {0}", dataReader["SalesOrderID"]);
                            }                          
                      }
                  });
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: {0}", e.Message );
            }

        }

The Transient Fault Handling Application Block supports the declaration of configurable retry policies. For more information about declaring retry policies, see Specifying Retry Strategies in the Configuration

The code examples shown here offer a quick glimpse at how to use the Transient Fault Handling Application Block. More detailed information about how to use this application library in a TechNet Wiki tutorial: Retry Logic for Transient Failures in Azure SQL Database

Network Latency in Microsoft Azure SQL Database

Besides connections errors, network latency is the other most encountered performance issue among current Microsoft Azure SQL Database users.

While the impact of internet latency is well known, people tend to underestimate the impact of latency between the application and Microsoft Azure SQL Database. Even when the same data center hosts the application and databases, the latency is typically higher than that of a traditional on-premises environment. This higher latency stems from the multi-tenant nature of Azure. In addition, this higher latency amplifies the impact of “chatty” application behaviors as each call to the database will experience the additional latency that could add up to overall performance degradation. Hence, “chatty” applications take significantly longer when accessing a database hosted in Microsoft Azure SQL Database than a SQL Server database hosted on-premises.

Besides latency between the application and Microsoft Azure SQL Database, there is increased latency in communications between the various distributed components of your solution. This type of latency is perhaps one of the biggest differences between on-premises and cloud applications. This type of latency carries through for communications between both user and application, and application and Microsoft Azure SQL Database.

From an end-user perspective, all of these causes for network latency correspond to the following perceived response time for a user:

Response Time = 2 x (Latency_1 + Latency_2) + Application_Processing_Time + Query_Exec_Time

Where

  • Latency_1 is the latency between the end user and the data center that is hosting the application. (This type of latency can also be found in on-premises environments as well.)

  • Latency_2 is the latency between the application and the databases in Microsoft Azure SQL Database.

To ensure performance optimization, we recommend you perform the following basic actions first:

  • Minimize Latency_1 by selecting a data center closest to majority of your users.

  • Minimize Latency_2 by colocating the data with the application to minimize network round-trips.

  • Minimize Application_Processing_Time and Query_Exec_Time by following the general best practices that you would use for on-premises databases when accessing the data layer, handling performance, and coding for optimization.

Minimize the Distance between Data and Applications

Keeping your databases hosted as closely as possible to where the application runs is important. If your application is running in the North Central US data center, then hosting your database in the North Central US data center will give you the shortest round trip, and thus the shortest distance related latency.

While it is possible to access a database hosted in from an application hosted on-premises, keep in mind that this will result in higher latency for data access, and will also result in data egress charges from the data center.

In cases where data needs to be accessed in multiple geographical locales, make use of Content Delivery Network to distribute static data across multiple sites. Alternatively, create multiple copies of a database in different data centers and use SQL Data Sync (Preview) to synchronize data across them. These approaches help applications running in multiple geographical sites by locating the data closer to the different instances of the application and reducing overall latency.

noteNote
SQL Data Sync (Preview) is currently available only as a Preview and is meant only for product feedback for future releases and should not be used in production environments.

Minimize Network Round Trips

Minimizing network round trips, while important for on-premises application, is especially important with . Queries processed by Microsoft Azure SQL Database must go through layers of network load balancing, as well as the TDS protocol gateway prior to being received by Microsoft Azure SQL Database. While this abstraction enables Microsoft Azure SQL Database to provide scaling and availability the end user, this abstraction also requires a certain amount of processing that result in a small amount of latency on each round trip. Applications that send data to Microsoft Azure SQL Database in many sequential round trips may notice a significant performance hit.

To minimize the effects of round trips, follow the same best practices that you would for on-premises applications:

  • Use stored procedures, especially to encapsulate complex data access logic and transactional behaviors—When making sequential calls where the second call depends on data returned by the first, using a stored procedure to perform the logic to make the second call eliminates one or more round trips and accelerates performance. This approach will naturally reduce round trips and resource locking and usage on the server side.

  • Minimize the use of cursors or row by row access—Use set based operations where possible. If cursors must be used, use a client side cursor.

  • Use table-valued parameters to send multiple rows to Microsoft Azure SQL Database in each round trip—Microsoft Azure SQL Database uses table-valued parameters just as on-premises versions of the SQL Server Database Engine do. Table-valued parameters help reduce multiple calls to the same stored procedure to process a set of records/values. You can also pass tabular parameters to a single parameterized query, such as a SELECT, INSERT, UPDATE, or DELETE command.
    For more information, see the following Books Online topic, Use Table-Valued Parameters.

  • Use local caching where possible—Local caching can allow you to reuse the same results without multiple round trips to Microsoft Azure SQL Database. Also, keep in mind you can cache calls to store procedures that return the same value when given the same.

  • Use caching where possible—Use caching for read-only lookup data to minimize the network traffic to Microsoft Azure SQL Database. For more information, see Reduce Network Round Trips by Using Azure Caching.

  • Cache metadata and data where possible.

  • Avoid metadata retrieval at runtime when possible.

  • Avoid classes like SqlCommandBuilder—These classes query metadata at runtime, which results in additional roundtrips.

  • Batch SQL statements together when possible— You can concatenate multiple Transact-SQL statements in a single batch command to retrieve multiple result sets or to execute multiple DML operations in a single network round trip. This approach is especially true when dealing with large amounts of consecutive INSERT operations.

  • Avoid application-based transaction management—Encapsulating transaction management operations—BEGIN TRAN, COMMIT/ROLLBACK—into stored procedures can reduce network roundtrips and locking.

Follow General Best Practices for On-Premises Databases

After you minimize the distance between the data and your users, and minimize network round trips, the next step is to ensure that your application follows the general best practices for on-premises databases. By applying well-known best practices and recommendations to your application’s data access layer, along with performance and optimization best practices, you should notice a “multiplied” benefit effect in a high-latency environment, like the cloud.

The general best practices for database interaction with on-premises databases include the following recommendations:

  • Open a connection late and close it as soon as possible—To minimize resource utilization and minimize the possibility of throttling, open a connection in the application only at the point at which the code needs that connection. Furthermore, return the connection to the pool immediately after the code finishes with the connection by disposing of the connection object. (An exception to this return policy is when there is more immediate work to complete. In this case, return the connection only when the all the most immediate work is done.)

  • Leverage connection pooling—At the process level, connection pools will contain connections targeting the same database and using the same security context. Where possible use the same connection string for all connections that have the same characteristics.

  • Retrieve only the data you need—Carefully define your SELECT list and WHERE clause. This approach allows you to minimize the use of network bandwidth, and also allows you to effectively index your database for performance.

  • Keep transactions as short as possible, and avoid involving unnecessary resources—Suboptimal data model design and excessive round trips between the application code and databases are common issues, but may not be critical in on-premises deployments due to a low latency connectivity environment. These applications may also be difficult to modify because of the following reasons:

    • Existing architectural constraints.

    • Monolithic design where data access and business logic are tightly connected and interdependent.

    • Some aspects, such as row-by-row data processing, that are required by the application logic itself and hard to modify without a massive rewrite.

Besides these general best practices, there are additional best practices that relate to the technologies that you use in your application. For instance, applications that use the .NET Framework are able to use technologies like ADO.NET, Entity Framework, and WCF Data Services. These technologies offer reduced development times and provide higher flexibility in implementing the data access layer for any application style. These technologies also fit well with a service orientation by decoupling layers, offering schema flexibility, using open data architectures (such as, OData) and offering “disconnected” design by nature. Yet, despite their benefits, these technologies may generate the same kind of issues described for legacy applications, such as chattiness, if you do not take into consideration an appropriate data design and operation optimization (this includes optimal data representation, cache usage, and batch data–related operations to reduce unnecessary round trips).

The following sections describe some of the best practices for these technologies, as well as employing asynchronous programming to your Azure applications.

Best Practices for ODBC and JDBC

For data access libraries, such as ODBC and JDBC, where client-side support for intelligent operations (such as sorting, filtering, and so on) is limited, you can still apply existing optimization techniques like reducing cursor-based operations and row-by-row processing. When cursors must be used, use read only, forward only cursors to retrieve values, and use SQL commands to make data modifications rather than using positioned updates in cursors.

Best Practices for ADO.NET

With ADO.NET, there are several optimizations that can be applied in order to maximize the efficiency of your data access code:

  • Choose the appropriate execution mode with SqlCommand—For example if you only need to retrieve a scalar value, use the ExecuteScalar() method, or the ExecuteNonQuery() if you don’t need to retrieve a result set

  • Use the UpdateBatchSize property when using the SqlDataAdapter class to execute multiple operations—this batches multiple commands as they are transmitted across the network thus minimizing network roundtrips.

  • Use SqlDataAdapter to implement direct database interactions through the use of stored procedures for SELECT, INSERT, UPDATE and DELETE operations.

  • Set the SerializationFormat property to Binary when using Datasets as a client side cache—This reduces the amount of data transmitted over the wire.

Best Practices for Entity Framework

When it comes to data access development, there is a lot of interest in Entity Framework for the following reasons:

  • Provides a rich object-to-relational mapping environment that can dramatically reduce development time.

  • Introduces more flexibility by decoupling physical database schema definition from the conceptual representation of your application data.

  • Provides a complete set of services to deal with persistence.

  • Reduces network roundtrips by being able to load result sets from Microsoft Azure SQL Database into object sets in the application memory, and reuse these in a disconnected fashion without the need to interact with the back end for every operation.

However, as with any other programming tool, Entity Framework can generate some performance issues if you fail to apply specific attention to dealing with database interactions. Furthermore, the environment amplifies these performance concerns.

To optimize Entity Framework usage with Microsoft Azure SQL Database, follow these best practice guidelines:

  • Explicitly disable tracking of object state at the ObjectStateManager level for both entities and relationships— Apply this guideline if your application does not need to track object state because of a typical read only usage.

  • Disable Lazy Loading to better control the interaction between your code and the database— In order to reduce round trips, you would preload everything you need in a single interaction. You can batch multiple commands both in loading and in persisting data to the data store can be applied by extending the object context with specific methods.

  • Use stored procedures for data store interactions where possible—Where stored procedures cannot be used, use parameterized queries and indexed views as a way to improve performance.

  • Execute multiple database operations in a single round trip by integrating table-valued parameters and mapping object sets to stored procedure tabular parameters—You can use this approach even if it is not directly supported by the design tools.

  • Map multiple result sets to objects in a single operation where possible.

  • Map object sets to the System.Data.SqlClient.SqlBulkCopy.WriteToServer() method for massive data insert activities—The SQLBulkCopy method allows the SQL Server Database Engine and Microsoft Azure SQL Database to make use of bulk operation logging optimizations that allows large inserts to be performed more quickly in many cases.

For more information, see Using the Entity Framework to Reduce Network Latency to Azure SQL Database.

Best Practices for Asynchronous Programming

Some database operations, such as command executions, may take a significant amount of time to complete. In such cases, single-threaded applications must block other operations and wait for the command to finish before continuing their other operations. In contrast, assigning the long-running operation to a background thread allows the foreground thread to remain active throughout the operation.

ADO.NET supports these same design patterns in its SqlCommand class. Specifically, pairing the BeginExecuteNonQuery(), BeginExecuteReader(), and BeginExecuteXmlReader() methods with the EndExecuteNonQuery(), EndExecuteReader(), and EndExecuteXmlReader() methods, respectively, provides asynchronous support.

These asynchronous methods will not save you from applying all the optimizations and best practices mentioned in the previous sections. However, by allowing other operations to run in parallel with the query in Microsoft Azure SQL Database, these asynchronous methods help reduce the impact of long running database operations in your data access layer.

Syntes du, dette var nyttigt?
(1500 tegn tilbage)
Tak for din feedback

Fællesskabsindhold

Tilføj
Vis:
© 2014 Microsoft