SQL server queries are really slow only on first run

Question

Somewhat strange problem... when I start my .NET app for the first time after rebooting my machine, the SQL Server queries are really slow... when I pause the debugger, I notice that it's hanging on getting the response from the query. This only happens when connecting to a remote SQL server (2008)... if I connect to one on my local machine, it's fine. Also, if I restart the app, it works fast, even off the remote SQL server, and subsequent runs are also fine. The only problem is when I connect to a remote SQL server for the first time after rebooting my machine. What's more, I have even noticed this same exact behavior with a 3rd party app (also .NET) that also connects to a remote SQL server.

Another piece of info... this has only started hapenning since I upgraded my machine from XP to Win7 (64 bit). Also, other developers on my team who upgraded to Win7 are seeing the same behavior (both with the app we're developing and the 3rd party .NET app).

EDIT: also copied to https://serverfault.com/questions/100141/sql-server-queries-are-really-slow-only-on-first-run due to suggestion by a commenter

Answer 1

Most likely you are enjoying the caching affect. When you first run a query, SQL generates an execution plan and then caches it. If you run the query again, it remembers the execution plan and in some cases you see a speed gain. So if you are testing a query, you need to clear the cache. Below is what I do.

Quote from Devx website (DEVx Tip)

run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.

HTH

Answer 2

Check your SQL Server Client Config Settings... perhaps TCP/IP is not first in the list.