re: AUTO_CLOSE and performance issue in a .NET application

Sakthivel Chidambaram — Tue, 08 May 2012 10:46:03 GMT

Hi Papy,

Thanks for your feedback and comments!

//Sakthi

re: AUTO_CLOSE and performance issue in a .NET application

Papy Normand — Tue, 08 May 2012 10:31:56 GMT

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