different time for same query

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 1

there could be a several issues. I would check this first.

my first guess the first time you ran it...the pages were not in cache...

you need to test this on dev machine...run

DBCC DROPCLEANBUFFERS

then run the qry...this will give you the time when the pages are not in cache

then run it again without running DBCC DROPCLEANBUFFERS, this will tell you how long it took to run while in cahce

also run this.before your qry .

USE AdventureWorks2008R2;

GO

SET STATISTICS IO ON;

GO

this will tell you how many pages are IO(phyiscal) vs buffer(logical)

jlefebvre-924740 Valued Member Points: 55 More actions · Answer 2

Valued Member

Points: 55

September 1, 2010 at 10:11 am

thanks for your answer but there is no physical reads

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

The first time you exec a query, it has to create an execution plan, pull the data, etc.

The second time, unless it has been flushed, it will already have the execution plan saved, data cached, etc.

The first time a query runs is always slower.

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 4

Derrick is correct..

SET STATISTICS TIME ON

1. run the query

2. DBCC FREEPROCCACHE ...ONLY ON DEV!

3. run the query again, this will more or less tell you how much time you are spending compiling the plan.

4. now compare the two, before the qry complied and with no compile

10 second compiling the plan is a while but depending on the qry this could be true!

----if the times are close then the next thing to ask are the qry that you are running the EXACT SAME..

dont run a qry with a search on X and then a search on Y.

info 14854 Newbie Points: 1 More actions · Answer 5

Thanks for your advice !