Long running queries in a database may or may not be a problem, depending on the type of system. In a data warehouse solution, queries that runs for several minutes may be perfectly normal and nothing to worry about. But in an OLTP system, long running queries may cause performance problems. Read on to see how to monitor for long running queries in SQL Server.
When SQL Server evaluates if an index should be used to retrieve data in a query, selectivity is one of the most important factors. Of course, given that the index column matches the filter or join columns.
If a column only has one or very few unique values, index selectivity is low. True/false columns and gender is common examples.
If there are only unique values in a column, index selectivity is high. Primary keys meets these requirements as they are always unique.
SQL Server favors indexes that are built on columns with high selectivity, since this may return fewer rows and therefore at a lower cost.
If the index has multiple columns, it is preferable (if possible) that the columns are in the order of their selectivity (most selective first).
So how is selectivity computed?