Posterous theme by Cory Watilo

Evil, thy name is AUTO_CLOSE

Okay, perhaps that’s overstating the case a bit, but on the other hand, perhaps not.

AUTO_CLOSE is the feature in SQL which will “close” a database (ie, clear the cache, write everything to disk, release the file lock, etc, etc) some 300ms after the last connection to the database is closed.  It is turned off by default with all editions of SQL except for SQL Express, which forces it to on.  This is ostensibly to ease XCopy deployment, according to MS.

A customer called with performance problems.  We use SQL Server 2005 Express Edition as part of our product, and we are constantly making single-use, non-overlapping connections to the database.  In this customer’s case, every time a new connection was made to the SQL Server, it would sit and chew on the CPU for a good couple minutes as it was processing.  This is not the normal behavior we expect to see.

So we looked at the usual things.  The two most notable things about his setup, aside from having a database on the large end of things, were that he had a configured memory constraint on the database at around 100MB and that there were a lot of Application Event logs saying “The server resumed execution after being idle %d seconds. This is an informational message only. No user action is required.”  These were accompanied by reports of the clearance of the cachestore.

As far as the memory constraint goes, we had set that manually at some time earlier.  Since the client had increased the system memory in the interim, I put it back at the default, which is somewhere around 2TB.  This is SQL Express, so it still has a 1GB memory limit coded into it somewhere else.  While this improved performance, it was only about a fifth faster.

I was also already familiar with the Application Event log message, although I was puzzled as to why it was there.  We encountered AUTO_CLOSE when we first started using SQL Express, since only SQL Express sets AUTO_CLOSE to True.  Our customers had been chewing our ears off about their log files filling up with useless messages (why, if the message states “No user action is required”, can you not simply disable that log message?).  We dutifully relayed the fact that it was a “harmless” message, but for this reason alone we began configuring the database to not AUTO_CLOSE.  As we find out now, that is not the most important reason to turn off AUTO_CLOSE, by any means.

So there were three puzzles here.  Why were those messages occurring if we don’t configure AUTO_CLOSE, why are there cachestore messages, and are either of them related to the performance problem?

The cachestore messages are the easiest to clear up.  They are a new, related message spit out by the same event, added with SQL SP2.  So that’s where they are coming from.  So at least MS is now telling us there is more to the picture than “No user action is required”.  Harmless?  Apparently they are now telling us there is a cost associated with the automatic closing of the database.

Further research showed that there are two “naughty” settings for performance with SQL Server.  AUTO_CLOSE and AUTO_SHRINK.  Technet states that best practice is to turn these both off.  So Express doesn’t follow best practices.  AUTO_CLOSE is even slated to be removed entirely from SQL server in the future!

As I mentioned, we knew that already, although we didn’t know the associated performance issues.  Now we knew that as well.  But if we didn’t have AUTO_CLOSE on, why were we seeing these things?

It’s a combination of things.  First of all, there is apparently no way to force the SQL Express instance, as a whole, to default that setting to off.  If there were, you could set that and be done with it, at least until you installed a new instance.  Then you’d need to set it off there as well.

Or, if the setting were a permanent part of the database, you could set it there and be done with it.  Microsoft’s documentation would lead you to believe that this is the answer.  In fact, this is the only way to set the attribute, on the database itself.

So that’s what we do.  However, there is a detail.  Apparently this setting isn’t actually set within the database, it is set somewhere more ephemeral.  You can tell because if you detach the database and reattach it, that setting is lost and defaults to True again!  We actually do that procedure quite a bit because we troubleshoot not by operating on live customer databases but rather by following Microsoft’s stated instructions on moving databases between instances, which is to detach and reattach.  There are other processes which require this as well, such as moving the database between customer servers.

Now, if the setting were part of the db, that wouldn’t be a problem.  But apparently it isn’t, because the re-attach resets the AUTO_CLOSE attribute to TRUE.  Blech.

So the answer is to reset this attribute every time we do the kind of support that requires us to copy the database.  It also means we have a lot of customers out there who have unwittingly had that setting changed by our troubleshooting procedures.  Thanks MS!  Sorry for the sarcasm, this is just one particularly tricky set of circumstances that don’t seem to be well thought out.  I am not above that either.

Fortunately or unfortunately, the result with the customer is that they did not see an enormous increase in speed with this change, so it apparently isn’t the major culprit I thought it might be in the first place.  Still, it would be nice for SQL Express to have a better story on this.