A summary of high performance optimizations for SQL Server databases _mssql

This article is to introduce you to the correct optimization of the SQL Server database experience, including in the actual operation of its optimization should be noted in the local description, as well as the most basic principles of the Optimization of SQL statements, the following is the main content of the article description.

Considerations for optimizing your database:

1, key fields to establish the index.

2. Using stored procedures, it makes SQL more flexible and efficient.

3, backup the database and clean up the garbage data.

4, the SQL statement syntax optimization. (You can use Sybase's SQL Expert, but I didn't find the unexpired serial number)

5, clean up the deletion log.

Basic principles of SQL statement optimization:

1, use the index to traverse the table more quickly.

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

Generally speaking:

①. You can consider setting up a clustered index if you have a large number of duplicate values and often have a range query (between, >,<,>=,< =) and a column that occurs by the group by.

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

③. Combined index to maximize the indexing of critical queries, the leading columns must be the most frequently used columns.

2, is null and is not NULL

cannot be indexed with NULL, and any column that contains null values will not be included in the index. Even if there are multiple columns in the index, the column is excluded from the index as long as one of the columns contains null. This means that if a column has a null value, even indexing the column does not improve performance. Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

3, in and exists

exists is far more efficient than in. The inside relates to the full table scan and range scan. Almost all of the in-operation Subcode queries are rewritten as subqueries using exists.

4, in the mass query as little as possible with the format conversion.

5, when in SQL SERVER 2000

If the stored procedure has only one parameter and is of the output type, the parameter must be given an initial value when the stored procedure is invoked, otherwise a call error will occur.

6. Order BY and Gropu by

Using the order by and group by phrases, any index contributes to the performance improvement of a SELECT. Note If there is a null value in the index column, optimizer will not be optimized.

7. Any action on the column will result in a table scan, which includes SQL Server database functions, calculation expressions, and so on, to move the action to the right of the equal sign whenever possible.

8, IN, or clauses often use a worksheet to invalidate the index. If you do not produce a large number of duplicate values, you can consider the sentence to be opened. The open clause should contain an index.

9, SET showplan_all>10, careful use of cursors

In some situations where cursors must be used, consider moving the qualifying data rows into a temporary table, and then defining the cursor for the temporary table, which can improve performance significantly.

Note: The so-called optimization is where the clause takes advantage of the index, and there is a table scan or extra overhead that is not optimized. Experience has shown that the greatest improvement in SQL Server database performance has benefited from logical database design, indexing design, and query design. Conversely, the biggest performance problem is often caused by deficiencies in these same areas.

In fact, the essence of SQL optimization is in the results of the correct premise, with the optimizer can identify the statement, full use of the index, reduce the number of I/O table scan, as far as possible to avoid the occurrence of table search. In fact, SQL performance optimization is a complex process, these are only a manifestation of the application level, in-depth research will also involve the SQL Server database layer resource allocation, network layer flow control and the overall design of the operating system layer.