MS SQL

MS SQL RSS

performance testing problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2007

Posts: 2

Reputation: koekie17 is an unknown quantity at this point

Solved Threads: 0

koekie17

Offline

Newbie Poster

performance testing problem

Dec 9th, 2007

Hi,

Simple query:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT * FROM MyTable WHERE Id=123456;
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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE('ALL');
CHECKPOINT;
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

Last edited by koekie17; Dec 9th, 2007 at 7:02 am.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: performance testing problem

Dec 9th, 2007

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!!

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Dec 2007

Posts: 2

Reputation: koekie17 is an unknown quantity at this point

Solved Threads: 0

koekie17

Offline

Newbie Poster

Re: performance testing problem

Dec 9th, 2007

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.