sys.dm_db_index_physical_stats (Transact-SQL)
適用於:SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體
傳回 SQL Server 中指定資料表或檢視表之數據和索引的大小和片段資訊。 如果是索引,則針對每個分割區中每個層級的 B 型樹狀目錄,各傳回一個資料列。 如果是堆積,則針對每個分割區中的 IN_ROW_DATA
配置單位,各傳回一個資料列。 對於大型物件 (LOB) 數據,會針對 LOB_DATA
每個數據分割的配置單位傳回一個數據列。 如果數據表中有數據列溢位數據,則會針對 ROW_OVERFLOW_DATA
每個分割區中的配置單位傳回一個數據列。
注意
SQL Server 文件通常會使用「B 型樹狀結構」一詞來指稱索引。 在資料列存放區索引中,SQL Server 會實作 B+ 樹狀結構。 這不適用於資料行存放區索引或記憶體內部資料存放區。 如需詳細資訊,請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。
sys.dm_db_index_physical_stats
不會傳回記憶體優化索引的相關信息。 如需記憶體優化索引使用的相關信息,請參閱 sys.dm_db_xtp_index_stats (Transact-SQL) 。
如果您在裝載可用性群組可讀取次要複本的伺服器實例上查詢sys.dm_db_index_physical_stats
,可能會遇到REDO
封鎖問題。 這是因為此動態管理檢視會IS
取得指定之用戶數據表或檢視的鎖定,而該檢視可封鎖該用戶數據表或檢視上鎖定之線程X
的要求REDO
。
語法
sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)
引數
database_id |NULL |0 |違約
資料庫的識別碼。 database_id為 smallint。 有效的輸入是資料庫、NULL、0 或 DEFAULT 的標識碼。 預設值是 0。 NULL、0 和 DEFAULT 在此內容中是相等的值。
指定 NULL 以傳回 SQL Server 實例中所有資料庫的資訊。 如果您為 database_id 指定 NULL,也必須針對 object_id、 index_id和 partition_number指定 NULL。
可以指定內建函數 DB_ID。 在不指定資料庫名稱的情況下使用 DB_ID
時,目前資料庫的相容性層級必須是 90 或更高層級。
object_id |NULL |0 |違約
索引開啟之數據表或檢視表的物件標識碼。 object_id為 int。
有效的輸入是數據表和檢視表、NULL、0 或 DEFAULT 的識別碼。 預設值是 0。 NULL、0 和 DEFAULT 在此內容中是相等的值。 自 SQL Server 2016 (13.x) 起,有效的輸入也包含 Service Broker 佇列名稱或佇列內部數據表名稱。 套用預設參數時(也就是所有物件、所有索引等),所有佇列的片段信息都會包含在結果集中。
指定 NULL 以傳回指定資料庫中所有資料表和檢視的資訊。 如果您為 object_id 指定 NULL,也必須為 index_id 和 partition_number指定 NULL。
index_id | 0 |NULL |-1 |違約
索引的標識碼。 index_id為 int。有效的輸入是索引的標識碼,如果object_id是堆積、NULL、-1 或 DEFAULT,則為 0。 預設值為 -1。 NULL、-1 和 DEFAULT 在此內容中是相等的值。
指定 NULL 以傳回基表或檢視表之所有索引的資訊。 如果您為 index_id 指定 NULL,也必須為 partition_number指定 NULL。
partition_number |NULL |0 |違約
物件中的分割區編號。 partition_number為 int。有效的輸入是索引或堆積、NULL、0 或 DEFAULT 的partion_number。 預設值是 0。 NULL、0 和 DEFAULT 在此內容中是相等的值。
指定 NULL 以傳回擁有物件之所有分割區的資訊。
partition_number是以 1 為基礎。 非分割索引或堆積partition_number設定為 1。
mode |NULL |違約
模式的名稱。 mode 指定用來取得統計數據的掃描層級。 mode 為 sysname。 有效的輸入為 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。 預設值 (NULL) 為 LIMITED。
傳回的資料表
資料行名稱 | 資料類型 | 描述 |
---|---|---|
database_id | smallint | 數據表或檢視表的資料庫標識碼。 在 Azure SQL 資料庫中,這些值在單一資料庫或彈性集區內是唯一的,但在邏輯伺服器內則不是唯一的。 |
object_id | int | 索引所開啟之數據表或檢視的物件標識碼。 |
index_id | int | 索引的索引標識碼。 0 = 堆積。 |
partition_number | int | 擁有物件內的1個分割區編號;數據表、檢視或索引。 1 = 非分割索引或堆積。 |
index_type_desc | nvarchar(60) | 索引類型的描述: - HEAP - 叢集索引 - NONCLUSTERED INDEX - PRIMARY XML INDEX - 擴充索引 - XML INDEX - 資料列存放區對應索引 (內部) - COLUMNSTORE DELETEBUFFER INDEX (內部) - COLUMNSTORE DELETEBITMAP INDEX (內部) |
hobt_id | bigint | 索引或分割區的堆積或 B 型樹狀目錄標識碼。 對於數據行存放區索引,這是追蹤數據分割內部數據行存放區數據的數據列集的標識符。 數據列集會儲存為數據堆積或 B 型樹狀結構。 它們與父數據行存放區索引具有相同的索引標識碼。 如需詳細資訊,請參閱 sys.internal_partitions (Transact-SQL) 。 |
alloc_unit_type_desc | nvarchar(60) | 設定單位類型的描述: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA 配置 LOB_DATA 單位包含儲存在 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)和 xml 類型的數據行中。 如需詳細資訊,請參閱 資料類型 (Transact-SQL)。配置 ROW_OVERFLOW_DATA 單位包含儲存在 varchar(n)、nvarchar(n)、varbinary(n)類型的數據行,以及sql_variant已推離數據列的數據。 |
index_depth | tinyint | 索引層級的數目。 1 = 堆積或 LOB_DATA ROW_OVERFLOW_DATA 配置單位。 |
index_level | tinyint | 索引的目前層級。 索引分葉層級、堆積和 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位的 0。非分葉索引層級大於 0。 index_level是索引根層級的最高層級。 只有在mode = DETAILED 時,才會處理索引的非分葉層級。 |
avg_fragmentation_in_percent | float | 索引的邏輯片段,或配置單位中堆積的範圍 IN_ROW_DATA 片段。此值會以百分比來測量,並考慮多個檔案。 如需邏輯和範圍片段的定義,請參閱。 0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 配置單位。當模式 = SAMPLED 時堆積的 NULL。 |
fragment_count | bigint | 配置單位分 IN_ROW_DATA 葉層級中的片段數目。 如需片段的詳細資訊,請參閱。索引和 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位的非分葉層級 NULL。當模式 = SAMPLED 時堆積的 NULL。 |
avg_fragment_size_in_pages | float | 配置單位分 IN_ROW_DATA 葉層級中一個片段的平均頁數。索引和 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位的非分葉層級 NULL。當模式 = SAMPLED 時堆積的 NULL。 |
page_count | bigint | 索引或數據頁的總數。 針對索引,配置單位中 B 型樹狀結構目前層級的 IN_ROW_DATA 索引頁總數。針對堆積,配置單位中的數據 IN_ROW_DATA 頁總數。針對 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,配置單位中的總頁數。 |
avg_page_space_used_in_percent | float | 所有頁面使用之可用數據儲存空間的平均百分比。 針對索引,平均值會套用至配置單位中 IN_ROW_DATA B 型樹狀結構的目前層級。針對堆積,配置單位中所有數據頁的 IN_ROW_DATA 平均值。若為 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為配置單位中所有頁面的平均值。當模式 = LIMITED 時為 NULL。 |
record_count | bigint | 記錄總數。 針對索引,記錄總數會套用至配置單位中 IN_ROW_DATA B 型樹狀結構的目前層級。針對堆積,配置單位中的 IN_ROW_DATA 記錄總數。注意: 對於堆積,從此函式傳回的記錄數目可能不符合針對堆積執行 SELECT COUNT(*) 所傳回的數據列數目。 這是因為一個資料列可能包含數筆記錄。 例如,在某些更新情況下,單一的堆積資料列可能有一筆轉送記錄以及一筆當做更新作業結果的轉送記錄。 此外,大部分的大型 LOB 數據列會分割成記憶體中的 LOB_DATA 多個記錄。針對 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,完整配置單位中的記錄總數。當模式 = LIMITED 時為 NULL。 |
ghost_record_count | bigint | 可供配置單位中的準刪除清除工作移除的准刪除記錄數目。 0 表示配置單位中 IN_ROW_DATA 索引的非分葉層級。當模式 = LIMITED 時為 NULL。 |
version_ghost_record_count | bigint | 配置單位中未完成快照隔離交易所保留的准刪除記錄數目。 0 表示配置單位中 IN_ROW_DATA 索引的非分葉層級。當模式 = LIMITED 時為 NULL。 |
min_record_size_in_bytes | int | 以位元組為單位的最小記錄大小。 針對索引,最小記錄大小會套用至配置單位中 IN_ROW_DATA B 型樹狀結構的目前層級。針對堆積,配置單位中的 IN_ROW_DATA 最小記錄大小。若為 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的最小記錄大小。當模式 = LIMITED 時為 NULL。 |
max_record_size_in_bytes | int | 以位元組為單位的記錄大小上限。 針對索引,記錄大小上限會套用至配置單位中 IN_ROW_DATA B 型樹狀結構的目前層級。針對堆積,配置單位中的 IN_ROW_DATA 記錄大小上限。針對 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,完整配置單位中的記錄大小上限。當模式 = LIMITED 時為 NULL。 |
avg_record_size_in_bytes | float | 以位元組為單位的平均記錄大小。 針對索引,平均記錄大小會套用至配置單位中 IN_ROW_DATA B 型樹狀結構的目前層級。針對堆積,配置單位中的 IN_ROW_DATA 平均記錄大小。若為 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的平均記錄大小。當模式 = LIMITED 時為 NULL。 |
forwarded_record_count | bigint | 堆積中具有指向另一個數據位置之正向指標的記錄數目。 (當沒有足夠的空間將新數據列儲存在原始位置時,就會在更新期間發生此狀態。 堆積配置單位以外的 IN_ROW_DATA 任何配置單位的NULL。當mode = LIMITED 時堆積的 NULL。 |
compressed_page_count | bigint | 壓縮的頁面數目。 對於堆積,新配置的頁面不會壓縮PAGE。 堆積會在兩個特殊條件下壓縮:大容量導入數據或重建堆積時。 導致頁面配置的典型 DML 作業不會壓縮 PAGE。 當值大於您想要的臨界值時 compressed_page_count ,重建堆積。對於具有叢集索引的數據表,值 compressed_page_count 表示PAGE壓縮的有效性。 |
columnstore_delete_buffer_state | tinyint | 0 = NOT_APPLICABLE 1 = OPEN 2 = 清空 3 = FLUSHING 4 = 淘汰 5 = READY 適用於:SQL Server 2016 (13.x) 和更新版本、Azure SQL 資料庫 和 Azure SQL 受控執行個體 |
columnstore_delete_buffer_state_desc | nvarchar(60) | 無效 - 父索引不是數據行存放區索引。 OPEN - 刪除程式和掃描器會使用此專案。 清空 - 刪除程式正在清空,但掃描器仍然使用它。 FLUSHING - 緩衝區已關閉,且緩衝區中的數據列正在寫入刪除位圖。 淘汰 - 關閉刪除緩衝區中的數據列已寫入刪除位圖,但緩衝區尚未被截斷,因為掃描器仍在使用它。 新的掃描器不需要使用淘汰的緩衝區,因為開啟的緩衝區就足夠了。 READY - 此刪除緩衝區已可供使用。 適用於:SQL Server 2016 (13.x) 和更新版本、Azure SQL 資料庫 和 Azure SQL 受控執行個體 |
version_record_count | bigint | 這是在此索引中維護的數據列版本記錄計數。 這些數據列版本是由 加速資料庫復原 功能所維護。 適用於:SQL Server 2019 (15.x) 和更新版本,以及 Azure SQL 資料庫 |
inrow_version_record_count | bigint | 數據列中保留的 ADR 版本記錄計數,以便快速擷取。 適用於:SQL Server 2019 (15.x) 和更新版本,以及 Azure SQL 資料庫 |
inrow_diff_version_record_count | bigint | 以基底版本差異形式保留的 ADR 版本記錄計數。 適用於:SQL Server 2019 (15.x) 和更新版本,以及 Azure SQL 資料庫 |
total_inrow_version_payload_size_in_bytes | bigint | 此索引之數據列內版本記錄的位元元組大小總計。 適用於:SQL Server 2019 (15.x) 和更新版本,以及 Azure SQL 資料庫 |
offrow_regular_version_record_count | bigint | 保留於原始數據列外部的版本記錄計數。 適用於:SQL Server 2019 (15.x) 和更新版本,以及 Azure SQL 資料庫 |
offrow_long_term_version_record_count | bigint | 考慮長期的版本記錄計數。 適用於:SQL Server 2019 (15.x) 和更新版本,以及 Azure SQL 資料庫 |
注意
SQL Server 文件通常會使用「B 型樹狀結構」一詞來指稱索引。 在資料列存放區索引中,SQL Server 會實作 B+ 樹狀結構。 這不適用於資料行存放區索引或記憶體內部資料存放區。 如需詳細資訊,請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。
備註
sys.dm_db_index_physical_stats
動態管理函數會取代 DBCC SHOWCONTIG
陳述式。
掃描模式
執行函式的模式會決定執行掃描的層級,以取得函式所使用的統計數據。 模式 指定為 LIMITED、SAMPLED 或 DETAILED。 函式會周游組成數據表或索引之指定分割區之配置單位的頁面鏈結。 sys.dm_db_index_physical_stats
不論其執行模式為何,只需要意圖共用 (IS) 數據表鎖定。
LIMITED 模式是最快的模式,會掃描最少的頁面數目。 針對索引,只會掃描 B 型樹狀結構的父層級頁面(也就是分葉層級上方的頁面)。 針對堆積,會檢查相關聯的 PFS 和 IAM 頁面,並以 LIMITED 模式掃描堆積的數據頁。
使用 LIMITED 模式時,是 NULL,compressed_page_count
因為 資料庫引擎 只會掃描 B 型樹狀結構的非分葉頁面,以及堆積的 IAM 和 PFS 頁面。 使用 SAMPLED 模式取得 的 compressed_page_count
估計值,並使用 DETAILED 模式取得 的實際值 compressed_page_count
。 SAMPLED 模式會根據索引或堆積中所有頁面的 1% 樣本傳回統計數據。 SAMPLED 模式的結果應視為近似值。 如果索引或堆積少於 10,000 頁,則會使用 DETAILED 模式,而不是 SAMPLED。
DETAILED 模式會掃描所有頁面,並傳回所有統計數據。
模式會逐漸變慢,從 LIMITED 到 DETAILED,因為每個模式都會執行更多工作。 若要快速量測數據表或索引的大小或片段層級,請使用 LIMITED 模式。 這是最快的,而且不會針對索引配置單位中的每個 IN_ROW_DATA
非分葉層級傳回一個數據列。
使用系統函式來指定參數值
您可以使用 Transact-SQL 函式DB_ID和OBJECT_ID來指定database_id和object_id參數的值。 不過,傳遞這些函式無效的值可能會導致非預期的結果。 例如,如果找不到資料庫或物件名稱,因為它們不存在或拼字不正確,則這兩個函式都會傳回 NULL。 函 sys.dm_db_index_physical_stats
式會將 NULL 解譯為指定所有資料庫或所有物件的通配符值。
此外,會在OBJECT_ID
呼叫函式之前sys.dm_db_index_physical_stats
處理函式,因此會在目前資料庫的內容中進行評估,而不是database_id中指定的資料庫。 此行為可能會導致函 OBJECT_ID
式傳回 NULL 值;或者,如果物件名稱同時存在於目前資料庫內容和指定的資料庫中,可能會傳回錯誤訊息。 下列範例示範這些非預期的結果。
USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO
最佳做法
請務必確定當您使用 DB_ID
或 OBJECT_ID
時,會傳回有效的標識符。 例如,當您使用 OBJECT_ID
時,請指定三部分的名稱,例如 OBJECT_ID(N'AdventureWorks2022.Person.Address')
,或先測試函式所傳回的值,然後再在函式中使用 sys.dm_db_index_physical_stats
它們。 下列範例 A 和 B 示範指定資料庫和物件識別碼的安全方式。
偵測片段
片段會透過針對資料表進行的數據修改程式(INSERT、UPDATE 和 DELETE 語句)進行,因此會針對資料表所定義的索引進行。 由於這些修改通常不會平均分散在數據表和索引的數據列之間,因此每個頁面的完整度可能會隨著時間而有所不同。 對於掃描數據表部分或所有索引的查詢,這種片段可能會導致其他頁面讀取。 這會防礙資料的平行掃描。
索引或堆積的片段層級會顯示在數據行中 avg_fragmentation_in_percent
。 對於堆積,值代表堆積的範圍片段。 對於索引,值代表索引的邏輯片段。 不同於 DBCC SHOWCONTIG
,這兩種情況下的片段計算演算法都會考慮跨越多個檔案的記憶體,因此正確無誤。
邏輯片段
這是索引分葉頁面中順序不一的分頁百分比。 失序頁面是指下一個實體頁面配置給索引的頁面,而不是目前分葉頁中「下一頁」指標所指向的頁面。
範圍片段
這是堆積分葉頁面中順序錯亂範圍的百分比。 順序錯亂的範圍是包含堆積目前頁面的範圍不是包含上一頁範圍之後的下一個範圍。
的值 avg_fragmentation_in_percent
應該盡可能接近零,以達到最大效能。 不過,0% 到 10% 的值可能可以接受。 減少片段的所有方法,例如重建、重新組織或重新建立,都可以用來減少這些值。 如需如何分析索引中片段程度的詳細資訊,請參閱 重新組織及重建索引。
減少索引中的片段
當索引以片段影響查詢效能的方式分散時,有三個選項可減少片段:
卸除並重新建立叢集索引。
重新建立叢集索引會重新發佈數據,併產生完整的數據頁面。 您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定完整度層級。 此方法的缺點是索引在卸除和重新建立週期期間脫機,且作業不可部分完成。 如果中斷了索引建立,就不會重建索引。 如需詳細資訊,請參閱 CREATE FULLTEXT CATALOG (TRANSACT-SQL)。
使用 ALTER INDEX REORGANIZE,取代
DBCC INDEXDEFRAG
為 ,以邏輯順序重新排序索引的分葉層級頁面。 因為這是在線作業,因此當語句執行時,可以使用索引。 作業也可以中斷,而不會遺失已完成的工作。 此方法的缺點是,它不會像重新組織數據做為索引重建作業一樣好,而且不會更新統計數據。使用 ALTER INDEX REBUILD,取代 的
DBCC DBREINDEX
,在在線或離線重建索引。 如需詳細資訊,請參閱 ALTER INDEX (Transact-SQL)。
單靠片段並不是重新組織或重建索引的足夠理由。 片段的主要效果是,在索引掃描期間,它會減緩頁面預先讀取輸送量的速度。 這會導致回應時間變慢。 如果片段數據表或索引上的查詢工作負載未涉及掃描,因為工作負載主要是單一查閱,因此移除片段可能會沒有作用。
注意
如果索引在壓縮作業期間部分或完全移動,則執行 DBCC SHRINKFILE
或 DBCC SHRINKDATABASE
可能會造成片段。 因此,如果必須執行壓縮作業,您應該先執行壓縮作業,再移除片段。
減少堆積中的片段
若要減少堆積的範圍片段,請在數據表上建立叢集索引,然後卸除索引。 這會在建立叢集索引時重新發佈數據。 這也使它盡可能優化,考慮資料庫中可用空間的分佈。 然後卸除叢集索引以重新建立堆積時,數據不會移動,而且會保持最佳位置。 如需如何執行這些作業的詳細資訊,請參閱 CREATE INDEX 和 DROP INDEX。
警告
在數據表上建立和卸除叢集索引,重建該數據表上所有非叢集索引兩次。
壓縮大型對象數據
根據預設,ALTER INDEX REORGANIZE 語句會壓縮包含大型物件 (LOB) 數據的頁面。 由於 LOB 頁面不會在空白時解除分配,因此如果已刪除許多 LOB 資料或卸除 LOB 資料行,壓縮此數據可以改善磁碟空間使用量。
重新組織指定的叢集索引會壓縮叢集索引中包含的所有LOB數據行。 重新組織非叢集索引會壓縮索引中非索引鍵(包含)數據行的所有LOB數據行。 在語句中指定 ALL 時,所有與指定資料表或檢視相關聯的索引都會重新組織。 此外,會壓縮與叢集索引、基礎表或包含數據行的非叢集索引相關聯的所有LOB資料行。
評估磁碟空間使用量
數據 avg_page_space_used_in_percent
行表示頁面完整度。 若要達到最佳磁碟空間使用,這個值應該接近 100%, 索引沒有太多隨機插入。 不過,索引具有許多隨機插入,而且頁面非常完整,頁面分割數目會增加。 這會造成更多的片段。 因此,為了減少頁面分割,值應該小於 100%。 使用指定的 FILLFACTOR 選項重建索引,可變更頁面完整度以符合索引上的查詢模式。 如需填滿因數的詳細資訊,請參閱 指定索引的填滿因數。 此外,ALTER INDEX REORGANIZE 會嘗試將頁面填滿到上次指定的 FILLFACTOR 來壓縮索引。 這會增加avg_space_used_in_percent中的值。 ALTER INDEX REORGANIZE 無法減少頁面完整度。 您必須改為執行索引重建。
評估索引片段
片段是由相同檔案中實體連續分葉頁面所組成,用於配置單位。 索引至少有一個片段。 索引可以擁有的最大片段等於索引分葉層級中的頁數。 較大的片段表示讀取相同頁數需要較少的磁碟 I/O。 因此,值越大 avg_fragment_size_in_pages
,範圍掃描效能就越好。 avg_fragment_size_in_pages
和 avg_fragmentation_in_percent
值會彼此成反比。 因此,重建或重新組織索引應該減少片段數量,並增加片段大小。
限制事項
不會傳回叢集數據行存放區索引的數據。
權限
需要下列權限:
資料庫內所指定物件的CONTROL許可權。
VIEW DATABASE STATE 或 VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) 許可權,可使用物件通配符 @object_id=NULL 傳回指定資料庫內所有對象的相關信息。
VIEW SERVER STATE 或 VIEW SERVER PERFORMANCE STATE (SQL Server 2022) 許可權可傳回所有資料庫的相關信息,方法是使用資料庫通配符 @database_id = NULL。
授與 VIEW DATABASE STATE 允許傳回資料庫中的所有物件,不論特定物件上有任何 CONTROL 許可權遭到拒絕。
拒絕 VIEW DATABASE STATE 不允許傳回資料庫中的所有物件,而不論授與特定物件的任何 CONTROL 許可權為何。 此外,指定資料庫通配符 @database_id=NULL 時,會省略資料庫。
如需詳細資訊,請參閱動態管理檢視和函式(Transact-SQL)。
範例
A. 傳回指定數據表的相關信息
下列範例會傳回數據表所有索引和數據分割的大小 Person.Address
和片段統計數據。 掃描模式會設定為 'LIMITED'
以獲得最佳效能,並限制傳回的統計數據。 執行此查詢至少需要數據表的 Person.Address
CONTROL許可權。
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO
B. 傳回堆積的相關信息
下列範例會傳回 AdventureWorks2022 資料庫中堆積 dbo.DatabaseLog
的所有統計數據。 因為數據表包含 LOB 數據,除了針對儲存堆積數據頁之 所傳IN_ROW_ALLOCATION_UNIT
回的數據列之外,配置單位也會傳回LOB_DATA
數據列。 執行此查詢至少需要數據表的 dbo.DatabaseLog
CONTROL許可權。
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO
C. 傳回所有資料庫的資訊
下列範例會指定所有參數的通配符 NULL
,以傳回SQL Server 實例內所有數據表和索引的所有統計數據。 執行此查詢需要 VIEW SERVER STATE 許可權。
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO
D. 在 sys.dm_db_index_physical_stats
腳本中使用 來重建或重新組織索引
下列範例會自動重新組織或重建資料庫中平均分散超過 10% 的所有分割區。 執行此查詢需要 VIEW DATABASE STATE 許可權。 這個範例會 DB_ID
指定為第一個參數,而不指定資料庫名稱。 如果目前的資料庫相容性層級為 80 或更低,則會產生錯誤。 若要解決錯誤,請以有效的資料庫名稱取代 DB_ID()
。 如需資料庫相容性層級的詳細資訊,請參閱 ALTER DATABASE 相容性層級(Transact-SQL)。
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid,
@indexid,
@partitionnum,
@frag;
IF @@FETCH_STATUS < 0
BREAK;
SELECT @objectname = QUOTENAME(o.name),
@schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid
AND index_id = @indexid;
SELECT @partitioncount = count(*)
FROM sys.partitions
WHERE object_id = @objectid
AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
E. 用來 sys.dm_db_index_physical_stats
顯示頁面壓縮的頁面數目
下列範例示範如何顯示和比較頁面總數與已壓縮數據列和頁面的頁面。 此資訊可用來判斷壓縮為索引或數據表提供的好處。
SELECT o.name,
ips.partition_number,
ips.index_type_desc,
ips.record_count,
ips.avg_record_size_in_bytes,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.page_count,
ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
ON o.object_id = ips.object_id
ORDER BY record_count DESC;
F. 在 SAMPLED 模式中使用sys.dm_db_index_physical_stats
下列範例示範 SAMPLED 模式如何傳回與 DETAILED 模式結果不同的近似值。
CREATE TABLE t3 (
col1 INT PRIMARY KEY,
col2 VARCHAR(500)
)
WITH (DATA_COMPRESSION = PAGE);
GO
BEGIN TRANSACTION
DECLARE @idx INT = 0;
WHILE @idx < 1000000
BEGIN
INSERT INTO t3 (col1, col2)
VALUES (
@idx,
REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
)
SET @idx = @idx + 1
END
COMMIT;
GO
SELECT page_count,
compressed_page_count,
forwarded_record_count,
*
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');
SELECT page_count,
compressed_page_count,
forwarded_record_count,
*
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');
G. 查詢索引片段的 Service Broker 佇列
適用於:SQL Server 2016 (13.x) 和更新版本。
下列範例示範如何查詢伺服器訊息代理程式佇列以進行片段化。
--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);
--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);