patternsqlMajor
Poor SQL Server performance when management studio closed
Viewed 0 times
studiosqlclosedpoormanagementperformancewhenserver
Problem
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
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?
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 ~650msAnd 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?
Solution
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:
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:
- Link
- http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-auto-close
- https://stackoverflow.com/questions/706732/what-is-the-auto-close-option-when-creating-a-new-database-in-sql-server-2008
Context
StackExchange Database Administrators Q#14467, answer score: 22
Revisions (0)
No revisions yet.