For example, if a predicate selects 12 rows from a table of 100 rows, the selectivity is (12/100) = 0.12.
When a column is referred to as “highly selective” that usually means values aren’t repeating all that often, if at all.
Finally, note that the histogram only represents data distribution for the first column in the density vector which is the first named column or left most column of the index.
EXEC [sp_helpindex] 'schema.object'; GO EXEC [sp_helpstats] 'schema.object', 'all'; GO SELECT sp.last_updated, stat.stats_id, as stats_name, STUFF((SELECT ', ' FROM sys.stats_columns AS statcols JOIN sys.columns AS cols ON statcols.column_id=cols.column_id AND statcols.object_id=cols.object_id WHERE statcols.stats_id = stat.stats_id and statcols.object_id=stat.object_id ORDER BY statcols.stats_column_id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') as stat_cols, sp.modification_counter, sp.rows, sp.rows_sampled FROM sys.stats as stat CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp JOIN sys.objects as so on stat.object_id=so.object_id JOIN sys.schemas as sc on so.schema_id=sc.schema_id WHERE sc.name= 'Schema' -- Schema Name and so.name='Table' -- Table Name ORDER BY 1 DESC OPTION (RECOMPILE) GO DBCC SHOW_STATISTICS(N'schema.object', statistic_name) --WITH STAT_HEADER, DENSITY_VECTOR, HISTOGRAM GO The above T-SQL is useful when looking at statistics.
Trace Flags: There are a number of undocumented trace flags that will allow you to determine which statistics objects were used by the query optimiser to produce an execution plan.
The below trace flags are useful when troubleshooting cardinality estimate issues: SELECT column_name FROM table_name OPTION ( QUERYTRACEON 3604, QUERYTRACEON 2363 --New CE Stats Report QUERYTRACEON 9481, QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204 --Legacy CE Stats Report ) Extended Events: The query_optimizer_estimate_cardinality XEvent fires when the Query Optimiser estimates cardinality on a relational expression.
This is outwith the scope of this blog, but see this post by Pedro Lopes and this post by Joe Sack for more details.
Mining The Plan Cache With TF 8666: If you really want to get your hands dirty, you can use the undocumented trace flag 8666 which exposes hidden internal information that is not normally available within an execution plan.
Density is a measure of the uniqueness of the data in a column(s).Notice how the CE (Cardinality Estimation) model versions tie in nicely with the database compatibility levels.Confirming the CE version used for a given query can be accomplished by checking the Cardinality Estimation Model Version attribute in an execution plan.In SQL Server 2014, a redesigned, revamped, cardinality estimator model 120 was introduced.Further improvements were made in SQL Server 2016 which uses cardinality esimation model 130.