Skip to content

Latest commit

 

History

History
86 lines (68 loc) · 6.44 KB

sys-dm-db-incremental-stats-properties-transact-sql.md

File metadata and controls

86 lines (68 loc) · 6.44 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sys.dm_db_incremental_stats_properties (Transact-SQL)
sys.dm_db_incremental_stats_properties (Transact-SQL)
rwestMSFT
randolphwest
12/18/2017
sql
system-objects
reference
sys.dm_db_incremental_stats_properties
sys.dm_db_incremental_stats_properties_TSQL
dm_db_incremental_stats_properties
dm_db_incremental_stats_properties_TSQL
sys.dm_db_incremental_stats_properties
TSQL

sys.dm_db_incremental_stats_properties (Transact-SQL)

[!INCLUDEsqlserver]

Returns properties of incremental statistics for the specified database object (table) in the current [!INCLUDEssNoVersion] database. The use of sys.dm_db_incremental_stats_properties (which contains a partition number) is similar to sys.dm_db_stats_properties which is used for non-incremental statistics.

This function was introduced in [!INCLUDEssSQL14_md] Service Pack 2 and [!INCLUDEssSQL15_md] Service Pack 1.

Syntax

sys.dm_db_incremental_stats_properties (object_id, stats_id)

Arguments

object_id
Is the ID of the object in the current database for which properties of one of its incremental statistics is requested. object_id is int.

stats_id
Is the ID of statistics for the specified object_id. The statistics ID can be obtained from the sys.stats dynamic management view. stats_id is int.

Table Returned

Column name Data type Description
object_id int ID of the object (table) for which to return the properties of the statistics object.
stats_id int ID of the statistics object. Is unique within the table. For more information, see sys.stats (Transact-SQL).
partition_number int Number of the partition containing the portion of the table.
last_updated datetime2 Date and time the statistics object was last updated. For more information, see the Remarks section in this page.
rows bigint Total number of rows in the table 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_sampled bigint Total number of rows sampled for statistics calculations.
steps int Number of steps in the histogram. For more information, see DBCC SHOW_STATISTICS (Transact-SQL).
unfiltered_rows bigint Total number of rows in the table before applying the filter expression (for filtered statistics). If statistics are not filtered, unfiltered_rows is equal to the value returns in the rows column.
modification_counter bigint Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated.

This column does not contain information for memory-optimized tables.

Remarks

sys.dm_db_incremental_stats_properties returns an empty rowset under any of the following conditions:

  • object_id or stats_id is NULL.
  • The specified object is not found or does not correspond to a table with incremental statistics.
  • 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.

This behavior allows for the safe usage of sys.dm_db_incremental_stats_properties when cross applied to rows in views such as sys.objects and sys.stats. This method can return properties for the statistics that correspond to each partition. To see the properties for the merged statistics combined across all partitions, use the sys.dm_db_stats_properties instead.

Statistics update date is stored in the statistics blob object together with the histogram and density vector, not in the metadata. 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.

Permissions

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. Simple example

The following example returns the statistics for the PartitionTable table described in the topic Create Partitioned Tables and Indexes.

SELECT * FROM sys.dm_db_incremental_stats_properties (object_id('PartitionTable'), 1);

For additional usage suggestions, see sys.dm_db_stats_properties.

See Also

DBCC SHOW_STATISTICS (Transact-SQL)
sys.stats (Transact-SQL)
Object Related Dynamic Management Views and Functions (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_stats_properties
sys.dm_db_stats_histogram (Transact-SQL)