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?
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...
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.
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.
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
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.
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 