SQL Server Express performance problems

Question

Initiation

I have a SQL Server Express 2008 R2 running. There are ten users who read / write permanently to the same tables using Stored Procedures. They do this day and night.

Problem

The performance of the Stored Procedures is getting lower and lower with increasing database size. A Stored Procedure call needs avg 10ms when the database size is about 200MB. The same call needs avg 200ms when the database size is about 3GB. So we have to cleanup the database once a month.

We already did index optimization for some tables with positive effects but the problem still exists.

Finally im not a SQL Server expert. Could you give me some hints to start getting rid of this performance problem?

Answer 1

• Download and read Waits and Queues
• Download and follow the Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart
• Read Troubleshooting Performance Problems in SQL Server 2005

The SQL Server Express Edition limitations (1GB memory buffer pool, only one socket CPU used, 10GB database size) are unlikely to be the issue. Application design, bad queries, excessive locking concurrency and poor indexing are more likely to be the problem. The linked articles (specially the first one) include methodology on how to identify the bottleneck(s).