SQL Server Speed

[3fonov]

What can cause slowness of SQL Server 2005 Enterprise? When start same DB on Enterprise and Express, express are mach more faster (100 times) Thanks for advises.

[Mike Dimmick]

Typically your databases on Express are set to the Simple recovery model. This keeps only the transaction logs required to roll back active transactions. It rapidly overwrites the log records once the corresponding transaction is committed. In contrast databases on the true server editions of SQL Server default to the Full recovery model, which logs everything that's done and keeps log records until the log is backed up, in order that recovery can occur to the point of failure if the data files are lost (and you restore a full backup). If you're not backing up the transaction log through a SQL Server-aware product, they simply keep growing. This may well take longer to scan on opening a database. Also, SQL Server Express Edition only actually opens databases and performing the roll forward of logged committed operations and rollback of logged uncommitted operations when that database is accessed (this is the 'auto close' option). The full editions of SQL Server open and recover all database at service startup. The time to perform recovery is also affected by the disk subsystem you're running the process on - it will be directly proportional to the read speed of the disks. If you're running on RAID 5, check that you haven't got a failed disk - your read speed will have gone through the floor. You will also get a complete failure if another drive fails.

Stability. What an interesting concept. -- Chris Maunder