系統動態管理檢視System Dynamic Management Views

本主題的適用對象: 是(從 2008年起) 的 SQL Server是Azure SQL Database是Azure SQL 資料倉儲yesParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

動態管理檢視和函數傳回伺服器狀態資訊,這項資訊可用來監視伺服器執行個體的健全狀況、診斷問題和調整效能。Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

重要

動態管理檢視和函數傳回內部實作特定狀態資料。Dynamic management views and functions return internal, implementation-specific state data. 其結構描述和傳回的資料在 SQL ServerSQL Server 的未來版本中可能會改變。Their schemas and the data they return may change in future releases of SQL ServerSQL Server. 因此,未來版本中的動態管理檢視和函數不一定與這個版本中的動態管理檢視和函數相容。Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. 例如,在 SQL ServerSQL Server 的未來版本中,Microsoft 可能會在資料行清單結尾加入資料行,藉以擴充任何動態管理檢視的定義。For example, in future releases of SQL ServerSQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. 我們建議您不要在實際執行的程式碼中使用 SELECT * FROM dynamic_management_view_name 語法,因為傳回的資料行數可能會變更和破壞應用程式。We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

動態管理檢視和函數有兩種類型:There are two types of dynamic management views and functions:

  • 伺服器範圍的動態管理檢視和函數。Server-scoped dynamic management views and functions. 這些都需要伺服器的 VIEW SERVER STATE 權限。These require VIEW SERVER STATE permission on the server.

  • 資料庫範圍的動態管理檢視和函數。Database-scoped dynamic management views and functions. 這些都需要資料庫的 VIEW DATABASE STATE 權限。These require VIEW DATABASE STATE permission on the database.

查詢動態管理檢視Querying Dynamic Management Views

動態管理檢視可使用兩部分、三部分或四部分名稱在 Transact-SQLTransact-SQL 陳述式中參考。Dynamic management views can be referenced in Transact-SQLTransact-SQL statements by using two-part, three-part, or four-part names. 另一方面,動態管理函數可使用兩部分或三部分名稱在 Transact-SQLTransact-SQL 陳述式中參考。Dynamic management functions on the other hand can be referenced in Transact-SQLTransact-SQL statements by using either two-part or three-part names. 動態管理檢視和函數不能使用一部分名稱在 Transact-SQLTransact-SQL 陳述式中參考。Dynamic management views and functions cannot be referenced in Transact-SQLTransact-SQL statements by using one-part names.

所有動態管理檢視和函數都存在於 sys 結構描述中,並遵照這個命名慣例 dm_*。All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. 當您使用動態管理檢視或函數時,必須使用 sys 結構描述來加上檢視或函數名稱的前置詞。When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. 例如,若要查詢 dm_os_wait_stats 動態管理檢視,請執行下列查詢:For example, to query the dm_os_wait_stats dynamic management view, run the following query:

SELECT wait_type, wait_time_ms  
FROM sys.dm_os_wait_stats;  

必要權限Required Permissions

若要查詢動態管理檢視或函數,需要物件的 SELECT 權限和 VIEW SERVER STATE 或 VIEW DATABASE STATE 權限。To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. 這可讓您選擇性地限制使用者的存取或登入到動態管理檢視和函數。This lets you selectively restrict access of a user or login to dynamic management views and functions. 若要這麼做,先在 master 中建立使用者,然後在您不要他們存取的動態管理檢視或函數上拒絕使用者 SELECT 權限。To do this, first create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. 在此之後,不論使用者的資料庫內容如何,使用者將無法從這些動態管理檢視或函數中選取。After this, the user cannot select from these dynamic management views or functions, regardless of database context of the user.

注意

因為以 DENY 的優先順序為準,如果已授與使用者 VIEW SERVER STATE 權限,但拒絕 VIEW DATABASE STATE 權限,則使用者可以查看伺服器層級資訊,但不能查看資料庫層級資訊。Because DENY takes precedence, if a user has been granted VIEW SERVER STATE permissions but denied VIEW DATABASE STATE permission, the user can see server-level information, but not database-level information.

