Right below the name of the statistics, there are three main areas of information: Use the below command to view its histogram :ĭBCC SHOW_STATISTICS(table_name, stats_name) For automatically generated stats we cannot view histogram through GUI. If you do not see the histogram, then that’s because you might have clicked on “automatically” generated statistic. Below is a sample of what you might see on this page : Here you will find detailed information about data distributions. More interesting data is shown on the “Details” page.
(You may also trigger an instant statistics update by selecting the relevant check box). If you open the properties window of a statistics object, the “General” page provides some data about what columns are included and the point in time of the last update. For index-related statistics, this column shows a value of “false”, even though these statistics are auto-generated as well. The sys.sys_stats view has a column called auto_created that differentiates between automatically and manually created statistics. Also it is not possible to know which stats the optimizer uses to generate plan and which stats are never used.(Like we have sys.dm_db_index_usage_stats for indexes there is no such DMV for stats)Īuto-created statistics can be easily distinguished by their name, which always starts with “_WA”.
We seldom use CREATE STATISTICS command because it is not trivial to know by which statistics will the optimizer benefit. Starts with _ WA_*ģ) User defined statistics which are created with CREATE STATISTICS command by the DBA These statistics have the index nameĢ) Statistics created by Optimizer(Column statistics).
There are 3 types of statistics in a SQL Server database.ġ) Statistics created due to index creation. These are referred to as AUTOMATICALLY created.(Obviously they will be created only if you are running your database in Auto Create Stats mode. In such cases, SQL Server automatically creates stat object corresponding to those column(s) so that they can be referred the next time such a query comes. Also suppose that there is no manually created stat for those columns. Suppose a query is fired on a table that involves columns on which there are no indexes (hence no index related stats). BUT this is NOT called automatically created stat (Though it is created so) instead its known as index statistics. So if you create an index, a corresponding stats object is itself created.
Statistic object can be created manually using CREATE STATISTICS command. Also a statistic object is automatically created when an index is created/rebuilt. This is also the exact basic principle of Hypothetical Indexes….which is a more interesting topic…. They will provide incorrect information to the optimizer and most probably will result in poor plan generation.Īnother point to stress is that to make a plan, stats will be consulted, not the base table. If the stats are outdated, this means they no longer correctly represent the data distribution.
The optimizer obtains cardinality (number of records within a range) estimations by the use of statistics. These stats are much smaller than the original amount of data and as such, allow for a faster plan generation. They represent the table in the sense that optimizer refers to the statistics to generate a plan rather than scanning the whole table for plan generation. How the data is distributed inside the table. Statistics contain information about the data.