SQL Azure故障排除以及查詢最佳化

SQL Azure資料庫是微軟提供的雲端式技術的關係型資料庫服務。雲端式技術的資料解決方案可以提供很多好處，包括快速provisioning, 更經濟的的延展性，高可用性並且減少了管理開支。本文介紹了SQL Azure中可用的動態管理檢視以及怎樣講它們用於故障排除。

效能注意事項

SQL Azure使用SQL Server 2008的資料引擎作為核心。SQL Server 2005引入的動態管理檢視DMVs）已經成為了強大的故障排除工具，被用於排除從系統狀態到死結資訊等各個層次的效能問題。在最初發布的SQL Azure中，大部分的DMV是被禁用的，在之後的計劃更新Service Update）中才被啟用。DMV暴露了執行個體層級的資訊。由於SQL Azure是一個共用架構模型，因此需要修改DMV來限制DMV的輸出使其只顯示適當的資訊。下面的DMV已經在第一階段中被啟用了。

這些DMV在本地部署的SQL Server中需要VIEW SERVER STATE的許可權。在SQL Azure中，查詢這些DMV需要新的名為VIEW DATABASE STATE的許可權。

和事務有關的DMV

sys.dm_tran_active_transactions：返回與您的當前邏輯資料庫的事務有關的資訊。

sys.dm_tran_database_transactions：返回有關使用者資料庫級事務的資訊。

sys.dm_tran_locks：返回有關當前處於活動狀態的鎖管理器資源的資訊。向鎖管理器發出的已授予鎖或正等待授予鎖的每個當前活動請求分別對應一行。結果集中的列大體分為兩組：資源群組和請求組。資源群組說明進行中鎖請求的資源，請求組說明鎖請求。

sys.dm_tran_session_transactions：返回關聯事務和會話的相關資訊。

和執行有關的DMV

sys.dm_exec_connections: 返回有關與 Microsoft SQL Azure 資料庫建立的串連的資訊。

sys.dm_exec_query_plan: 以 XML 格式返回計劃控制代碼指定的批查詢的執行程序表。計劃控制代碼指定的計劃可以處於緩衝或正在執行狀態。

sys.dm_exec_query_stats: 返回緩衝查詢計劃的彙總效能統計資訊。緩衝計劃中的每個查詢語句在該視圖中對應一行，並且行的生存期與計劃本身相關聯。在從緩衝刪除計劃時，也將從該視圖中刪除對應行。

sys.dm_exec_requests: 返回有關在 Microsoft SQL Azure 伺服器內執行的每個請求的資訊。

sys.dm_exec_sessions: 返回有關所有活動的使用者串連和內部任務的資訊。

sys.dm_exec_sql_text: 返回由指定的 sql_handle 標識的 SQL 批處理的文本。該資料表值函式將替換系統函數 fn_get_sql。

sys.dm_exec_text_query_plan: 為 Transact-SQL 批查詢或批查詢中的特定語句返迴文本格式的執行程序表。執行計畫控制代碼指定的查詢計劃可處於緩衝狀態或正在執行狀態。此資料表值函式與 sys.dm_exec_query_plan 類似，但存在以下差異：

查詢計劃的輸出以文字格式設定返回。

查詢計劃的輸出無大小限制。

和資料庫相關的DMV

sys.dm_db_partition_stats：為當前資料庫中每個分區返回頁和行計數資訊。只有那些與範圍內的伺服器直接相關的資訊會被暴露出來。在SQL Azure中，那些在SQL Server會返回執行個體級伺服器資訊的列將會返回空值。因此，如果你有一些用於SQL Server的自訂故障排除查詢，你可以直接在SQL Azure中運行它們而不需要進行修改。

識別效能不佳的查詢語句

SQL Server為所有執行的查詢產生一個最佳化過的查詢計劃。這使得SQL Server的最佳化器可以在同樣或者類似的查詢執行的時候重用查詢計劃，從而在最短的時間內接收資料。一旦資料發生變化，資料列上的統計資訊會使得查詢計劃變得到期和低效。為了獲得應用程式的最佳效能以及一致的使用者體驗，識別並且調試這些語句非常的重要。之前列出的DMV可以直接協助識別那些有問題的查詢。

下面的是一些用於標識這些有問題的查詢的基本查詢語句：

過多的重新編譯：

 

  
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 
 

不夠高效的查詢計劃

 

  
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瓶頸  
  
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 
 

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

編者推薦】