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?
An electric car built in Seattle and powered with recycled Starbuck's coffee cups.
A small marsupial native to Redmond, Washington that lives in the beards of SQL developers.
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
Comments
Anonymous
February 22, 2008
PingBack from http://www.biosensorab.org/2008/02/22/understanding-sql-express-behavior-idle-time-resource-usage-auto_close-and-user-instances/Anonymous
March 30, 2008
Thanks for this it's good to know; I thought I was simply imagining SQL Express's tendency to release memory more than it's bigger brethren.Anonymous
March 31, 2008
what's the exact timeout after which the sqlexpress goes to sleep? is that value at least configurable in some limits?Anonymous
March 31, 2008
Answer to andreipopovici: SQL Express will start paging out memory about 25 minutes after it goes idle. There is no way to configure this.
- Mike
Anonymous
March 31, 2008
thanks, mike. now, this behavior is not, in my opinion, much of a feature, but a limitation since it's not configurable... but that's a business decision and we're not going to start that discussion here. but I need a solution (I have problems, connection timeouts when the user tries to wake up the 'good citizen' etc...). what do you think, if I were to periodically 'ping' the server with some kind of NOP statement like SELECT TOP 0 NULL , every 20 minutes, would that do to keep it alive? I already use a windows service on the client desktop for some other maintenance tasks (e.g. software updates), so it wouldn't be much of a problem in terms of new development areas... TIA.Anonymous
March 31, 2008
Answer to andreipopovici: The official answer is that we have several editions of SQL Server that do not have this behavior - if it's important for you to have a server that doesn't idle in this way I would suggest SQL Server Workgroup. There have been a couple threads in the Express forum on MSDN where folks have suggested the same workaround and I believe this has worked for them - you may want to search around the forum to see if anyone provided some specifics as I don't recall. I don't typically have timeout issues caused by this idle behavior, which makes me wonder if something else is going on. It should be enough to extend the connection timeout for your application - but if making the connection from idle is not behavior fairly consitently I'd say you have something else impacting your system that is making it harder for SQL to obtain memory when it needs it.
- Mike
Anonymous
March 31, 2008
i see. the product is a desktop application and it might be deployed on machines that are not extremely comfortable on the memory side. still, the application would be an important one among user's priorities and I would prefer to keep the memory instead of freeing it and then begging every half an hour. it's got the usage pattern of say... Outlook, that is, always running and accessed periodically throughout the day, in sessions of 5-10 minutes or so. all in all, buying a 'real' sql server is not an option due to an increase of the total cost for the product and also the resource requirements on user's machine. thanks, mike.Anonymous
March 31, 2008
I agree that purchasing Workgroup isn't realistic for a desktop app. I'm still surprised you're seeing timeouts that can't be consistently solved by changing the connection time out to 60 seconds (90 at most). When I let my instance of SQL go idle and then bring it back by making a connection - it spins up within about 15 seconds. It would be interesting to start a thread about this on the forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) to see what others might contribute. It's also a better location for this type of interaction - I'd like to dig into this a bit. It's one thing if you're experiencing a 10 second delay and you just don't want to live with that - it's something totally different if you're seeing >60 seconds for SQL Express to "wake up". If it's the latter, it would be better to look at what is causing the long lag time. If you have computers that are under some type of memory constrain that is causing longer than usual lags - tying up the memory with SQL Express is not going to make your application very popular to the users.
- Mike
- Anonymous
June 29, 2008
I have a problem which is ( maybe ) related with auto_close When i'm executing a program accessing a remote database ( thru SMO from VC# 2005 ), i get an error 26. If i launch a new time the program, it executes correctly with no error ( each time, when i'm looking the ERRORLOG of the instance, i can see 2 messages :
- an incomplete request has been time-outed
- the database has gone out its idle state and been awakened Do you think that's related with auto-close ? If you prefer a thread on Sql Server Express forum, it's possible Have a nice day
Anonymous
June 30, 2008
Response to Papy: Hard to say from this if it's related to AutoClose. If the problem is reproducible 100% of the time the easiest way to check is to change the AutoClose setting on the database (use Alter Database) and then run the test. If it doesn't happen, problem solved. If the problem still occurs after changing the setting it's probably best to post the details in the forum. MikeAnonymous
April 14, 2010
After fighting with this for a while, it seems that if you detach a database from SQLExpress and then re-attach it, the AutoClose flag gets set to on again, so if you are seeing the flag getting turned on for you, this is most likely where it is coming from.Anonymous
August 25, 2010
Hi, I'm new to SQL Server and have a question on performance for SQL Server Express R2 2008. I have an application that will be developed using express R2 2008 and placed on a Terminal server. There will be multiple customers and each would have thier own database and a max of 5 users. Will a seperate instance of sql express run for each database or for each user. I'm trying to determine if express should be the way to go. Database sizes will never be more than 1 gig but it's the memory and cpu that has me worried. Thanks in advance.Anonymous
March 09, 2014
Is the idle timeout for SQL Server Express 2012 still 25 minutes? It seems to be shorter. Thank you.