I’m working the forums this week and I’ve already seen several questions asking if SQL Server Express Edition is up to the task. Shown below are the Microsoft links to Express’ limitations but I would like to clarify just what the mean (and can’t say).
Back in my Microsoft days, I was an (unofficial) PM for MSDE--the predecessor for SQL Express. I'll tell you what I have been telling folks for decades. It's not usually the horse that determines the speed, it's the rider. If your applications are built correctly, you'll have no trouble supporting dozens to hundreds of users on a fairly simple SQL Server Express server. Today’s versions of SQL Server all share the same binaries (except for the Compact Edition) so they all work with the same applications. However, the SQL Express edition limits two important performance-enhancing benefits: RAM cache and parallel processing. Instead of letting you cache a big hunk of the database pages currently being accessed in RAM you’re limited to 1GB. In addition, instead of using several threads (processors) to fetch the data all at the same time, you’re limited to a single thread. But do these limitations really mean you can’t use SQL Server Express to support a small business? IMHO, it does not.
Remember that what makes SQL Server fast is the amount of work it has to do (duhhh).
- If it has to constantly hit the hard disk for data, it will be slower. The solution: add more RAM to cache the rows. In SQL Express this is going to be limited to 1GB.
- Make sure that nothing else is running on the server--nothing (not even a print server, reporting services, exchange, IIS or even Castle Wolfenstein screen-saver). This means that the processor is not being distracted by other tasks and can focus on pulling your SQL Server wagon with all of it’s might.
- Make sure that the client queries are well-written and return just the rows they need at the time. Fetching too many rows flushes the (limited) cache.
- Don’t put pictures or documents in the database. One big picture can flush the entire RAM cache. Put BLOBs in files and put the paths in the DB.
- Make sure that the database indexes are well designed and USED. If you fetch too many rows the indexes won't be used at all. Learn how to code and maintain indexes. Too many are just as bad as too few. Indexes can help focused queries but slow down updates.
- Having a dual or quad-core (or better) processor will help the OS (Windows) stay out of the way. While SQL Express won't use the other processors to parallel process, the rest of the system will.
- Pre-allocate the database to the size you expect it to be two years (or more) from now. Do the same for Model (which is used to create Temp) to eliminate the need to constantly stretch Temp during the day.
Most of these tips and more are in my book... hth
Come to my next Progressive webinar (“Visual Studio, SQL Server and Reporting Services”) where we discuss these issues at length.
For all the official details see http://www.microsoft.com/sqlserver/en/us/editions/express.aspx and http://www.microsoft.com/express/Database/.