performance testing problem

[koekie17]

Hi,

Simple query:

select * from MyTable where Id=123456;

The first time I execute this query it takes 14 seconds. However, the second time I execute it takes 0 seconds.

I wanted to clear the cache so the second execution takes 14 second just like the first execution. So i tried:

DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE('ALL');
CHECKPOINT;

But no success. I'm using ms sql server management studio (2005). I also tried the option Reset client statistics. I even restarted the sql server. No matter what I try, it looks like the result keeps somewhere saved.

Does somebody have a solution for this?

Cheers

[Ramy Mahrous]

It maybe SQL Server was busy doning some jobs that's why, it executed this query in 14 seconds (I assume you running this query against table has records less than 100, 000)

I don't deadly think that '*' the reason of that bad performance!!

[koekie17]

The table has 10.583.483 records. If I reboot my laptop the first query takes 14 seconds again. I think it has something to do with the primary key I'm using in the where clause.

If I change the where clause to "WHERE another_column = 23434" the cache clearing works fine. with the "Where Id=123456" (Id is a primary key) it does not.