I understand your point and you are right Paul,

It's all about estimated costs and assumptions made by the optimiser. Try a TOP (100) query hinting the non-clustered index and hinting...

I understand your point and you are right Paul,

It's all about estimated costs and assumptions made by the optimiser. Try a TOP (100) query hinting the non-clustered index and hinting...

Execution plan without index hint:

Paul you are right, by using order by, optimizer chooses to use the index, why is that behavior, but it is still interesting scanning table for TOP 100 in a...

DDL of the table is, sql plans is attached, without index hint it took absolutely 00:06:15 scanning entirely whole 29 GB table!

/****** Object: Table [dbo].[tblSMSArsiv] Script...

Oh, thats my fault, they is same tables, I forgot to replace the names of second..

SELECT TOP 1 *

FROM tbl1 with (nolock)

where col1>='2010-07-01' and col1<'2010-07-06'

--Takes 2 minutes with table scan

SELECT...

By the way index_3 is on col1 ascending

yes update, insert will be needed.. we'll recover them fully..

so I see no other way than to a new full backup restore (it will come a long way in a disk after being encrypted) , yes the test data...

We are planning a disaster test, but without switching the databases for the first test, only test users will be directed to test environment, is this a good approach? So...

The reason for encrption is we are planning to transfer sensitive data, physically transfer a machine over a long road with the backups on disk, because it will be faster...

Thanks Andy!!! This solved my problem, msdn says master database is set to TRUSTWORTHY OFF by default, when I set the option it worked, thanks again,

Cheers Serter

Thanks jeroshan, I quitted all windows and it worked! But all the connections were with master in sysprocesses not Test db..

Hi Jason, thanks for the replies, in our environment our primary consideration is performance, our database engines are consolidated so each have a high workload, do I have a trouble...

Hi, disabling and enabling the default trace enabled option in sp_configure has restarted the stopped default trace in my server (I couldn't find out why it stopped by the way,...