From a conceptual point of view think of this:

In a phone directory the clustered index is LastName,FirstName, Street Address.

That being the case how can the be a clustered index on...

I would be interested in seeing the plan for this ..

Select TradeID,DataSource.DataSourceID

FROM

dbo.Trade

INNER JOIN dbo.DataSource ON dbo.DataSource.DataSourceID=dbo.Trade.DataSourceID

WHERE

(...

Hmm, odd.

Im interested in this part of the plan

Which is pretty much the very first join between datasource and trade.

The estimate from the hash join is 1 ,...

GilaMonster (12/19/2012)

No need to use recompile. The stats update alone would...

I think your statistics are out of date on that table.

Update them , then run the queries again (using WITH RECOMPILE) and see what the plans look like.

The estimations and...

The writes could be due to a memory spill

http://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx

In terms of what else *could* (pure guesswork 🙂 ) be happening here:

The "delay" may not even be in SQL Server,...

Fraid not ,

if a value is used multiple time in Indexing for Col2 then you will get "duplicate" rows back

select col2,count(*)

from indexing

group...

Have a look at the seek predicate for the DATE cast.

You will see that it has converted the predicate to a date range

ie

>= Date and < Date

So IMO...

Did you read those articles ?

Example 3 on the beyondrelational one is very close to what you require.

So this is in a WHERE clause ??

Assuming so, this is what is known as a non-sargable condition.

Basically, as you have wrapped the datetime column in a function...

Three seeks here,

Anyway the point is that the optimzer is now able to choose between a seek and scan not the developer 🙂

dilipd006 (11/29/2012)

Thanks Eugene..never known we can use Cross Apply in this...

Using uncommitted/nolock may well 'fix' the problem but please understand that you will be reading dirty data.

If the transaction is to be rolled back then what does that be for...

RedGate sqlsearch may be your best bet

Can you post both of the new plans ?