Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers

  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
share|improve this answer
    
What's up with the random T/SQL? If you have writing this for SQL 2005+ you should be using the ALTER INDEX statements instead of DBCC. –  mrdenny Jun 29 '09 at 1:02
    
Thanks for u r updates i do use on 2000 and mysql so it works for me that i thought that i can share with all serverfault ppl.. –  Rajat Jun 29 '09 at 5:41
add comment

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

share|improve this answer
    
Just read that blog posting and it says that one of the scenarios that Express was targeted towards is single-user database. It later gives an example of using Express as a multi-user database. –  John Saunders Jun 28 '09 at 19:12
    
Yes you are right I didn't mean to imply it was only a single user DB, I reworded my answer to clear up the confusion. –  Element Jun 29 '09 at 0:29
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Indexed views are not present in Standard edition either (only Enterprise and Developer) –  David Spillett Jun 26 '09 at 17:36
    
Indexed views can be created and used in all editions of SQL Server, including Express Edition. The Enterprise edition's optimizer has extra functionality - it may use the indexed view even if the view is not mentioned in the query. –  Greenstone Walker Feb 2 at 21:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.