One of the interesting scenario I worked recently.
.NET application which has multiple tabs showing employee details shows poor performance when run for the first time during anytime of the day but when we connect to SQL Server Management Studio and run any ALTER COMMAND, then switch back to application, performance is as expected.
This .NET application is used by several customers and within each customer environment, the number of users accessing the application is < 10. These 10 users also access the application infrequently.
As a good programmer, the developer who wrote the application, opened a connection, queried the database and closed the connection immediately.
There are multiple tabs in the application to select the employee name like Tab 1 for employee names starting with A-C, Tab 2 for employee names starting with D-F.
The target database against which the queries are executed has AUTO_CLOSE turned ON. As per http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx the moment last user exits, database is shutdown (auto closed) within 300 ms.
So when a user launches the application, opens a tab, connection is made to SQL Server and it is closed immediately after reading the data. If this user is the only user and he takes more than 300 ms to switch to next tab, database need to re-opened and that's how performance is down.
So what happens when we run any ALTER command which makes performance look good? It is because a connection is currently open in the management studio so the database cannot be shutdown.
There is no message printed in SQL Server Errorlog when the database is shutdown. Simple way to track whether the database is already shutdown is to use the T-SQL command:
SELECT name, collation_name FROM sys.databases
Collation Name for a database will be NULL if it is already shutdown. So from the query given above, a database with NULL in the collation name is currently auto closed.
Solution in this case could be turn off AUTO_CLOSE using the command given below or any other programmic logic to keep connection active (with sleeping state) till the user exits application:
ALTER DATABASE [Database] SET AUTO_CLOSE OFF WITH NO_WAIT
Sakthivel Chidambaram, SQL Server Support
AUTO_CLOSE is set to OFF for every edition of SQL Server except for the Express ones for which the default value is ON
You have not forgotten to recall the article of Mike Wachal with his explanation about the 300 ms.
in msdn.microsoft.com/.../bb522682.aspx , i have discovered :
"When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2 and higher, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval"
In the same page , i have discovered "Database mirroring requires AUTO_CLOSE OFF"
You have learnt me something useful : the possibility to use WITH NO_WAIT . Nice and precious.Thanks
An excellent article written in a simple way ( so really easy to understand ). I regreat that it is not possible to give a 6 starts to it
Thanks for your feedback and comments!