SQL Server 2005動態管理物件

SQL Server 2005的新功能為動態管理物件，它們是在指定時間返回某個資料庫例項的特殊狀態資訊的資料庫檢視或函式。這些物件允許資料庫管理員或開發者對資料庫伺服器進行高效監控。動態管理物件取代了SQL Server以前版本中的許多系統表格，但也為資料庫管理員提供更多觀測手段。

　　伺服器上需要監控的內容，幾乎都有對應的動態管理物件。既然有這麼多動態管理物件，在這篇文章中逐一進行討論是不實際的。例如，共有12類動態管理物件，但這裡我主要討論我認為相當重要的五個物件，它們分別來自與SQL Server作業系統、目錄和執行有關的類中。

　　這些物件被分為兩類：資料庫級別和伺服器級別。SQL Server 2005的安全構架有些不同，所以你必須採取一些措施保證你能夠使用本文討論的檢視和函式。你必須保證使用這些例子的使用者能夠檢視物件的SERVER STATE和DATABASE STATE。可以應用GRANT語句來完成這一點。

　　在我開始介紹動態管理物件之前，你應該建立一個可用在所有例子裡面的表格。列表A是建立這個表格的程式碼。

　　SQL Server作業系統相關動態管理物件

　　sys.dm_os_performance_counters：這個檢視返回的資訊列舉與SQL Server直接相關的效能統計。返回的資訊代表通過Windows效能監控器顯示的外部效能計數器。在SQL Server 2000中，這些資料以一個叫做sysperfinfo的系統表格來表示，SQL Server 2005中也有這個表格，但僅用於向後相容。將來的版本可能會刪除這個表格。

　　要了解如何應用這個檢視，而不是執行Windows效能監控器來獲取系統資訊，請執行列表B中的查詢程式碼。這個查詢將返回伺服器當前時刻的快取命中率(Buffer Cache Hit Ratio)。快取命中率指在記憶體中所發現的SQL Server請求頁面的百分比。如果伺服器運轉正常，這個值一般在90%以上。如果這個值低於90%，則意味著伺服器將會去硬碟那裡恢復資料頁面，這可能是伺服器需要更多記憶體的訊號。

　　如果你為了同樣的目的，準備執行上面的查詢，並將得到的結果與執行Windows效能監控器顯示的結果進行比較(SQL Server：緩衝器管理器快取命中率)，你會發現這些資料幾乎是一樣的。稍稍研究一下，就可以根據這個表格開發出你自己的指令碼庫，而不必總是執行Windows效能監控器。

　　Caveat：這個檢視僅限於SQL Server相關的計數器使用。如果你希望獲得SQL Server之外的資料，你還是要使用Windows效能監控器。

　　目錄相關動態管理檢視和函式

　　sys.dm_db_index_physical_stats：這個動態管理函式為資料表格和檢視返回相關資料及目錄資訊。檢視列表C。這個函式接受5個引數：資料庫的ID、所討論的表格或檢視的ID、表格或檢視上的一個特定目錄的ID(被我忽略)、分割槽號(也被我忽略)和一個模式，我將它指定為“DETAILED”。將這些引數提交給這個函式會允許你檢視DMVTest表格中的所有目錄細節。

　　在前面定義的表格中執行列表C中的查詢，你會發現表格中既沒有頁面也沒有記錄。在DMVTest中增加一條記錄，再執行查詢，就會發現表格中增加了一個頁面。

　　INSERT INTO DMVTest(C2， C3)
　　VALUES(1，1)

　　這個函式返回的另一個特別有趣的域為avg_page_space_used_in_percent域，它說明資料頁面有多大。從查詢中發現資料頁面約為0.25%。你可以應用這個資料來開發程式，檢視資料庫表格，並根據程式中設定的標準，重新為它們編寫目錄。

　　執行相關的動態管理檢視和函式

　　sys.dm_exec_requests：這個檢視顯示發生在SQL Server例項中的每個請求的有關資訊。當你研究伺服器阻塞時，由此檢視提供的資訊特別有用。讀、寫、會話設定和 blocking_session_id是這個檢視返回的一些有用資料。blocking_session_id欄指出系統上阻塞資料庫請求的會話。稍後，我將舉出一個例子，說明如何確定使用者執行的阻塞請求的語句。

　　sys.dm_exec_sessions：SQL Server上每個得到驗證的會話由它返回一行。在你希望找出連線到資料庫伺服器的所有使用者時，可用到這個檢視。它返回session_id、連線通過它建立會話執行的讀和寫的程式、以及其它一些會話設定。這個檢視還包含一個叫做is_user_process的BIT域，在查詢這個表格時你可能會用到它。數值1表示會話為使用者會話而非系統定義的會話。

　　sys.dm_exec_sql_text：這是一個根據sql操作返回SQL Server語句的動態管理函式。對某些動態管理函式而言，SQL Server 2005將正在執行的sql語句以雜湊值儲存。這個雜湊值可由sys.dm_exec_sql_text動態管理函式解析。但是，任何時候你希望將一個函式與另一個物件連線起來，並從表格或檢視中提交一個域值時，就會需要用到新的APPLY運算子。SQL Server 2005以前的版本並不具備這一功能。(欲瞭解SQL Server 2005中APPLY運算子的詳細資訊，請參閱這篇文章。)

　　現在我們討論一下如何應用這三個動態管理函式。列表D中的查詢列舉出資料庫中所有當前使用者請求執行的會話、阻塞會話、程式、主機和SQL Server語句。

　　注意正在使用dm_exec_sql_text函式，並從 sys.dm_exec_requests檢視中提交sql_handle域。由sys.dm_exec_requests和 sys.dm_exec_sessions檢視返回的每一行，都將呼叫sys.dm_exec_sql_text函式。

　　測試

　　對它們進行測試是學習這些新型動態管理物件的最佳方法。SQL Server線上書籍提供了使用這些物件的一些資料，但動手使用要比閱讀學到更多內容。投入一點努力，你就會發現許多應用物件的新方法，不僅包括這個例子中列出的物件，還包括SQL Server 2005中的動態管理物件。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-564919/，如需轉載，請註明出處，否則將追究法律責任。