Geek Answers Handbook

SQL Server Queries Are Very Slow Only When First Run

A somewhat strange problem ... when I first run my .NET application after rebooting my machine, SQL Server queries are very slow ... when I pause the debugger, I notice that it hangs on receiving a response from the query. This only happens when connecting to a remote SQL server (2008) ... if I connect to one on my local machine, this is normal. In addition, if I restart the application, it runs quickly, even from a remote SQL server, and subsequent runs are also fine. The only problem is when I first connect to a remote SQL server after rebooting my machine. What else, I even noticed the same exact behavior with a third-party application (also .NET), which also connects to a remote SQL server.

Another information ... this is just the beginning of hapenning, since I upgraded my machine from XP to Win7 (64 bit). In addition, other developers of my team who upgraded to Win7 see the same behavior (both with the application we are developing and with a third-party .NET application).

EDIT: also copied to https://serverfault.com/questions/100141/sql-server-queries-are-really-slow-only-on-first-run due to the commentator's suggestion

+4
source share
2 answers

Most likely you like the caching effect. When you first run the query, SQL generates a execution plan and then caches it. If you run the query again, it will remember the execution plan, and in some cases you will see an increase in speed. Therefore, if you are testing a request, you need to clear the cache. That's what I'm doing.

Quote from Devx website ( DEVx tooltip )

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

NTN

+7
source

Check the SQL Server client configuration settings ... maybe TCP / IP is not the first on the list.

+1
source

More articles:


All Articles