本節內容In This Section

動態管理檢視和函數已組織成下列類別目錄。Dynamic management views and functions have been organized into the following categories.

Alwayson 可用性群組動態管理檢視和 Funtions (TRANSACT-SQL)Always On Availability Groups Dynamic Management Views and Funtions (Transact-SQL) 記憶體最佳化的資料表動態管理檢視(Transact SQL)Memory-Optimized Table Dynamic Management Views (Transact-SQL)
異動資料擷取相關的動態管理檢視 (Transact-SQL)Change Data Capture Related Dynamic Management Views (Transact-SQL) 物件與相關的動態管理檢視和函數(Transact SQL)Object Related Dynamic Management Views and Functions (Transact-SQL)
變更追蹤相關的動態管理檢視Change Tracking Related Dynamic Management Views 查詢通知相關的動態管理檢視(Transact SQL)Query Notifications Related Dynamic Management Views (Transact-SQL)
Common Language Runtime 相關的動態管理檢視(Transact SQL)Common Language Runtime Related Dynamic Management Views (Transact-SQL) 複寫相關的動態管理檢視(Transact SQL)Replication Related Dynamic Management Views (Transact-SQL)
資料庫鏡像相關的動態管理檢視(Transact SQL)Database Mirroring Related Dynamic Management Views (Transact-SQL) 資源管理員相關的動態管理檢視(Transact SQL)Resource Governor Related Dynamic Management Views (Transact-SQL)
與資料庫相關動態管理檢視(Transact SQL)Database Related Dynamic Management Views (Transact-SQL) 安全性相關的動態管理檢視和函數 (Transact-SQL)Security-Related Dynamic Management Views and Functions (Transact-SQL)
執行相關動態管理檢視和函數(Transact SQL)Execution Related Dynamic Management Views and Functions (Transact-SQL) 伺服器相關的動態管理檢視和函式 (Transact-SQL)Server-Related Dynamic Management Views and Functions (Transact-SQL)
擴充的事件動態管理檢視Extended Events Dynamic Management Views Service Broker 相關的動態管理檢視 (Transact-SQL)Service Broker Related Dynamic Management Views (Transact-SQL)
Filestream 和 FileTable 動態管理檢視(Transact SQL)Filestream and FileTable Dynamic Management Views (Transact-SQL) 空間資料相關動態管理檢視和函數(Transact SQL)Spatial Data Related Dynamic Management Views and Functions (Transact-SQL)
全文檢索搜尋和語意搜尋動態管理檢視與函數(Transact SQL)Full-Text Search and Semantic Search Dynamic Management Views and Functions (Transact-SQL) SQL 資料倉儲和平行處理資料倉儲動態管理檢視(Transact SQL)SQL Data Warehouse and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)
地理複寫動態管理檢視和函數(Azure SQL Database)Geo-Replication Dynamic Management Views and Functions (Azure SQL Database) SQL Server 作業系統相關的動態管理檢視(Transact SQL)SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
索引相關的動態管理檢視和函數(Transact SQL)Index Related Dynamic Management Views and Functions (Transact-SQL) Stretch Database 動態管理檢視(Transact SQL)Stretch Database Dynamic Management Views (Transact-SQL)
我 O 相關動態管理檢視和函數(Transact SQL)I O Related Dynamic Management Views and Functions (Transact-SQL) 交易相關的動態管理檢視和函數 (Transact-SQL)Transaction Related Dynamic Management Views and Functions (Transact-SQL)

另請參閱See Also

GRANT 伺服器權限(Transact SQL) GRANT Server Permissions (Transact-SQL)
GRANT 資料庫權限 (Transact-SQL) GRANT Database Permissions (Transact-SQL)
系統檢視表(Transact SQL)System Views (Transact-SQL)