CODEWITHSUNDEEP
performancesql-server-2008r2-express
flosk8
flosk8

Reputation: 575

SQL Server Express performance problems

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?

Upvotes: 6

Views: 39383

Answers (6)

user1709180
user1709180

Reputation: 121

Before Optimizing your SQL queries, you need to find the hotspot of the queries. Usually you can use SQL Profiler to do this on SQL Server. For Express edition, there's no such tool. But you can walk around by using a few queries:

Return all renct query:

SELECT *
FROM sys.dm_exec_query_stats order by total_worker_time DESC;

Return only top time consuming queries:

SELECT total_worker_time, execution_count, last_worker_time, dest.TEXT
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY total_worker_time DESC;

Now you should know which query needs to be optimized.

Upvotes: 1

Harinath
Harinath

Reputation: 1

May be poor indexes,Poor design of database, may not apply normalization,unwanted column indexes,poor queries which take much time to execute.

Upvotes: 0

TomTom
TomTom

Reputation: 62159

This is MOST likely simple a programmer mistake - sounds like you simply do either have:

  • Non proper indexing on some tables. THis is NOT optimization - bad indices is like broken HTML for web people, if you have no index then basically you are not using SQL as it is supposed to be used, you should always have proper indexes.
  • Not enough hardware, such as RAM. yes, it can manage a 10gb database, but if your hot set (the suff accessed all the time) is 2gb and you have only 1gb it WILL hit disc more often than it needs.
  • Slow discs, particularly a express problem because most people do not bother to get a proper disc layout. THen they run a sQL database againnst a slow 200 IOPS end user disc where - depending on need - a SQL database wants MANY spindles or an SSD (typical SSD these days has 40.000 IOPS).

That is it at the end - plus possibly really bad SQL. Typical filter error: somefomula(field) LIKE value, which means "forget your index, please, make a table scan and calculate someformula(field) before checking".

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294487

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

Upvotes: 9

Diego
Diego

Reputation: 36176

First, SQL Server Express is not the best edition to your requierement. Get a Developer's Edition to test it. Its exactly like the Enterprise but free if you dont use on "production".

About the performance, there are so many things involved here, and you can improve it using, since indexes until partitioning. We need more info to provide help

Upvotes: 1

Ezi
Ezi

Reputation: 2210

SQLExpress is built for testing purposes and the performance is directly limited by Microsoft, If you use it in a production environment you may want to get a license for SQL Server.

Have a look here SQL Express for production?

Upvotes: -3

Related Questions