Here is my finding can some shedsome light on it. If i replace the below qry with code in * it run only in 2 sec comapred to 16min. any...

from the execution plan is see that #revdates has the following

actual number of rows = 3426054035

estimated rows = 643743

when i...

a

do you see any problem in the above query and why it is scannig more records than required ?

Here is what i found, the tem table #revdate has only half million records but in the execution plan is that it is scanning more than 250 million records, how...

yes server /hardware are same and when you say slow qry temp table is large may i know how did you find that because technically it should be small as...

yes you are right, i see an index misssing at this point am not sure how that index is missing i may have to research but that is from the...

as i said bot hare idential except the slow qry is a smaller database comapred to fast_qry and here is hte count of #revdates table respectively.

slow_qry = 454080

fast_qry =...

I am adding 2 plans as attachements both are completely identical but one is lsow and the other is fast.

query is a simple query and index are also rebuilt on...

steve

what format of execution plan you are looking into ?

I am not looking to tune the query but would like to dignosis the issue why same query can run faster on larger database compared to smaller database.

I am not doubting the query but looking for exact reason why same query performs diffrent way on both databases which are similar except the size.

How can a user run Database Tuning Advisor without having db_owner previalges ?

these are the 4 areas he is looking for at server level

DTA

Profiler

Perf Monitor

DMV's

what are risk involved in giving VIEW SERVER STATE by which he can access all DMV's ?

Also would...

Actually we have an application on which perofrmance is worst and would like to improve it as much as we can and so we are hiring a tuning specialist for...