Azure SQL Database: Troubleshoot and Optimize Queries

  • Article

Microsoft Azure SQL Database is a cloud-based relational database service by Microsoft. SQL Database provides web-facing database functionality as a utility service. Cloud-based database solutions such as SQL Database can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This paper provides guidelines on the Dynamic Management Views that are available in SQL Database and how they can be used for troubleshooting purposes.

Note

If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

Performance Considerations

SQL Database is a cloud-based relational database with SQL Server 2008 engine at its core. Dynamic Management Views (DMVs) were introduced with SQL Server 2005 and since then, have become a very powerful tool for troubleshooting issues at all levels of performance,  from the state of the system to deadlock information, etc. While most of these DMVs have been disabled for the first release of SQL Database, they are being enabled in phases as part of the scheduled Service Updates (SUs) to SQL Database. DMVs expose information at the instance level. Since SQL Database is a shared infrastructure model, the DMVs have to be modified to filter the output and show information only as appropriate. In this effort, the following DMVs have been enabled in the first phase.

These DMVs being released typically require VIEW SERVER STATE permissions in an on-premise SQL Server. The new permission level required on SQL Database would be VIEW DATABASE STATE to query these DMVs.

Following are the transaction related DMVs released in January SU:

? sys.dm_tran_active_transactions - returns information about transactions for the SQL Database server

? sys.dm_tran_database_transactions - returns information about transactions at the user database level

? sys.dm_tran_locks - returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

? sys.dm_tran_session_transactions - returns correlation information for associated transactions and sessions.

Following are the execution related DMVs released in January SU:

sys.dm_exec_connections - returns information about the connections established to SQL Database and the details of each connection.

sys.dm_exec_query_plan - returns the showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.

sys.dm_exec_query_stats - returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

sys.dm_exec_requests - returns information about each request that is executing within SQL Database.

sys.dm_exec_sessions - returns one row per authenticated session on SQL Database.

sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.

sys.dm_exec_text_query_plan - returns the showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing. This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences:

  • The output of the query plan is returned in text format.
  • The output of the query plan is not limited in size.

The following DMV is released in the January SU:

sys.dm_db_partition_stats - returns page and row-count information for every partition in the current database.

Only information that is directly related to the server in scope is exposed. Columns that would otherwise return server information at the instance level would return NULL in SQL Database. So if you have custom troubleshooting queries that you are already using on an on-premise SQL Server then you don't need to modify those queries to execute on SQL Database. They can be run directly in SQL Database seamlessly.

Truncation of Trans Logs

Truncate SQL Trans Logs will enable you to have better DB maintenance.

Identifying poorly performing queries

SQL Server generates an optimized query plan for all the queries that are executed. This allows the SQL Server optimizer to reuse the query plan when the same or similar query is executed to retrieve the data in the fastest time possible. As the data changes and the statistics on those data change the query plans become out of date and can become inefficient. It is important to identify these queries and tune them for optimal performance of the application and consistent user experience. The DMVs discussed above directly help in identifying problematic queries.

Following are some basic queries to troubleshoot poor performance of queries:

Excessive recompiles:

select top 25
 
    sql_text.text,
 
    sql_handle,
 
    plan_generation_num,
 
    execution_count,
 
    dbid,
 
    objectid
 
from
 
    sys.dm_exec_query_stats a
 
    cross apply sys.dm_exec_sql_text(sql_handle) as  sql_text
 
where
 
    plan_generation_num >1
 
order by plan_generation_num desc

Inefficient query plans:

select
 
    highest_cpu_queries.plan_handle,
 
    highest_cpu_queries.total_worker_time,
 
    q.dbid,
 
    q.objectid,
 
    q.number,
 
    q.encrypted,
 
    q.[text]
 
from
 
    (select top 50
 
        qs.plan_handle,
 
        qs.total_worker_time
 
    from
 
        sys.dm_exec_query_stats qs
 
    order by qs.total_worker_time desc) as  highest_cpu_queries
 
    cross apply sys.dm_exec_sql_text(plan_handle) as  q
 
order by highest_cpu_queries.total_worker_time desc

I/O Bottlenecks:

select top 25
 
    (total_logical_reads/execution_count) as  avg_logical_reads,
 
    (total_logical_writes/execution_count) as  avg_logical_writes,
 
    (total_physical_reads/execution_count) as  avg_phys_reads,
 
     Execution_count,
 
    statement_start_offset as  stmt_start_offset,
 
    sql_handle,
 
    plan_handle
 
from sys.dm_exec_query_stats  
 
order by
 
(total_logical_reads + total_logical_writes) Desc

See Also

  • [[Windows Azure SQL Database TechNet Wiki Articles Index]]
  • [[Windows Azure SQL Database Connection Management]]

The author of the original information contained in this article is Dinakar Nethi.

Other Languages

This article is also available in other languages, including Russian.

Wiki: Решение проблем и оптимизация запросов Azure SQL Database (ru-RU)