SQL Azure troubleshooting and Query Optimization

SQL Azure database is a cloud-based relational database service provided by Microsoft. Cloud-based data solutions provide many benefits, including fast provisioning, more economical scalability, high availability, and reduced management costs. This article describes the dynamic management views available in SQL Azure and how they are used for troubleshooting.

Performance Considerations

SQL Azure uses the data engine of SQL Server 2008 as the core. The dynamic management view DMVs introduced by SQL Server 2005 has become a powerful troubleshooting tool used to eliminate performance problems at various levels, from system status to deadlock information. In the originally released SQL Azure, most of the DMV is disabled and will be enabled only when the Service Update is planned later. DMV exposes instance-level information. Because SQL Azure is a shared architecture model, you need to modify DMV to limit DMV output so that it only displays the appropriate information. The following DMV has been enabled in the first phase.

The DMV needs the permission to VIEW the Server STATE in the locally deployed SQL SERVER. In SQL Azure, querying these DMV requires a new permission named VIEW DATABASE STATE.

Transaction-related DMV

Sys. dm_tran_active_transactions: returns information related to transactions in your current logical database.

Sys. dm_tran_database_transactions: returns information about user database-level transactions.

Sys. dm_tran_locks: returns information about the currently active lock Manager resource. Each currently active request that has been granted a lock to the lock manager or is waiting for the lock to be granted corresponds to one row. The columns in the result set are generally divided into two groups: Resource Group and request group. The Resource Group indicates the resource in which the lock request is in progress, and the request group indicates the lock request.

Sys. dm_tran_session_transactions: returns information about the associated transactions and sessions.

DMV related to execution

Sys. dm_exec_connections: returns information about the connection to the Microsoft SQL Azure database.

Sys. dm_exec_query_plan: return the display plan of the batch query specified by the Plan handle in XML format. The plan specified by the scheduler handle can be in the cache or running state.

Sys. dm_exec_query_stats: return the aggregate performance statistics of the cache query plan. Each query statement in the cache plan corresponds to a row in this view, and the row survival is associated with the plan itself. When a plan is deleted from the cache, the corresponding row is also deleted from the view.

Sys. dm_exec_requests: returns information about each request executed on the Microsoft SQL Azure server.

Sys. dm_exec_sessions: returns information about all active user connections and internal tasks.

Sys. dm_exec_ SQL _text: return the SQL batch processing text identified by the specified SQL _handle. This table value function replaces the system function fn_get_ SQL.

Sys. dm_exec_text_query_plan: A display plan in text format returned by a specific statement in a Transact-SQL batch query or batch query. The query plan specified by the execution plan Handle can be in the cache or running state. This table value function is similar to sys. dm_exec_query_plan, but has the following differences:

The query plan output is returned in text format.

The query plan output has no size limit.

Database-related DMV

Sys. dm_db_partition_stats: returns the page and row count information for each partition in the current database. Only the information directly related to the servers in the range will be exposed. In SQL Azure, columns that return instance-level Server information on SQL Server return NULL values. Therefore, if you have some custom troubleshooting queries for SQL Server, you can run them directly in SQL Azure without modifying them.

Identifying query statements with poor performance

SQL Server generates an optimized query plan for all executed queries. This allows the SQL Server optimizer to reuse the query plan when the same or similar query is executed, so as to receive data in the shortest time. Once the data changes, the statistics on the data column will make the query plan expire and inefficient. To achieve the best performance and consistent user experience of the application, it is very important to identify and debug these statements. DMV listed earlier can help identify problematic queries directly.

Below are some basic query statements used to identify these problematic queries:

Excessive recompilation:

 
 

  
  
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 plan

 
 

  
  
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 bottleneck
  
  
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
  
  
(Total_logical_reads + total_logical_writes) desc
 
 

Translation: http://social.technet.microsoft.com/wiki/contents/articles/troubleshoot-and-optimize-queries-with-sql-azure.aspx

Recommended by editors]