5

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
flosk8
  • 465
  • 1
  • 6
  • 17
  • 1
    Can you paste an example of one of the stored procedures? I will then take a look and see if I can help. – Darren May 23 '12 at 14:58
  • Only thing you can do is either. look at the perfgormance of the queries in the stored procs, or if they've hit their practical limit, look at changing the design to side step whatever issues there are. Probably worth hiring a consultant dba for some expert assistance. – Tony Hopkinson May 23 '12 at 15:00
  • Your version of SQLServer is limited in max data size : 10 GBytes. So if you overcome the performance problem, you must cleanup the database once a couple of months or upgrade it. – t_motooka May 23 '12 at 15:19

6 Answers6

8

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

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    Updated link for the flowchart [here](https://web.archive.org/web/20081011132716/http://sqlcat.com/files/folders/280/download.aspx). – Eduardo Flores Mar 04 '21 at 09:50
2

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

TomTom
  • 61,059
  • 10
  • 88
  • 148
1

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

Diego
  • 34,802
  • 21
  • 91
  • 134
  • -1. See, his problem likely does not appear in development but in real use. Read his text. Actually you advice him to break developer licensing, OR your post makes ZERO sense as he can not use it anyway ;) – TomTom May 23 '12 at 15:09
  • I advised him to test it, on a test environment obviously, to see if the SQL Express really poses a problem. Im not suggesting him to simply change his express edition to developer. – Diego May 23 '12 at 15:28
  • Since when is Developer Edition "free"? I've always had to shell out about $49 for it. – Aaron Bertrand May 23 '12 at 21:48
  • my misconception them. I've always thought it was as long as not used in productions environments. – Diego May 23 '12 at 22:12
  • 1
    Developer Edition is free now... They changed it to free starting with SQL Server Express 2016. https://www.microsoft.com/en-us/sql-server/sql-server-editions-developers – MattSlay Oct 29 '16 at 15:31
1

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.

user1709180
  • 121
  • 1
  • 2
0

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

Harinath
  • 1
-3

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?

Community
  • 1
  • 1
Ezi
  • 2,212
  • 8
  • 33
  • 60
  • 2
    -1 SQL Express can (and is) be used in production. I'm pretty sure the OP would have exactly the same issues on Enterprise Edition, it was not proven that the performance problems he sees are due to Express limitations. Application issues (table scans!) are a far far far more likely issue. – Remus Rusanu May 23 '12 at 15:03
  • -1. You dont even bother - for smaller setups (100 users, 10gb data) it is perfectly fine to use SQL Express. Licensing allows it. READ before posting - in this case licensing documents. – TomTom May 23 '12 at 15:04
  • it seems like he will hit the limit of Express soon (4gs DB, 1 gig of RAM, and 1 CPU). personal preference, I would never use express for an app, but that is my call. if your app and data are tiny, then there should be no issue using express though. – Limey May 23 '12 at 15:12
  • sorry, 4g, is the old limit, now its 10g. – Limey May 23 '12 at 15:20
  • It depends. Account for a small 10 people company - what would you use? Even 1 gigabyte is a LOT of transactions. There are a lot of apps where this works perfectly - 2000 machiens WSUS, Firewall logging of who does how much traffic. – TomTom May 23 '12 at 17:00