为什么控制台应用程序受SSMS打开影响(why is a console app affected by having SSMS open)

为什么控制台应用程序受SSMS打开影响(why is a console app affected by having SSMS open)

我有一个控制台应用程序（C＃），它打开到SQL数据库的连接，执行存储过程，然后退出。 存储过程使用自己的时间（使用getdate和datediff）并将时间返回给控制台应用程序。 存储过程总是报告需要大约100毫秒才能执行。

重复运行控制台应用程序会提供一致的时间设置（包括ExecuteReader命令的300 ms）

然而，我偶然发现并可以可靠重现的是以下效果：如果我打开SSMS并连接到数据库，然后运行控制台应用两次，控制台应用中的ExecuteReader第二次显着更快。

请注意，您不必在SSMS中运行或打开存储过程，只需连接到数据库即可

例如，控制台应用程序的第二次运行受到严重影响，并且通过将SSMS打开并连接到同一数据库而得到改善

ExecuteReader when SSMS is not open 300 ms ExecuteReader when SSMS is not open 300 ms ExecuteReader when SSMS is not open 300 ms Open SSMS and connect to database First ExecuteReader when SSMS is open and connected to same database 300 ms Second ExecuteReader with SSMS open and connected 10 ms !!! Third ExecuteReader with SSMS open and connected 10 ms Fourth ExecuteReader with SSMS open and connected 10 ms Close SSMS ExecuteReader back to reporting 300 ms to execute

换句话说，为ExecuteReader报告的时间少于存储过程运行的时间

请注意，存储过程始终需要运行相同的时间量。

看起来就像SSMS有一种允许控制台应用程序使用的缓存。

任何人都可以对此有所了解吗？ sys.dm_exec_connections显示所有各种连接之间没有差异

SSMS是v17.3，连接到sql server 2008 R2 SP2数据库

I have a console app (c#) which opens a connection to a sql database, executes a stored procedure and then exits. The stored procedure times itself (using getdate and datediff) and returns the timings to the console app. The stored procedure always reports taking about 100 milliseconds to execute.

Running the console app repeatedly gives a consistent set of timings (including 300 ms for the ExecuteReader command)

However what I discovered by accident and can reliably reproduce is the following effect: If I open SSMS and connect to the database, then run the console app twice, the ExecuteReader in the console app is significantly faster the second time.

Note you don't have to run or even open the stored procedure in SSMS, you simply have to connect to the database

The second run of the console app is being significantly affected and indeed improved by having SSMS open and connected to the same database For example

ExecuteReader when SSMS is not open 300 ms ExecuteReader when SSMS is not open 300 ms ExecuteReader when SSMS is not open 300 ms Open SSMS and connect to database First ExecuteReader when SSMS is open and connected to same database 300 ms Second ExecuteReader with SSMS open and connected 10 ms !!! Third ExecuteReader with SSMS open and connected 10 ms Fourth ExecuteReader with SSMS open and connected 10 ms Close SSMS ExecuteReader back to reporting 300 ms to execute

In other words the time reported for ExecuteReader is less than the amount of time the stored procedure takes to run

Note the stored procedure always takes the same amount of time to run.

It seems almost like SSMS has a kind of cache that the console app is allowed to use.

Can anyone shed any light on this? sys.dm_exec_connections shows no differences between all the various connections

The SSMS is v17.3 connecting to a sql server 2008 R2 SP2 database

最满意答案

请确保您没有为您的数据库设置AUTO_CLOSE 。 根据聊天中的讨论，似乎这是设置为ON。

从微软引用

[当] AUTO_CLOSE设置为ON时，由于在每次连接之后打开和关闭数据库的开销增加，因此它会导致频繁访问的数据库性能下降。 每次连接后，AUTO_CLOSE也会刷新过程缓存。

最佳实践建议

如果频繁访问数据库，请将数据库的AUTO_CLOSE选项设置为OFF。

您可以通过运行以下查询来关闭AUTO_CLOSE （如Dan Guzman所建议的）：

ALTER DATABASE YourDB SET AUTO_CLOSE OFF;

Do make sure that you do not have AUTO_CLOSE set for your database. According to the discussion in chat, it does seem that this is set to ON.

Quoted from Microsoft

[When] AUTO_CLOSE is set ON [it] can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

Best Practices Recommendations

If a database is accessed frequently, set the AUTO_CLOSE option to OFF for the database.

You can turn of AUTO_CLOSE by running the following query (as suggested by Dan Guzman):

ALTER DATABASE YourDB SET AUTO_CLOSE OFF;