c# - ASP.NET MVC Debugging Very Slow - SQL Server Express Connection Issue?

Question

I am developing an application in ASP.NET MVC, using SQL Server Express as the backend and Cassini as the development web server (the one that comes with Visual Studio 2008).

The application performance is blazingly fast (nearly instantaneous page switches). However, spinning up the debugger is painfully slow; it takes about 30 seconds from the time I hit F5 to the time the ASP.NET welcome page comes up.

I have noticed a similar delay when loading SQL Server Management Studio Express, and another delay when I open a table in my database for the first time for viewing. After I open my first table, everything goes smoothly.

Given the behavior of SQL Server Management Studio Express, I suspect that the problem lies in making the initial connection to SQL Server Express. Is this really where the problem is, and if so, how can I fix it?

Answer 1

I'd check the auto_close property on the database.

sp_dboption 'MyDatabaseName', 'autoclose'

I think the default on express might be to set autoclose to on. When that is set to TRUE, the server will close the database and free all resources from it when there are no users in the database. Setting autoclose to FALSE will tell the server to hang on to the database so that it's in the ready state regardless of users being in the database or not.

See here for more info.