why is a console app affected by having SSMS open

Question

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

Answer 1

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;