What is cardinality and how does it affect performance (SQL Server)?

Question

We have a massive table where I need to make an update on a single row. I don't know the primary key of the row, but I have a varchar value that is unique within that table. I also have the value of a few other columns in that tables.

Running the update is taking upwards of three minutes, and I assume its doing a full table scan.

Looking at the indices on the table, the index on the column has a cardinality of zero and page count of zero. There are other indices with a cardinality equal to the number of rows in the table (couple million) and page count of a couple hundred thousand.

What do these numbers actually mean?

And as a followup, would adding a restriction that hits an index with a higher cardinality or number of pages speed up the execution? Or is there anything else I can look at on the indicies to find one that would be better suited to quickly find the row I ned to change.

Masaccio

Does this answer your question? What is cardinality in Databases? – Reindeer

Answer 1

Cardinality is the number of unique values for that field within the table.

I suspect that SQL Server didn't actually create the index because there are no records for it. Is is possible this field is all NULLs, or there is a filter on it that eliminates all records like WHERE 1=0?

A good place to start would be to run the update and capture the actual execution plan.

EDIT:

Index Type 3 is an XML Index.

Please see this page for reference.

The structure of XML indexes is quite a bit different. I don't use them myself but from some quick reading it sounds like it creates a series of additional indexes that can't be accessed directly by users but are used by the query engine.

Noun

The field is definitely not null. It has an index type of 3 ... which I'm wondering if its an unclustered index and only used to make ure that values are unique. – Masaccio

@Reverend - can you look at the index definition? – Noun

Yeah ... but I'm looking at it through Squirrel which only gives me a number. It says its index type 3. – Masaccio

@Reverend - I'm looking that up now. I suspect it may be an XML index or something weird...What data is the field? – Noun

that can't be right ... it's just a string ... like a uuid (not exactly, but effectively) – Masaccio

It's definitely an XML index. – Noun

weird ... in sys.indexes it says the type is 2 ... either way it's also says its a nonclustered index which makes sense why using it doesn't improve that much performance. – Masaccio