SqlServer Express slow performance
I am stress testing 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.
How can we improve our application's performance?
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.
You may be running into concurrency issues, depending on how your application runs. Try performing your reads with the "nolock" keyword.
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.
If you aren't already, move to SPROCs, this allows MSSQL to index your data better for a given query's normal result set.
Try following the execution plan of your SPROCS to ensure they are using the indexes you think they are.
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.)