sys.dm_db_stats_histogram & Amp;#40;transact-SQL)sys.dm_db_stats_histogram (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 the statistics histogram for the specified database object (table or indexed view) in the current SQL ServerSQL Server database. 類似於DBCC SHOW_STATISTICS WITH HISTOGRAMSimilar to DBCC SHOW_STATISTICS WITH HISTOGRAM.

注意

此 DMF 開始才提供SQL Server 2016 (13.x)SQL Server 2016 (13.x)SP1 cu2 開始This DMF is available starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU2

語法Syntax

sys.dm_db_stats_histogram (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).
step_numberstep_number intint 在長條圖中的步驟數目。The number of step in the histogram.
range_high_keyrange_high_key sql_variantsql_variant 長條圖步驟的上限資料行值。Upper bound column value for a histogram step. 此資料行值也稱為索引鍵值。The column value is also called a key value.
range_rowsrange_rows realreal 資料行值在長條圖步驟內的預估資料列數,不包括上限。Estimated number of rows whose column value falls within a histogram step, excluding the upper bound.
equal_rowsequal_rows realreal 資料行值等於長條圖步驟之上限的預估資料列數。Estimated number of rows whose column value equals the upper bound of the histogram step.
distinct_range_rowsdistinct_range_rows bigintbigint 在長條圖步驟內具有相異資料行值的預估資料列數,不包括上限。Estimated number of rows with a distinct column value within a histogram step, excluding the upper bound.
average_range_rowsaverage_range_rows realreal 平均數目,上限不包括長條圖步驟內重複的資料行值的資料列 (RANGE_ROWS / DISTINCT_RANGE_ROWS針對DISTINCT_RANGE_ROWS > 0)。Average number of rows with duplicate column values within a histogram step, excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0).

備註Remarks

結果集sys.dm_db_stats_histogram會傳回類似資訊DBCC SHOW_STATISTICS WITH HISTOGRAM還包含object_idstats_id,和step_numberThe resultset for sys.dm_db_stats_histogram returns information similar to DBCC SHOW_STATISTICS WITH HISTOGRAM and also includes object_id, stats_id, and step_number.

因為資料行range_high_keysql_variant 資料類型,您可能需要使用CASTCONVERT如果述詞與非字串常數的比較。Because the column range_high_key is a sql_variant data type, you may need to use CAST or CONVERT if a predicate does comparison with a non-string constant.

長條圖Histogram

長條圖會測量資料集中每一個相異值的發生頻率。A histogram measures the frequency of occurrence for each distinct value in a data set. 查詢最佳化工具會計算有關統計資料物件之第一個索引鍵資料行中資料行值的長條圖,以統計方式取樣資料列或執行資料表或檢視表中所有資料列的完整掃描來選取資料行值。The query optimizer computes a histogram on the column values in the first key column of the statistics object, selecting the column values by statistically sampling the rows or by performing a full scan of all rows in the table or view. 如果長條圖是從一組取樣的資料列所建立,資料列數和相異值數的儲存總計會是預估值,而且不需要為整數。If the histogram is created from a sampled set of rows, the stored totals for number of rows and number of distinct values are estimates and do not need to be whole integers.

若要建立長條圖,查詢最佳化工具會排序資料行值、計算符合每一個相異資料行值的值數目,然後將資料行值彙總成最多 200 個連續長條圖步驟。To create the histogram, the query optimizer sorts the column values, computes the number of values that match each distinct column value and then aggregates the column values into a maximum of 200 contiguous histogram steps. 每一個步驟都包含某個範圍的資料行值,後面緊接著上限資料行值。Each step includes a range of column values followed by an upper bound column value. 此範圍包括界限值之間的所有可能資料行值,但是不包括界限值本身。The range includes all possible column values between boundary values, excluding the boundary values themselves. 最低的已排序資料行值就是第一個長條圖步驟的上限值。The lowest of the sorted column values is the upper boundary value for the first histogram step.

下列長條圖顯示包含六個步驟的長條圖。The following diagram shows a histogram with six steps. 第一個上限值左側的區域就是第一個步驟。The area to the left of the first upper boundary value is the first step.

