SQL Server Express performance problems

performance sql-server-2008r2-express
flosk8 picture flosk8 · May 23, 2012 · Viewed 33k times · Source

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

Remus Rusanu picture Remus Rusanu · May 23, 2012

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

Related questions

Which "href" value should I use for JavaScript links, "#" or "javascript:void(0)"?

The following are two methods of building a link that has the sole purpose of running JavaScript code. Which is better, in terms of functionality, page load speed, validation purposes, etc.? or function myJsFunc() { alert("myJsFunc"); } <a href="javascript:…

Read More
Fastest way to check if a value exists in a list

What is the fastest way to know if a value exists in a list (a list with millions of values in it) and what its index is? I know that all values in the list are unique as in this …

Read More
How to measure elapsed time in Python?

What I want is to start counting time somewhere in my code and then get the passed time, to measure the time it took to execute few function. I think I'm using the timeit module wrong, but the docs are …

Read More