When you are tuning queries, there are two fundamental concepts that you need to be well aware of – Density & Selectivity. Today, let’s talk about Selectivity. In simple words, it means, how selective your predicates are. Low selectivity means your query is returning a high number of records and, high selectivity means your query is returning a low number of records. A lot of decisions that the optimizer takes depend on selectivity. A classic example could be Scan vs Seek. Typically, when a given predicate is returning a high number of records, the optimizer may deploy a Scan operator to read the data pages. Conversely, if the predicate returns a low number of records, the optimizer may deploy a Seek operator to read the data. Assumptions are being made here that there is a non-clustered index on the column used in the predicate. Given this simple example, if you were ever wondering why are you seeing a scan sometimes and seek other times, think Selectivity – it helps the optimizer take a cost-based decision. Sounds interesting? Watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.
Happy Learning.
Leave A Comment