Sql-server – Poor SQL Server performance when management studio closed

sql serverssms

I've noticed a strange behaviour of SQL Server 2008 r2 express:

usually my query execution time is ~650ms, but if I open Management Studio and run any simple query (for example SELECT * FROM Something), the execution time becomes ~40ms. If I close management studio this value returns to normal ~650ms

And no matter where the query was executed: from Management Studio or my application (it uses ADO.Net)

ARITHABORT setting has no effect

Why is this happening?

Best Answer

  • The database you are querying probably has "auto close" enabled.

    The connection from SSMS stops the database closing. When you close SSMS, the database closes. A subsequent call requires it to be opened.

    Auto close is the default for SQL Server Express and is not a good idea: