What is cardinality and how does it affect performance (SQL Server)?
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.
Answer
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.