SqlServer Express slow performance

Question

Yesterday, I did a stress test for a .NET web application. I did this for 2 reasons, I wanted to see what performance was like under real world conditions and also to make sure we hadn't missed any problems during testing. We had 30 concurrent users in the application using it as they would during the normal course of their jobs. Most users had multiple windows of the application open.

• 10 Users: Not bad
• 20 Users: Slowing down
• 30 Users: Very, very slow but no timeouts

It was loaded on the production server. It is a virtual server with a 2.66G Hz Xeon processor and 2 GB of RAM. We are using Win2K3 SP2. We have .NET 1.1 and 2.0 loaded and are using SQLExpress SP1.

We rechecked the indexes on all of the tables afterword and they were all as they should be.

We are supposed to launch this at the end of this month, I could sure use some help on improving performance.

Thanks Scott and the Dev Team

Answer 1

1. You may be running into concurrency issues, depending on how your application runs. Try performing your reads with the "nolock" keyword.

2. Try adding in table aliases for your columns (and avoid the use of SELECT *), this helps out MSSQL, as it doesn't have to "guess" which table the columns come from.

3. If you aren't already, move to SPROCs, this allows MSSQL to index your data better for a given query's normal result set.

4. Try following the execution plan of your SPROCS to ensure they are using the indexes you think they are.

5. Run a trace against your database to see what the incoming requests look like. You may notice a particular SPROC is being run over and over: generally a good sign to cache the responses on the client if possible. (lookup lists, etc.)

Answer 2

Update: Looks like SQL Server express is not the problem as they were using the same product in previous version of the application. I think your next step is in identifying the bottlenecks. If you are sure it is in the database layer, I would recommend taking a profiler trace and bringing down the execution time of the most expensive queries.

This is another link I use for collecting statistics from SQL Server Dynamic Management Views (DMVs) and related Dynamic Management Functions (DMFs). Not sure if we can use in the Express edition. Uncover Hidden Data to Optimize Application Performance.

---

Are you using SQL Server Express for a web app? As far as I know, it has some limitations for production deployment.

SQL Server Express is free and can be redistributed by ISV's (subject to agreement). SQL Server Express is ideal for learning and building desktop and small server applications. This edition is the best choice for independent software vendors, non-professional developers, and hobbyists building client applications. If you need more advanced database features, SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.

Answer 3

This is just something that I thought of, but check to see how much memory SQL Server is using when you have 20+ users - one of the limitations of the Express version is that it is limited to 1GB of RAM. So it might just be a simple matter of there not being enough memory available to to server due to the limitations of Express.

Answer 4

I would check disk performance on the virtual server. If that's one of the issues, I would recommend putting the database on a separate spindle.

Update: Move to separate spindle or Upgrade SQL Server version as Gulzar aptly suggests.

Answer 5

make sure you close connections after retrieving data.

Answer 6

Run SQL Profiler to see the queries sent to the database. Look for queries that are:

• returning too much data
• constructed poorly
• are being executed too many times