Performance of MSSQL Server Express Edition

Question

Our Company has several SQL Server Enviornments, some of them work with MS-SQL Server 2005 Express Edition others with Standard or Enterprise Edition.

In a discussion today a DBA said MSSQL Express Ed. would be less performant then the Standard Edition.

Well I know there are the following limits: Server does use only 1GB of memory Server does use only 1 CPU

but I don't know that there are other limitations. What is your experience there are other possible performance problems?

Answer 1

One correction to the previous answers - SQL Server Express supports a single CPU socket only - but if the socket has multiple cores, Express will use them and you'll get parallelism. Little known fact...

Answer 2

There aren't any "artificial" limitations on standard queries - ie, "select * from tableFoo" won't run slower aside from the obvious cases where >1 CPU cores would be of benefit.

There are some other missing features that boost performance in certain cases, such as indexed views, fulltext indexing, etc.

Like most questions, it really depends on the exact application(s). If your application benefits from any of the features missing from the Express Edition (see Microsoft's comparison grid: http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx ) then yeah performance will suffer. If not, if not.

I believe your next step (if need dictates, and office politics and/or interpersonal relationships permit) is to print that chart out and ask your DBA to detail which specific missing features will present problems.

Answer 3

SQL Server Express will not run slower than the other editions other than the fact that it only supports 1 CPU and 1 Gig of RAM.

SQL Server 2000 MSDE only supported 5 concurrent statements being run, and anything else would have to wait until a free slot showed up, but that was removed in SQL 2005 Express edition.

Answer 4

One problem with SQL Express is it will spool down after ~15mins of idle time and free its cache. This will cause a lag when users start hitting it again. Depending on your usage patterns this can be a problem. You can read about the details here: http://blogs.msdn.com/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

Answer 5

Major limitation other than CPU and RAM use which you already identified is that the total of any given databases datafiles can be no larger then 4Gb in size. The total total for a DB can be larger than this as transaction log files do not count against the 4Gb.

In MSDE2000 (the SQL 200 equivalent of Express Edition) the size limit was 2Gb.

Answer 6

1. reindex
2. truncate
3. shrink

for reindexing

USE {DB NAME}

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN 
if @tablename='DUAL' or @tablename='IMPORT_CONTROL_TRN'
    begin
    print 'skipping ' +@tablename
    FETCH NEXT FROM TableCursor INTO @TableName
    continue
    end
else
    begin
    if @TableName='calltrak'
      begin
      print 'reindexing calltrak'
      DBCC DBREINDEX(@TableName,' ',50)
    end
    else
    begin
      print 're-indexing ' + @TableName
      DBCC DBREINDEX(@TableName,' ',70)
    end
    end
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
exec sp_updatestats