Daily collection and collation of SQL Server database optimization experience and considerations _mssql

There are a lot of tutorials about SQL optimization on the web, but it's messy. Recently have time to tidy up a bit, write to share with you, where there are errors and deficiencies, but also please correct the supplementary.

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. In general: ①. 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 often accessed at the same time, and each column contains duplicate values to consider establishing a combined index; ③. Combined indexes to make the key query an index overlay, 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 an 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 table scan, which includes database functions, evaluation expressions, and so on, when the query to move the operation to the right of the equal sign as much as 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 on to view the execution scenario. DBCC checks database data integrity. DBCC (DataBase consistency Checker) is a set of programs that verify the integrity of SQL Server databases.

10. Use Cursors with caution

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 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 database layer of resource allocation, network layer flow control and the overall design of the operating system layer.

The above content is about the day-to-day collection and collation of SQL Server database optimization experience and attention to all the narrative, I hope you like.