Collect and organize SQL Server database optimization experience and precautions

Collect and organize SQL Server database optimization experience and precautions

There are many tutorials on SQL Optimization on the Internet, but they are messy. I have made some preparations recently, and I will share some of them with you. There are some errors and deficiencies. Please correct them.

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, >,<<=, <=), order by, and group; ②. multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can create a composite index. composite indexes should try to overwrite key queries. 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. In SQL SERVER 2000, if a stored procedure has only one parameter and is of the OUTPUT type, an initial value must be provided to the stored procedure when it is called, 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 operation on a column will cause a table scan, including database functions and calculation expressions. During query, try to move the operation 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 ON to view the execution plan. DBCC checks database data integrity. DBCC (DataBase Consistency Checker) is a set of programs used to verify the integrity of the SQL Server DataBase.

10. Use cursors 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 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 resource configuration at the database layer, traffic control at the network layer, and the overall design of the operating system layer.

The above is a full description of the daily collection and collation of SQL Server database optimization experience and precautions. I hope you will like it.