The Importance of Cardinality

Bert Wagner shows us why cardinality is important to understand when indexing data:

When building indexes for your queries, the order of your index key columns matters.  SQL Server can make the most effective use of an index if the data in that index is stored in the same order as what your query requires for a join, where predicate, grouping, or order by clause.

But if your query requires multiple key columns because of multiple predicates (eg. WHERE Color = ‘Red’ AND Size= ‘Medium’), what order should you define the columns in your index key column definition?

One of my favorite books for query tuning is a bit long in the tooth at this point but remains quite relevant, and a key point there is to look for ways to drop the largest percent of rows as soon as possible. This applies for good indexes as well: they’ll let you ignore as large a percentage of your irrelevant data as you can, as soon as possible.

Related Posts

Logical Equivalence and Query Tuning

Erik Darling didn’t warn us that there would be math: Often when query tuning, I’ll try a change that I think makes sense, only to have it backfire. It’s not that the query got slower, it’s that the results that came back were wrong different. Now, this can totally happen because of a bug in previously used […]

Read More

SQL Server Execution Plan Operators

Bert Wagner takes us through some of the more common execution plan operators: Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb. SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow […]

Read More

Categories

January 2019
MTWTFSS
« Dec Feb »
 123456
78910111213
14151617181920
21222324252627
28293031