SSMS first time sql query MUCH slower than subsequent times - why ?

Question

SSMS first time sql query MUCH slower than subsequent times - why ?

isuckatsql

SSChampion

Points: 10381
More actions
March 5, 2010 at 10:45 am

#216308

I am running a 'vanilla' sql query on a FTI field and the first time the query runs in SSMS it takes 8 seconds, then each subsequent time it seems to average 4 or 5 seconds for the same query.
What would cause this ?
BTW i am directly logged onto the server.
Thanks

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Data caching. The first time you run the query the data has to be pulled from disk. Subsequent queries for the same data pull from cache.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Plan caching. The first time you run the query it has to be compiled and an execution plan generated. Subsequent queries used the cached execution plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

isuckatsql SSChampion Points: 10381 More actions · Answer 3

isuckatsql

SSChampion

Points: 10381

March 5, 2010 at 11:19 am

#1129343

Thanks!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Heh... bad code. What are you doing that requires 4 to 8 seconds to run and how many rows are involved?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crazy4sql SSCoach Points: 19590 More actions · Answer 5

like all said and further to add....in case you want query to use same time then run DBCC FreeProcCache and execute your query again.

If you want to list all the cache details in the database :-

SELECT cacheobjtype,objtype,sql FROM syscacheobjects

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ashish.kuriyal (3/7/2010)
like all said and further to add....in case you want query to use same time then run DBCC FreeProcCache and execute your query again.

Not on a production server.

If you want to list all the cache details in the database :-
SELECT cacheobjtype,objtype,sql FROM syscacheobjects

syscacheobjects is a deprecated system view, it's included only for backward compat with SQL 2000 and should not be used in new development. Since this is a SQL 2008 server, use sys.dm_exec_cached_plans and sys.dm_exec_query_stats instead. They show lots more info than syscacheobjects ever did.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass