You've actually got a few questions in here, so I'll break 'em out individually.
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?
In order to answer that question, the first thing we would need is the actual execution plan for the query. In SQL Server Management Studio, you can get that by clicking Query, Include Actual Execution Plan. Run the query, click on the Execution Plan tab, and right-click anywhere in the whitespace to click Save Plan As. Save that, and post it somewhere for people to download and examine.
The next thing we would need is the output from DBCC SHOW_STATISTICS for the stats on that table. You've hinted at the output, and that's a good start, but the raw output will help us understand exactly what's going on.
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?
No.
I have an index on (TaskExecStatusID,TaskExecUpdatedDate),
If you frequently use the query in the example (with TaskExecUpdateDate IS NULL), then you might check out filtered indexes. They're a new feature in SQL Server 2008 that allows you to put a where clause on your index, basically.
http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/
Use:
set showplan_xml on;
go
Your query;
go
set showplan_xml off;
go
This will give the XML, and you should be able to see the Estimates in there.
Best Answer
The estimated execution plan is generated based solely on the statistics that SQL Server has - without actually executing the query. The query optimizer is just asked what it would most likely do with this query, based on all the information it has on the query and the data distributions etc.
This works OK, the query doesn't need to run (which could take a long time), but if the statistics are out of date, the plan might be severely skewed.
The actual execution plan is just that - the actual execution plan that was used when actually running the query. This will show you things that might hint at "out-of-date" statistics etc. But to get this, you must run the query - which can take a long time.