SQL Server 2005 五個動态管理對象

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中的動态管理對象。

本文轉自yonghu86 51CTO部落格，原文連結：http://blog.51cto.com/yonghu/1321478，如需轉載請自行聯系原作者