The histogram captures the frequency of occurrence for each distinct value in the first key column of the statistics object. This actually helps SQL Server Query Optimizer to decide whether to use Index Seek or Index Scan. The value for all_density (1 / number of distinct values for a column) ranges from 0.0+ to 1, which indicates selectivity (duplicates), 0.0+ being highly selective with less duplicates whereas 1 is less selective with a high number of duplicates as a rule of thumb, the lesser the better. You can also view the statistical information by going to the properties page of the statistics object in SQL Server Management Studio as shown below: This only shows histogram based on data stored in the statistics object DBCC SHOW_STATISTICS ("SalesOrderDetail", NCI_SalesOrderDetail_ProductID ) WITH HISTOGRAM This shows header, histogram, and density vector based on data stored in the statistics object DBCC SHOW_STATISTICS ("SalesOrderDetail", NCI_SalesOrderDetail_ProductID ) DBCC SHOW_STATISTICS shows the header, histogram, and density vector based on data stored in the statistics object. For example, as shown in the query below, you can use the DBCC SHOW_STATISTICS command. There are different ways you can view the details of the statistics objects. Multi-column statistics also contains a correlation of values among the columns (called densities), which are derived from the number of distinct rows or the column values. Often statistics are created on a single column but it’s not uncommon to create statistics on multiple columns.Įach statistics object contains a histogram displaying the distribution of values of the column (or of the first column in the case of multi-column statistics). SQL Server Query Optimizer uses statistics to estimate the distribution of values in one or more columns of a table or index views, and the number of rows (called cardinality) to create a high-quality query execution plan. In this article series, I am going to talk about statistics in detail. For example, based on these statistical information SQL Server Query Optimizer might decide whether to use the index seek operator or a more resource-intensive index scan operator in order to provide optimal query performance. The SQL Server Query Optimizer uses this statistical information to estimate the cardinality, or number of rows, in the query result to be returned, which enables the SQL Server Query Optimizer to create a high-quality query execution plan. Statistics refers to the statistical information about the distribution of values in one or more columns of a table or an index.
0 Comments
Leave a Reply. |