每一個長條圖步驟:For each histogram step:

  • 粗線代表上限值 (range_high_key) 以及其所發生的次數 (equal_rows)Bold line represents the upper boundary value (range_high_key) and the number of times it occurs (equal_rows)

  • range_high_key 左邊的實線區域代表資料行值範圍,以及每一個資料行值發生的平均次數 (average_range_rows)。Solid area left of range_high_key represents the range of column values and the average number of times each column value occurs (average_range_rows). 第一個長條圖步驟的 average_range_rows 一定是 0。The average_range_rows for the first histogram step is always 0.

  • 虛線代表用來預估範圍內相異值總數的取樣值 (distinct_range_rows) 以及範圍內的值總數 (range_rows)。Dotted lines represent the sampled values used to estimate total number of distinct values in the range (distinct_range_rows) and total number of values in the range (range_rows). 查詢最佳化工具會使用 range_rowsdistinct_range_rows 來計算 average_range_rows,而且不會儲存取樣值。The query optimizer uses range_rows and distinct_range_rows to compute average_range_rows and does not store the sampled values.

查詢最佳化工具會根據長條圖步驟的統計重要性來定義長條圖步驟。The query optimizer defines the histogram steps according to their statistical significance. 它會使用最大值差異演算法,讓長條圖中的步驟數減至最少,同時讓界限值之間的差異最大化。It uses a maximum difference algorithm to minimize the number of steps in the histogram while maximizing the difference between the boundary values. 步驟數的最大值為 200。The maximum number of steps is 200. 長條圖步驟的數目可以少於相異值數目,即使包含了少於 200 個界限點的資料行也是如此。The number of histogram steps can be fewer than the number of distinct values, even for columns with fewer than 200 boundary points. 例如,包含 100 個相異值的資料行可以擁有少於 100 個界限點的長條圖。For example, a column with 100 distinct values can have a histogram with fewer than 100 boundary points.

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

下列範例會建立,並填入簡單的資料表。The following example creates and populates a simple table. 然後會建立統計資料Country_Name資料行。Then creates statistics on the Country_Name column.

CREATE TABLE Country
(Country_ID int IDENTITY PRIMARY KEY,
Country_Name varchar(120) NOT NULL);
INSERT Country (Country_Name) VALUES ('Canada'), ('Denmark'), ('Iceland'), ('Peru');

CREATE STATISTICS Country_Stats  
    ON Country (Country_Name) ;  

主索引鍵會佔用stat_id數字 1,因此呼叫sys.dm_db_stats_histogramforstat_id數字 2,則可傳回統計資料長條圖Country資料表。The primary key occupies stat_id number 1, so call sys.dm_db_stats_histogram for stat_id number 2, to return the statistics histogram for the Country table.

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('Country'), 2);

B.B. 實用的查詢:Useful query:

SELECT hist.step_number, hist.range_high_key, hist.range_rows, 
    hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'<statistic_name>';

C.C. 實用的查詢:Useful query:

下列範例會從資料表選取Country資料行上的述詞與Country_NameThe following example selects from table Country with a predicate on column Country_Name.

SELECT * FROM Country 
WHERE Country_Name = 'Canada';

下列範例會查看先前建立的統計資料,資料表上Country和資料行Country_Name長條圖步驟符合上述查詢中的述詞。The following example looks at the previously created statistic on table Country and column Country_Name for the histogram step matching the predicate in the query above.

SELECT ss.name, ss.stats_id, shr.steps, shr.rows, shr.rows_sampled, 
    shr.modification_counter, shr.last_updated, sh.range_rows, sh.equal_rows
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) sh
WHERE ss.[object_id] = OBJECT_ID('Country') 
    AND ac.name = 'Country_Name'
    AND sh.range_high_key = CAST('Canada' AS CHAR(8));

另請參閱See Also

DBCC SHOW_STATISTICS & Amp;#40;transact-SQL&#41 DBCC SHOW_STATISTICS (Transact-SQL)
物件相關的動態管理檢視和函數 & Amp;#40;transact-SQL&#41Object Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL)sys.dm_db_stats_properties (Transact-SQL)