Experience in optimizing SQL Server databases with High Performance

This article mainly introduces the experience of correctly optimizing the SQL Server database, including the descriptions worth your attention in the actual operations on it, the most basic principles for optimizing SQL statements are described below.

Database optimization considerations:

1. Create an index for the key fields.

2. Using Stored Procedures makes SQL more flexible and efficient.

3. Back up the database and clear junk data.

4. SQL statement syntax optimization. (You can use Sybase SQL Expert. Unfortunately, I did not find the unexpired serial number)

5. Clear and delete logs.

Basic principles for SQL statement optimization:

1. Use indexes to traverse tables faster.

The index created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically stored on the data page randomly. Reasonable index design should be based on the analysis and prediction of various queries.

Generally speaking:

①. You can create a cluster index for columns with a large number of duplicate values and frequent range queries (between, >,<,>=, <=) and order by and group.

②. Multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can consider creating a composite index;

③ Composite indexes should try to overwrite key queries, and the leading column must be the most frequently used column.

2. is null and IS NOT NULL

Null cannot be used as an index. Any column containing null values will not be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved. Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.

3. IN and EXISTS

EXISTS is far more efficient than IN. It is related to full table scan and range scan. Almost all IN operator subqueries are rewritten to subqueries using EXISTS.

4. Use as few formats as possible for massive queries.

5. When SQL SERVER 2000

If a stored procedure has only one parameter and is of the OUTPUT type, you must give the parameter an initial value when calling the stored procedure. Otherwise, a call error occurs.

6. order by and GROPU

Using the 'ORDER BY' and 'group by' phrases, any index can improve SELECT performance. Note: If the index column contains a NULL value, Optimizer cannot optimize it.

7. Any operations on the column will cause the table to scan, including SQL Server database functions and computing expressions. During query, try to move the operations to the right of the equal sign.

8. IN And OR clauses usually use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.

9. SET SHOWPLAN_ALL> 10. Use the cursor with caution.

In some cases where a cursor must be used, you can consider transferring qualified data rows to a temporary table and then defining the cursor on the temporary table, which can significantly improve the performance.

Note: the so-called optimization means that the WHERE clause uses the index. If the index is not optimized, table scanning or additional overhead occurs. Experience shows that the greatest improvement in SQL Server database performance is due to the logical database design, index design, and query design. Conversely, the biggest performance problem is often caused by these deficiencies in the same aspect.

In fact, the essence of SQL optimization is to use the statements that can be identified by the optimizer on the premise that the results are correct to fully utilize the index, reducing the number of I/O scans on the table, avoid table search as much as possible. In fact, SQL Performance optimization is a complex process. The above is only a manifestation of the application layer, in-depth research will also involve the resource configuration at the SQL Server database layer, traffic control at the network layer, and the overall design of the operating system layer.