I'm trying to understand multi column indexes and statistics, here is my real world example:
Table: TaskExecutions, size=~1 million rows. Query SARGS: TaskExecStatusID = 3 AND TaskExecUpdatedDate IS NULL Distribution:: TaskExecStatusID: 1=160k rows, 2=370k rows, 3=560k rows. TaskExecUpdatedDate: Null=171k rows.
I have an index on (TaskExecStatusID,TaskExecUpdatedDate),
My problem/question is that the estimated number of rows for the above query is 256, the actual number of rows is 560K.
I want to understand why there is such a big difference between these two numbers?
Stats are up to date, the stats for the index are multi-column stats. I've tried creating in index/stats on (TaskExecUpdatedDate, TaskExecStatusID), and the estimate/actual does not change.
If I run a DBCC SHOW_Statistics, the density section has both columns in it, the histogram does not. Does SQL server produce a histogram for the combination of columns in multi-column statistics?