SATıŞ: 1-800-867-1389
EN
Bu içerik dilinizde bulunmamaktadır ancak İngilizce sürümüne buradan bakabilirsiniz.

Azure SQL Database Resource Management

Updated: September 10, 2014

This topic describes how Azure SQL Database controls the resources dedicated to the databases to achieve maximum performance predictability. The amount of resources available to each database depends on the performance level assigned to the database. Use the information in this topic to understand the mechanisms used to control resources. This information is useful when developing your application for optimal performance. The topic includes some recommended practice for issues encountered in each category. 

Azure SQL Database uses three different mechanisms to control resources:

  • Resource Governance

    CPU, Memory, Log writes, and query IOPS are subject to resource governance. This newly introduced mechanism, in contrast to the enforcement of maximum limits and throttling, does not prevent or terminate the execution of queries, but queues queries and grants resources to the queued queries as they become available. 

  • Enforcement of Limits

    Maximum limits are enforced for the amount of connections that can be opened to a database as well as on the on parallel execution of queries (worker threads). 

  • Throttling

    Throttling occurs in the situation where a machine hosting databases reaches a critical amount of load, leading to a potential system outage. Throttling is a last resort mechanism of the system to protect itself from overloading and is rarely encountered.

One of the design goals of the Basic, Standard, and Premium service tiers, is for Azure SQL Database to behave as if the database is running on its own machine, completely isolated from other databases. Resource governance emulates this behavior during the execution of queries. If the aggregated resource utilization reaches the maximum available CPU, Memory, Log Writes and Query IOPS resources assigned to the database, resource governance will queue queries in execution and assign resource to the queued queries as they free up.

As on a dedicated machine, utilizing all available resources will result in a longer execution of currently executing queries, which can result in client timeouts. Applications with aggressive retry logic and applications that execute queries against the database with a high frequency can encounter error when trying to execute new queries due to unavailability of worker threads.

Recommendations: Monitor the resource utilization as well as the average response times of queries when nearing the maximum utilization of a database. When encountering long running queries you generally have three options: 

  1. Reduce the amount of incoming requests to the database to prevent timeout and the pile up of worker threads

  2. Assign a higher performance level to the database.

  3. Optimize queries to reduce the resource utilization of each query. For more information, see the Query Tuning/Hinting section in the Azure SQL Database Performance Guidance article.

Azure SQL Database employs resource governance by setting a maximum limit on concurrent worker threads (requests) and concurrent sessions for each database. The resource governance mechanism varies depending on whether the target database is a Web/Business edition database or a premium database. For more information, see Azure SQL Database Resource Governance.

The number of connections to a SQL database as well as the number of requests that can be processed in parallel are restricted. SQL Database allows the number of connections to the database to be greater than the number of concurrent requests to support connection pooling. 

While the amount of connections that are available can easily be controlled by the application, the amount of parallel requests is often times harder to estimate and to control. Especially during peak loads when the application either sends to many requests or the database cannot reaches its resource limits and starts piling up worker threads due to longer running queries, error <NUMBER> can be encountered.

Recommendations:  When running out of worker threads you generally have three options:

  1. Reduce the amount of incoming requests to the database to prevent the pile up of worker threads

  2. Assign a higher performance level to the database which will allow a higher number of concurrent requests. For more information, see Azure SQL Database Service Tiers and Performance Levels.

  3. Optimize queries to reduce the resource utilization of each query. For more information, see the Query Tuning/Hinting section in the Azure SQL Database Performance Guidance article.

The following table lists the resource limits beyond which Azure SQL Database denies request or terminates connections to the affected resource, and an error code is returned

 

Resource Limit Error code returned

Database Size

Depends on the database quota (MAXSIZE)

40544

Transaction Duration

State 1: 24 hours

State 2: 20 seconds if a transaction locks a resource required by an underlying system task

40549

Transaction Lock Count

1 million locks per transaction

40550

Tempdb

State 1: 5 GB of tempdb space

State 2: 2 GB per transaction in tempdb

State 3: 20% of total log space in tempdb

40551

Transaction Log Length

State 1: 2 GB per transaction

State 2: 20% of total log space

40552

Memory Usage

16 MB memory grant for more than 20 seconds

40553

For details on each of the error codes, see Azure SQL Database Resource Limits.

Throttling severity falls into one of the following two stages:

  • Soft throttling: This is the first stage when machine resources such as transaction log, I/O, and storage exceed predefined safety thresholds. SQL Database selects a subset of the databases consuming the most resources, and then throttles their activities. Not all the databases on the machine undergo throttling, just the ones using the most of the resources. Usage below the predefined threshold indicates that there are sufficient resources for all the databases on the server.

  • Hard throttling: This is the second and final stage when a machine is critically impacted due to overload. With hard throttling, no more new connections are allowed to the databases hosted on the machine until resources are freed up. SQL Database returns error messages for new connection attempts indicating the resource that has been exceeded.

For more information about the engine throttling mechanism, the corresponding error code that is returned, and recommendations on how to address it, see. Azure SQL Database Throttling

See Also

Bunu faydalı buldunuz mu?
(1500 karakter kaldı)
Geri bildiriminiz için teşekkür ederiz
Show:
© 2014 Microsoft