sys.dm_db_partition_stats (Transact-SQL)sys.dm_db_partition_stats (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

針對目前資料庫中的每個資料分割,各傳回其頁面和資料列計數資訊。Returns page and row-count information for every partition in the current database.

注意

若要呼叫這個屬性從 Azure SQL 資料倉儲Azure SQL Data Warehouse或是 平行處理資料倉儲Parallel Data Warehouse,使用名稱sys.dm_pdw_nodes_db_partition_statsTo call this from Azure SQL 資料倉儲Azure SQL Data Warehouse or 平行處理資料倉儲Parallel Data Warehouse, use the name sys.dm_pdw_nodes_db_partition_stats.

資料行名稱Column name 資料類型Data type 描述Description
partition_idpartition_id bigintbigint 資料分割的識別碼。ID of the partition. 在資料庫中,這是唯一的。This is unique within a database. 這是相同的值sys.partitionssys.partitions目錄檢視This is the same value as the partition_id in the sys.partitions catalog view
object_idobject_id intint 資料分割所屬資料表或索引檢視的物件識別碼。Object ID of the table or indexed view that the partition is part of.
index_idindex_id intint 資料分割所屬之堆積或索引的識別碼。ID of the heap or index the partition is part of.

0 = 堆積0 = Heap

1 = 叢集索引。1 = Clustered index.

> 1 = 非叢集索引> 1 = Nonclustered index
partition_numberpartition_number intint 在索引或堆積內,以 1 為基底的資料分割編號。1-based partition number within the index or heap.
in_row_data_page_countin_row_data_page_count bigintbigint 這個資料分割中用來儲存同資料列資料的頁數。Number of pages in use for storing in-row data in this partition. 如果資料分割屬於堆積,這個值是堆積中的資料頁數。If the partition is part of a heap, the value is the number of data pages in the heap. 如果資料分割屬於索引,這個值是分葉層級中的頁數。If the partition is part of an index, the value is the number of pages in the leaf level. (計數不包含 B 型樹狀目錄中的非分葉頁數。)以上兩種情況均不包含 IAM (索引配置對應) 頁數。(Nonleaf pages in the B-tree are not included in the count.) IAM (Index Allocation Map) pages are not included in either case. xVelocity 記憶體最佳化的資料行存放區索引之 Always 0。Always 0 for an xVelocity memory optimized columnstore index.
in_row_used_page_countin_row_used_page_count bigintbigint 這個資料分割中用來儲存和管理同資料列資料的總頁數。Total number of pages in use to store and manage the in-row data in this partition. 這個計數包括非分葉 B 型樹狀目錄頁數、 IAM 頁數,以及包含在所有網頁in_row_data_page_count資料行。This count includes nonleaf B-tree pages, IAM pages, and all pages included in the in_row_data_page_count column. 永遠是 0,表示資料行存放區索引。Always 0 for a columnstore index.
in_row_reserved_page_countin_row_reserved_page_count bigintbigint 這個資料分割中為儲存和管理同資料列資料所保留的總頁數,不管這些頁面是否正在使用中。Total number of pages reserved for storing and managing in-row data in this partition, regardless of whether the pages are in use or not. 永遠是 0,表示資料行存放區索引。Always 0 for a columnstore index.
lob_used_page_countlob_used_page_count bigintbigint 儲存和管理資料列外的使用中的頁數文字ntext映像varchar (max)nvarchar(max)varbinary (max),以及xml分割區內的資料行。Number of pages in use for storing and managing out-of-row text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml columns within the partition. IAM 頁數包括在內。IAM pages are included.

資料分割中用來儲存和管理資料行存放區索引的 LOB 總數。Total number of LOBs used to store and manage columnstore index in the partition.
lob_reserved_page_countlob_reserved_page_count bigintbigint 儲存和管理資料列外保留的分頁總數文字ntext映像varchar (max)nvarchar (max)varbinary (max),以及xml資料分割,不論頁面是否正在使用中或未內的資料行。Total number of pages reserved for storing and managing out-of-row text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml columns within the partition, regardless of whether the pages are in use or not. IAM 頁數包括在內。IAM pages are included.

保留在資料分割中儲存和管理資料行存放區索引的 LOB 總數。Total number of LOBs reserved for storing and managing a columnstore index in the partition.
row_overflow_used_page_countrow_overflow_used_page_count bigintbigint 用來儲存和管理資料列溢位中的頁數varcharnvarcharvarbinary,以及sql_variant資料行資料分割。Number of pages in use for storing and managing row-overflow varchar, nvarchar, varbinary, and sql_variant columns within the partition. IAM 頁數包括在內。IAM pages are included.

永遠是 0,表示資料行存放區索引。Always 0 for a columnstore index.
row_overflow_reserved_page_countrow_overflow_reserved_page_count bigintbigint 儲存和管理資料列溢位所保留的分頁總數varcharnvarcharvarbinary,以及sql_variant資料分割,不論頁面是否正在使用中或未內的資料行。Total number of pages reserved for storing and managing row-overflow varchar, nvarchar, varbinary, and sql_variant columns within the partition, regardless of whether the pages are in use or not. IAM 頁數包括在內。IAM pages are included.

永遠是 0,表示資料行存放區索引。Always 0 for a columnstore index.
used_page_countused_page_count bigintbigint 資料分割的總使用頁數。Total number of pages used for the partition. 計算方式為in_row_used_page_count + lob_used_page_count + row_overflow_used_page_countComputed as in_row_used_page_count + lob_used_page_count + row_overflow_used_page_count.
reserved_page_countreserved_page_count bigintbigint 資料分割的總保留頁數。Total number of pages reserved for the partition. 計算方式為in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_countComputed as in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_count.
row_countrow_count bigintbigint 此資料分割中大約的資料列數目。The approximate number of rows in the partition.
pdw_node_idpdw_node_id intint 適用於: Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseApplies to: Azure SQL 資料倉儲Azure SQL Data Warehouse, 平行處理資料倉儲Parallel Data Warehouse

這個分佈是在節點的識別碼。The identifier for the node that this distribution is on.
distribution_iddistribution_id intint 適用於: Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseApplies to: Azure SQL 資料倉儲Azure SQL Data Warehouse, 平行處理資料倉儲Parallel Data Warehouse

與發佈相關聯的唯一數值識別碼。The unique numeric id associated with the distribution.

備註Remarks

sys.dm_db_partition_stats顯示用來儲存和管理同資料列資料、 LOB 資料和資料列溢位資料之資料庫中的所有資料分割的空間的相關資訊。sys.dm_db_partition_stats displays information about the space used to store and manage in-row data LOB data, and row-overflow data for all partitions in a database. 每個資料分割顯示一個資料列。One row is displayed per partition.

在各個不同的系統資料表中,輸出所依據的計數快取於記憶體中或儲存在磁碟中。The counts on which the output is based are cached in memory or stored on disk in various system tables.

同資料列資料、LOB 資料和資料列溢位資料代表組成資料分割的三個配置單位。In-row data, LOB data, and row-overflow data represent the three allocation units that make up a partition. Sys.allocation_units目錄檢視可以查詢資料庫中每個配置單位的相關中繼資料。The sys.allocation_units catalog view can be queried for metadata about each allocation unit in the database.

如果堆積或索引不分割,則是由一個資料分割組成 (資料分割數目 = 1);因此,只會針對該堆積或索引傳回一個資料列。If a heap or index is not partitioned, it is made up of one partition (with partition number = 1); therefore, only one row is returned for that heap or index. Sys.partitions可以查詢目錄檢視的所有資料表和資料庫中的索引的每個分割區的相關中繼資料。The sys.partitions catalog view can be queried for metadata about each partition of all the tables and indexes in a database.

個別資料表或索引的總計數可以藉由加入所有相關資料分割的計數來取得。The total count for an individual table or an index can be obtained by adding the counts for all relevant partitions.

PermissionsPermissions

需要 VIEW DATABASE STATE 權限來查詢sys.dm_db_partition_stats動態管理檢視。Requires VIEW DATABASE STATE permission to query the sys.dm_db_partition_stats dynamic management view. 如需動態管理檢視權限的相關詳細資訊,請參閱動態管理檢視和函式(TRANSACT-SQL)For more information about permissions on dynamic management views, see Dynamic Management Views and Functions (Transact-SQL).

範例Examples

A.A. 傳回資料庫中所有索引和堆積之所有資料分割的所有計數Returning all counts for all partitions of all indexes and heaps in a database

下列範例會顯示 AdventureWorks2012AdventureWorks2012 資料庫中所有索引和堆積之所有資料分割的所有計數。The following example shows all counts for all partitions of all indexes and heaps in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
SELECT * FROM sys.dm_db_partition_stats;  
GO  

B.B. 傳回資料表與其索引之所有資料分割的所有計數Returning all counts for all partitions of a table and its indexes

下列範例會顯示 HumanResources.Employee 資料表與其索引之所有資料分割的所有計數。The following example shows all counts for all partitions of the HumanResources.Employee table and its indexes.

USE AdventureWorks2012;  
GO  
SELECT * FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('HumanResources.Employee');  
GO  

C.C. 傳回堆積或叢集索引的總使用頁數和資料列總數Returning total used pages and total number of rows for a heap or clustered index

下列範例會傳回 HumanResources.Employee 資料表之堆積或叢集索引的總使用頁數和資料列總數。The following example returns total used pages and total number of rows for the heap or clustered index of the HumanResources.Employee table. 因為依預設不會分割 Employee 資料表,請注意總和只包括一個資料分割。Because the Employee table is not partitioned by default, note the sum includes only one partition.

USE AdventureWorks2012;  
GO  
SELECT SUM(used_page_count) AS total_number_of_used_pages,   
    SUM (row_count) AS total_number_of_rows   
FROM sys.dm_db_partition_stats  
WHERE object_id=OBJECT_ID('HumanResources.Employee')    AND (index_id=0 or index_id=1);  
GO  

另請參閱See Also

動態管理檢視與函數 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
與資料庫相關動態管理檢視(Transact SQL)Database Related Dynamic Management Views (Transact-SQL)