SQL Server性能優化：初識元數據優化(2)

　　DMV是以SQL Server實例為級別進行保存的。也就說，如果在服務器上面，安裝了一個SQL Server，那么此時這個SQL Server就是一個實例，那么這個實例里面的所有的數據庫的DMV都是保存在相同的內存中。當然，我們在使用的時候，可以根據需要只提取更低級別的DMV，例如提取某個數據庫的DMV，某個表的DMV，甚至是某個查詢的DMV。

　　因為DMV信息是保存在內存中的，我們不需要額外的操作，只需要將這些信息取出來，按照我們的要求進行運算，統計，分析就夠了，獲取信息的數據非?？?，并且不會對服務器產生壓力。另外，因為DMV是SQL Server本身保存的，并且已經做了統計的信息，所以，數據更加的接近于數據庫本身的狀態。

　　SQL Server運行的時候越長，DMV中保存的信息就越多(當然，DMV非常小，不會對內存造成壓力)，利用DMV分析就越準確。這一點和之前的Profiler和DTA是完全不一樣的。唯一的一個問題就是：每次SQL Server服務重啟，這些保存在內存中的DMV信息就沒有了，又是從頭開始，慢慢的保存。當然，對于這個問題，我們有很多的解決方案，例如，我們可以定期的將DMV的信息導出，保存在磁盤上。

　　DMV包含了的信息有很多：索引相關的，查詢執行相關的，還有SQL Server OS相關的，Common Language Runtime(CLR)相關的，事務相關，安全相關的，資源管理相關的，數據備份相關的，I/O相關，全文查找相關，數據庫鏡像相關的，等等信息。所以，我們完全可以使用已經保存在DMV中的信息來進行我們的分析。

　　因為SQL Server內部的DMV很多，我們本次系類的文章注重在性能分析與調優上，所以，我們主要關注以下幾類DMV：索引相關，執行相關，SQL Server OS相關，CLR相關，事務相關，I/O相關，數據庫相關。

　　下面，我們分析一個查詢的運行，看看在這個過程中SQL Server都記錄了哪些信息(或者說，DMV中保存了什么信息)：

　　查詢的執行計劃(即描述了一個查詢是如何被執行的)

　　什么索引被使用

　　什么索引本來應該被用到，但是又沒有使用。(因為此時存在缺失索引的性能問題)

　　I/O的狀態(包含邏輯I/O操作和物理的I/O操作)

　　查詢執行消耗的時間

　　查詢等待其他資源消耗的時間

　　查詢在等待什么資源

　　通過分析這些信息，不僅僅可以使得我們更好的理解查詢的是如何工作的，并且還可以讓我們思考如何更加合理，高效的使用資源，提高性能。

　　一般而言，在使用DMV的時候，我們很多時候也需要將其與DMF(Dynamic Management Functions)一起使用。我們可以簡單的將DMF理解為SQL Server內部的一系列函數。例如，通過分析sys.dm_exec_query_stats，可以知道查詢的相關信息，如果將sys.dm_exec_query_stats里面的sql_handle傳給sys.dm_exec_sql_text，那么，我們就可以知道查詢的語句的內容。

　　DMV簡單示例

　　為了使得大家對DMV有更加深入的了解，我們首先來看看一個使用DMV來找出那些查詢運行的最慢。(朋友們可能對這里提到的相關的DMV和DMF不太熟悉，沒關系，后續文章會介紹)

　　在SQL Server的查詢分析器中，運行一下SQL 語句：

　　此時，運行的結果如下：

　　在這個查詢中，我們主要是通過將sys.dm_exec_query_stats這個DMV與sys.dm_exec_sql_text和sys.dm_exec_query_plan這兩個DMF結合，通過分析查詢所消耗的時間，然后按照從高到低進行排序，選出前20個進行展示。

　　從這個示例中，我們可以知道幾點：

　　查詢DMV時，應該盡可能的將對數據庫的影響降到最小。所以，我們在查詢的最上面，加上了：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。因為每次在運行查詢的時候，或多或少的會對數據庫產生不同程度的鎖定，并且鎖定的級別各不一樣。通過上面的設置，就告訴SQL Server，接下來的執行的查詢將鎖定的級別定為：Read Uncommitted。從而將影響減小到最小。

　　每次進行性能問題診斷的時候，首先要解決最嚴重的性能問題。所以，我們此處只是找出前20個運行最慢的查詢語句。

　　雖然原生的DMV信息提供了很多的信息，但是很多時候需要對DMV進行復雜的統計分析。(這個成本比分析SQL Server Profiler收集到的數據小，也更加簡單，準確。)

　　DMV可以解決哪些問題

　　看完了上面的簡單的例子之后，相信朋友們對DMV有了一個感性的認識，下面，我們就來看看，利用DMV，我們可以解決哪些問題。

　　故障診斷

　　診斷就是要識別出問題的所在。有很多的方式和工具可以幫助我們達到這個目的，但是，有了DMV，可能效率會更快：沒有什么比分析SQL Server內部的元數據來的更快。

　　很多時候，對問題的診斷也是性能調優的第一步，搞清楚了問題，才好對癥下藥。

　　利用DMV可以診斷出以下問題：最慢的查詢語句，常見的等待與阻塞，沒有用的索引，大量的I/O操作，利用率最低的執行計劃。

　　正如之前所說，我們可以在不同的級別上面分析問題，例如從整個服務器級別，數據庫級別，甚至是某個查詢。我們可以通過在獲取DMV信息時，設置獲取信息的條件來辦到。例如，在上一小節的示例中，就是獲取整個SQL Server中找出最慢的前20個查詢，如果需要，我們完全可以將條件縮小到某個數據庫。

　　很多時候，在識別問題的時候，不是那么容易，僅僅通過一個DMV就搞定了的，需要和DMF結合。甚至要和其他的DMV一起結合分析(在后續文章中，我們會理解的更加深刻)。

　　診斷出了問題，是一個方面，解決問題也尤為重要。

　　性能調優

　　性能調優主要是利用相關的技巧技術之前診斷中出現的問題，從而提升性能。我們后續會詳細講述，這里就不再贅述了。

　　狀態監控

　　很多的DMV(特別是那些以 sys.dm_exec_開頭的)都反映了數據庫服務器執行的狀態。通過查看這些DMV，我們可以清楚的知道數據庫服務器的現在的狀態和歷史的狀態(當然，如何SQL Server服務被重啟，那么之前的信息都丟失了，除非定期做了保存)。例如，數據庫需要做批處理等長時間的操作，如果其中操作執行超時或運行的非常慢，這個時候，我們就可以查詢DMV來分析。如果采用Profiler或者相關的Profiler腳本跟蹤，會對數據庫服務器的壓力相當大。再如，還可以分析數據庫中現在有哪些查詢在運行，有多少請求在處理，打開多少連接等等，主要是對數據庫的操作，都可以通過DMV查詢到。

原文轉自：http://www.anti-gravitydesign.com