sys.dm_db_stats_properties (Transact-SQL)sys.dm_db_stats_properties (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

針對目前 SQL ServerSQL Server 資料庫中的指定資料庫物件 (資料表或索引檢視表) 傳回統計資料的屬性。Returns properties of statistics for the specified database object (table or indexed view) in the current SQL ServerSQL Server database. 對於資料分割的資料表,請參閱類似sys.dm_db_incremental_stats_propertiesFor partitioned tables, see the similar sys.dm_db_incremental_stats_properties.

語法Syntax

sys.dm_db_stats_properties (object_id, stats_id)  

引數Arguments

object_idobject_id
這是目前資料庫中,要求其中一個統計資料屬性之物件的識別碼。Is the ID of the object in the current database for which properties of one of its statistics is requested. object_idintobject_id is int.

stats_idstats_id
這是指定 object_id之統計資料的識別碼。Is the ID of statistics for the specified object_id. 您可以從 sys.stats 動態管理檢視取得統計資料識別碼。The statistics ID can be obtained from the sys.stats dynamic management view. stats_idintstats_id is int.

傳回的資料表Table Returned

資料行名稱Column name 資料類型Data type 描述Description
object_idobject_id intint 要傳回統計資料物件屬性之物件 (資料表或索引檢視表) 的識別碼。ID of the object (table or indexed view) for which to return the properties of the statistics object.
stats_idstats_id intint 統計資料物件的識別碼。ID of the statistics object. 這在資料表或索引檢視表中是唯一的。Is unique within the table or indexed view. 如需詳細資訊,請參閱 sys.stats (Transact-SQL)For more information, see sys.stats (Transact-SQL).
last_updatedlast_updated datetime2datetime2 上次更新統計資料物件的日期和時間。Date and time the statistics object was last updated. 如需詳細資訊,請參閱此頁的備註一節。For more information, see the Remarks section in this page.
rowsrows bigintbigint 上一次更新統計資料時位於資料表或索引檢視表中的資料列總數。Total number of rows in the table or indexed view when statistics were last updated. 如果篩選了統計資料或是統計資料對應至篩選過的索引,此資料列數可能會少於資料表中的資料列數。If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table.
rows_sampledrows_sampled bigintbigint 針對統計資料計算進行取樣的資料列總數。Total number of rows sampled for statistics calculations.
步驟steps intint 長條圖中的步驟數。Number of steps in the histogram. 如需詳細資訊,請參閱 DBCC SHOW_STATISTICS (Transact-SQL)並未包含檢視。For more information, see DBCC SHOW_STATISTICS (Transact-SQL).
unfiltered_rowsunfiltered_rows bigintbigint 套用篩選運算式 (針對篩選的統計資料) 之前,資料表中的資料列總數。Total number of rows in the table before applying the filter expression (for filtered statistics). 如果統計資料未經過篩選,unfiltered_row 就會等於 rows 資料行中傳回的值。If statistics are not filtered, unfiltered_rows is equal to the value returns in the rows column.
modification_countermodification_counter bigintbigint 自從上次更新統計資料以來,前端統計資料資料行 (用以建置長條圖的資料行) 的總修改次數。Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated.

記憶體最佳化資料表: 正在啟動SQL Server 2016 (13.x)SQL Server 2016 (13.x)然後在Azure SQL DatabaseAzure SQL Database這個資料行包含: 總資料表中,更新最後一個時間統計資料或資料庫重新啟動之後的修改次數。Memory-optimized tables: starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database this column contains: total number of modifications for the table since the last time statistics were updated or the database was restarted.
persisted_sample_percentpersisted_sample_percent floatfloat 使用於未明確指定取樣百分比之統計資料更新的保存取樣百分比。Persisted sample percentage used for statistic updates that do not explicitly specify a sampling percentage. 如果值為零,表示這個統計資料未設定保存取樣百分比。If value is zero, then no persisted sample percentage is set for this statistic.

適用於: SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4

備註Remarks

sys.dm_db_stats_properties傳回任何下列的情況下的空資料列集:sys.dm_db_stats_properties returns an empty rowset under any of the following conditions:

  • object_id或是stats_id是 NULL。object_id or stats_id is NULL.
  • 找不到指定的物件,或者該物件沒有對應至資料表或索引檢視表。The specified object is not found or does not correspond to a table or indexed view.
  • 指定的統計資料識別碼沒有對應至指定之物件識別碼的現有統計資料。The specified statistics ID does not correspond to existing statistics for the specified object ID.
  • 目前的使用者沒有檢視統計資料物件的權限。The current user does not have permissions to view the statistics object.

此行為是用來安全地使用sys.dm_db_stats_properties當交叉套用至檢視中的資料列例如sys.objectssys.statsThis behavior allows for the safe usage of sys.dm_db_stats_properties when cross applied to rows in views such as sys.objects and sys.stats.

統計資料更新日期儲存在統計資料 Blob 物件中,其中還有長條圖密度向量,不是儲存在中繼資料中。Statistics update date is stored in the statistics blob object together with the histogram and density vector, not in the metadata. 讀取任何資料時,以產生統計資料,不會建立統計 blob、 沒有日期,而last_updated資料行是 NULL。When no data is read to generate statistics data, the statistics blob is not created, the date is not available, and the last_updated column is NULL. 這是已篩選統計資料的情況,其中述詞未傳回任何資料列,或為新的空白資料表的情況。This is the case for filtered statistics for which the predicate does not return any rows, or for new empty tables.

PermissionsPermissions

要求使用者對於統計資料資料行擁有選取權限,或是使用者擁有資料表,或使用者是 sysadmin 固定伺服器角色、db_owner 固定資料庫角色或 db_ddladmin 固定資料庫角色的成員。Requires that the user has select permissions on statistics columns or the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

範例Examples

A.A. 簡單範例Simple example

下列範例會傳回的統計資料Person.PersonAdventureWorks 資料庫中的資料表。The following example returns the statistics for the Person.Person table in the AdventureWorks database.

SELECT * FROM sys.dm_db_stats_properties (object_id('Person.Person'), 1);

B.B. 傳回資料表的所有統計資料屬性Returning all statistics properties for a table

下列範例會傳回 TEST 資料表現有的所有統計資料屬性。The following example returns properties of all statistics that exist for the table TEST.

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter   
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('TEST');  

C.C. 傳回經常修改之物件的統計資料屬性Returning statistics properties for frequently modified objects

下列範例會傳回目前資料庫中,自從上次更新統計資料以來修改前端資料行超過 1000 次的所有資料表、索引檢視表和統計資料。The following example returns all tables, indexed views, and statistics in the current database for which the leading column was modified more than 1000 times since the last statistics update.

SELECT obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter  
FROM sys.objects AS obj   
INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id  
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE modification_counter > 1000;  

另請參閱See Also

DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
sys.stats (Transact-SQL) sys.stats (Transact-SQL)
物件相關的動態管理檢視和函數 (Transact-SQL) Object Related Dynamic Management Views and Functions (Transact-SQL)
動態管理檢視與函數 (Transact-SQL)Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_incremental_stats_properties (Transact-SQL)sys.dm_db_incremental_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)sys.dm_db_stats_histogram (Transact-SQL)