All geek questions in one place

SQL Server Express Performance Issues

Initiation

I have SQL Server Express 2008 R2 running. There are ten users who constantly read / write to the same tables using stored procedures. They do it day and night.

Problem

The performance of stored procedures becomes lower and lower as the size of the database increases. A stored procedure call requires avg 10ms when the database size is about 200 MB. The same call requires avg 200ms when the database size is about 3 GB. Therefore, we must clean the database once a month.

We have already optimized indexes for some tables with positive effects, but the problem still exists.

Finally, this is not an SQL Server expert. Could you give me some tips to start getting rid of this performance problem?

+4
source share
6 answers

Limitations of SQL Server Express Edition (1 GB memory buffer pool, only one socket processor, 10 GB database size) are unlikely to be a problem. Most likely, the problem is related to application design, poor requests, excessive concurrency blocking and poor indexing. Related articles (especially the first) include a methodology for identifying bottlenecks.

+7
source

First, SQL Server Express is not the best option for your requirement. Get the Developer Edition to check it out. This is exactly the same as Enterprise, but free if you do not use "production."

About performance, there are so many things involved, and you can improve it by using indexes before splitting. We need more information to provide help.

+1
source

This is MOST, probably a simple programmer error - it looks like you are just doing:

  • Incorrect indexing on some tables. This is NOT an optimization - bad indexes are like broken HTML for web users, if you do not have an index, then basically you are not using SQL, since it should be used, you should always have the corresponding indexes.
  • Not enough hardware, such as RAM. yes, it can manage a 10 GB database, but if your hot set (still available all the time) is 2 GB, and you only have 1 GB, it WILL press the disk more often than necessary.
  • Slow disks are a particularly obvious problem because most people do not bother to get the right drive layout. Then they start the sQL database again on the slow 200 millionth IOPS drive, where - depending on the need - the SQL database wants LOTS of spindles or SSDs (a regular SSD these days has 40,000 IOPS).

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

+1
source

May be a weak index, poor database design, normalization may not apply, unwanted column indexes, poor queries that take a long time to complete.

0
source

Before optimizing your SQL queries, you need to find a query access point. You can usually use SQL Profiler to do this on SQL Server. There is no such tool for Express Edition. But you can go around several queries:

Returns the entire renct request:

SELECT * FROM sys.dm_exec_query_stats order by total_worker_time DESC;

Return only the longest 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 need to know which query needs to be optimized.

0
source

SQLExpress is built for testing, and performance is directly limited by Microsoft. If you use it in a production environment, you can obtain a license for SQL Server.

Look here for SQL Express for production?

-2
source

Source: https://habr.com/ru/post/1413964/

More articles:


All Articles