Sql-server – Why can the index seek estimate the right number of rows and the sort operator can’t

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/

When I have this kind of performance problems in a big query I split it into small queries with temporary tables. For me it is a solution and performance ratio may be 10 to 1 or more.

First step:

with t1 as (
SELECT 
  t1.Symbol, 
  t1.Period, 
  t.TradingDate,
  t1.Value as FastValue
FROM      tblDailySMA t1 
LEFT JOIN tblTradingDays t 
        ON t.TradingDate = t1.TradeDate
) 
select * 
into #t1
from t1;

--I include period into idex to avoid table access on next query
create index t1_idx on ( Symbol, TradeDate, Period ) 

Secon step:

with t2 as (
SELECT
  t1.Symbol, 
  t1.Period as period_t1, 
  t.TradingDate,
  t1.Value as FastValue
  t2.Period as period_t2,
  t2.Value as SlowValue,
  t2.TradeDate
FROM       #t1 as t1
INNER JOIN tblDailySMA t2 
   ON t1.Symbol = t2.Symbol AND t1.TradeDate  = t2.TradeDate
WHERE t1.Period < t2.Period
)
select 
  *
into #t2
from t2;

--Here create indexes for t2
--Here next and final query 

And so on. One benefit of this system is that you can improve queries step by step.