Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances

I've gotten a number of questions recently asking how SQL Express uses resources during idle time, and how that behavior impacts the way SQL Express behaves "on first connection" after being idle for a period of time. While many of these questions have been related to web hosting, the behavior is a general behavior for SQL Express, so I'll answer in general terms.

SQL Express: It's SQL Server with a twist

SQL Express is SQL Server. This may seem an odd thing to say, but many people get confused on this point, so I'll say it again: SQL Express is SQL Server. We have introduced some scale limitations to SQL Express and removed some features from SQL Express, but it is the same code base as all the other SQL Server editions. There are some specific places where we have changed the way SQL Express behaves compared to other editions and one case where we've introduced a feature that is unique in SQL Express (more on that later). I won't go into the details of which features are in which editions, you can find that information on the Microsoft web site: SQL Server 2005 Feature Comparison. This post is about those behavioral differences that lie just beneath the surface.

Idle thoughts about Idle time memory usage

SQL Server, in general, tries to return query results fast. This makes sense in terms of being a centralized, multi-user data service. When there are a whole bunch of users simultaneously asking questions, it's good to be able to answer those questions fast. One of the many ways SQL Server manages to do this is by caching frequently used information in memory. (That's as technical I'll get, you'll have to look elsewhere for a deep, technical discussion of the ins and outs of memory usage in SQL Server.) With SQL Express we wanted to target a different scenario, specifically, we wanted to support being a data store for single-user applications. This presents a different set of challenges, for one thing, there are long periods of time (call it idle time) when no one is asking any questions. The idea of holding a whole bunch of data in memory doesn't make as much sense if no one is going to be using it, and it might even be bad, because there may be other ways the computer could use that memory. So we made a change in the way SQL Express behaves:

  • When SQL Express is active it works just like any instance of SQL Server, data is cached in memory in order to improve the performance of subsequent queries.

  • When SQL Express is idle it aggressively trims back the working memory set by writing the cached data back to disk and releasing the memory. This frees up memory for other applications to use.

    This behavior makes sense for a single user database engine - it supports higher performance when the application using the data is running, but once that application is shut down, memory is released to allow other applications to use it. I like to think of this as "being a good citizen" in the application community. There is always a catch when making this kind of trade-off though, and SQL Express is no different in this aspect. When SQL Express transitions from idle to active some of the memory that was released needs to be reclaimed; this results is a slight lag during "startup time" when you're first connecting to SQL Express. You can actually see this happening in the Widows Application Log, when you first activate SQL Express after some idle time, an entry is written to the log that reads like this:

    Server resumed execution after being idle 16056 seconds: user activity awakened the server. This is an informational message only. No user action is required.

    This is just telling you that SQL Express was idle and is now awake because some user activity woke it up. (When you think of it, it's kind of rude for users to keep waking up SQL Express without consideration for SQL Express's feelings. Maybe it's had a rough day and needs some rest!) This behavior is not configurable, it's just the way SQL Express is written. As mentioned above, this behavior change is unique to SQL Express, if you have need for your SQL Server to stay awake, SQL Workgroup or higher would be a more appropriate choice for your database engine.

    This (data)store will AUTO_CLOSE in 300 milliseconds

    Another minor change in SQL Express that has a lesser impact on perceived performance is the way the AUTO_CLOSE property of a database is handled. The AUTO_CLOSE property allows a database to be managed more like any other file in Windows by releasing the lock that SQL Server would normally hold on the file and allowing it to be copied, backed up, etc. This type of behavior is very important to supporting XCopy type deployment, which is a core piece of functionality needed to support Visual Studio ClickOnceâ„¢ Deployment. Supporting ClickOnce is something we wanted to do with SQL Express, so we modified the behavior of the CREATE DATABASE functionality in SQL Express in order to set AUTO_CLOSE to True for all databases. The results of this is that after 300 ms of inactivity for a database, SQL Express will close the database and release the lock on the file. It's important to recognize that Close and Detach are two different things. SQL Express still maintains metadata information about the closed databases, we just don't lock the file. When a request comes in that requires the use of a Closed database, we Open the database using the information stored in metadata. Opening a database that has been closed does not have a significant performance impact, but it can have some interesting side effects (and by interesting I mean negative) for certain kinds of operations:

  • If you are running an iterative process that includes a cross-database query and the period of the iteration exceeds 300 ms, the external database your process access would go through a Close/Open cycle during every iteration. This can add up over a large number of iterations and become a large component of the process.

  • If you have a process that polls a database at intervals, and that interval is larger than 300 ms, the database will go through a Close/Open cycle with every poll. In this case, the direct performance impact is negligible, but the fact that SQL Server writes an entry to the Windows Application Log every time a database is opened can result in your log filling up unexpectedly. (One example of this is if you have installed Reporting Services Express; it polls one of it's databases every couple of minutes.)

    Happily, you have some flexibility here to address the behavior within SQL Express. The behavior of CREATE DATABASE in SQL Express is not configurable, it will always set AUTO_CLOSE to True, but AUTO_CLOSE is just another database property, so you can change that property once the database has been created. Read the BOL topic ALTER DATABASE (Transact-SQL) for more information about changing the AUTO_CLOSE property programmatically. You can also change this property using SMO and in the Database Properties dialog in management studio. (Interesting Trivia: When you create a database in management studio, there is some post-creation processing that happens to set the database properties to match what ever is in the model database. Since all system database have AUTO_CLOSE set to False, even in SQL Express, this results in databases created in management studio to have AUTO_CLOSE set to False, even for SQL Express, unless you've changed the properties on your model database.)

    Net/Net - If you're using SQL Express as a standard multi-user server, you'll probably want to consider changing the AUTO_CLOSE property of your databases to False after you create them.

    What is a RANU?

  1. An electric car built in Seattle and powered with recycled Starbuck's coffee cups.

  2. A small marsupial native to Redmond, Washington that lives in the beards of SQL developers.

  3. A user specific process of SQL Express designed to allow non-administrative users to use SQL Server as the data store for Windows Forms based applications.

    As much as I wish the answer were either 1 or 2, it is actually 3. RANU stands for Run As Normal User and is a feature that is unique to SQL Express. You've probably heard this feature referred to as User Instances. I'm not going to go into a detailed, technical description of User Instances, but will rather point you to the white paper on the topic, SQL Server 2005 Express Edition User Instances. I'll stick with the theme for this post and just discuss how RANU behavior impacts perceived performance and resource usage.

    If you didn't read the white paper (feel free to do so now, I'll wait) it is important to understand that RANU is a separate process of SQL Express from the parent instance and that it has some additional limitations beyond SQL Express:

  • RANU supports only local connections via Shared Memory. (i.e. It's designed for single-user applications.)

  • RANU supports only Windows authentication.

  • RANU is a user specific instance, each user gets their own RANU instance that is not shared with other users. (i.e. The databases can not be shared between RANU instances.)

    There are three basic issues to be aware of when consider how RANU impacts resources and perceived performance:

  • Since RANU is starting a separate user Process of SQL Express when an application launches, you have multiple copies of SQL Express running and using system resources. Most commonly this means there are two instances running, the parent instance and the RANU instance, for a computer with one user. (This could change, for example, on Terminal Services where you can have more than one user of the computer simultaneously.) RANU is tied to the User, not to the application, so while an application starting can result in a RANU instance being started, it does not shutdown the RANU instance automatically when the application closes. A RANU instance has a defined timeout value of 60 minutes; after 60 minutes without activity, RANU shuts itself down. The RANU timeout is configurable at the parent instances through the 'User Instance Timeout' setting.

  • The first time you start RANU for a specific user, SQL Express has to make copies of all the system database that will be used by the RANU instance. Making copies of these files can take longer than the default connection timeout value (30 seconds if you're wondering) and result in a timeout error. Behind the scenes, the file copy will finish and the RANU instance will starte, it's only the connection that fails. If you try the connection again, it will succeed. I find that changing the connection timeout to 60 seconds in your RANU connection strings handles this "first use" delay. (Your mileage may vary.)

  • On all subsequent connections to RANU for the same user (see #2 for the first time connection issues), there are two states, RANU may already be running having been started previously and not timed out yet or RANU may not be running, in which case it must be started. If RANU is not running, there is a short delay while the instances is started. I've not found this delay to be large enough to be noticeable within the context of other application load operations, and have rarely seen problems with the connection failing because of the lag, other claim there is a delay. Again, your mileage may vary, but changing the connection timeout to 60 seconds in RANU connection strings usually ensures a successful connection. The "start up lag" is part of the territory with RANU. You can consider the trade-offs of setting the User Instance Timeout to a longer period, thus reducing the number of occurrences of "start up lag", with the viability of keeping the dormant RANU instances running when it's not being used. (Note: RANU will trim it's memory usage when idle just like the parent instance of SQL Express, so you're really splitting hairs at this point.

    That's quite enough for now, it's been awhile since I've posted anything and I guess I got carried away.

    - Mike Wachal
    - SQL Express team