SQL服务器查询实际上只在第一次运行缓慢 [英] SQL server queries are really slow only on first run

问题描述

有些奇怪的问题......当我重新启动我的机器启动后，我的第一次.NET应用程序时，SQL Server查询是很慢的......如果我暂停调试器，我发现它挂在得到响应从查询。这只有在连接到远程SQL Server（2008年），于发生时，如果我连接到一个在我的本地机器，它的罚款。另外，如果我重新启动应用程序，它的工作速度快，甚至关闭远程SQL服务器，随后的运行也正常。唯一的问题是，当我连接到远程SQL服务器的第一次重新启动我的机器后。更重要的是，我甚至看到了第三方应用程序（也.NET）也连接到远程SQL Server此完全相同的行为。

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.

另一块的信息...这只是开始hapenning，因为我从XP升级我的机器的Win7（64位）。此外，谁升级到Win7的其他开发人员在我的球队也看到了同样的行为（都与我们正在开发的应用程序和第三方.net应用程序）。

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).

编辑：也被复制到<一个href="http://serverfault.com/questions/100141/sql-server-queries-are-really-slow-only-on-first-run">http://serverfault.com/questions/100141/sql-server-queries-are-really-slow-only-on-first-run由于建议由一个评论者

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

推荐答案

最有可能你正在享受高速缓存的影响。当你第一次运行查询，SQL生成执行计划，然后缓存它。如果再次运行该查询，它会记住的执行计划，并在某些情况下，你看到一个速度增益。所以，如果你正在测试一个查询，你需要清除缓存。下面是我做的。

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.

从Devx网报价（ DEVx提示）

运行DBCC DROPCLEANBUFFERS，这将清除所有数据从缓存中。 然后运行DBCC FREEPROCCACHE，从而清除了存储过程的缓存。

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

心连心

这篇关于SQL服务器查询实际上只在第一次运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！