The table has about 1.5 billion rows, maybe that's why it's not able to correctly estimate the "Estimated Rows". Query is not timing out, it is using the execution plan...

I rebuild the stats and reran the query, attached is the Index Seek values on the same index.

I can try to rebuild the stats, but if stats are out of date it still picked the correct index.

It's a prod server so can't clear the cache but i...

Yes, they are.

Yes, the log shipping itself works but once we try to bring the db in standby mode to run the ETL it complains that db first need to be upgraded...

We run ETL daily and currently it takes about 12 hours.

Agree but it's a very large 5tb+ DW so rebuilding index might not be an option for us in favor of quick cut-over.

Thanks Grant. So do we still need to update statistics as suggested by Microsoft?

We are not planning to change the compatibility level until after couple of weeks of migration to...

Thanks a lot. Do you know the command to run plink from sql agent?

Luis Cazares (10/4/2013)

---

You might want to add a column to your queries, using ROW_NUMBER().

Here's an example, but without some data more similar to the real data, it's hard to give...

Thanks for the suggestions guys. I am sorry i should not have used "P1" & "p2" as codes, these are just my examples codes actual values won't have any correlation...

Thanks Luis i did some changes and here is what is giving me my expected results.

SELECT DATEDIFF(DD, date1, date2), DATEDIFF( mm, date1, date2), DATEDIFF( mm, date1, date2) + CASE WHEN...

That...

I think i got this one.

WITH A as

(

SELECTcast('FirstCall' as varchar(100)) Cal, 0 DiffBtwCalls, *

FROM [MyTransactions]

WHERESNRank = 1

UNION ALL

SELECTcast(CASE WHENFirstCall.SNRank IS NOT NULL

AND FirstCall.TFunction = 'activate'

AND SecondCall.TFunction = 'activate'...

Already seeing that 😀 but we understand the risk.