GO: SQL Server database Optimization Experience Summary

Considerations for Optimizing Databases:

1. Index key fields.

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

3. Back up the database and clean up junk data.

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

5. Clean and delete logs.

The basic principles of SQL statement optimization:

1. Use the index to traverse the table more quickly.

The index established by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. A reasonable index design should be based on the analysis and prediction of various queries. Generally speaking: ①. There are a number of duplicate values, and often have a range of queries (between, >,<,>=,< =) and order BY, the group by occurs column, you can consider establishing a clustered index ; ②. Multiple columns are frequently accessed at the same time, and each column contains duplicate values to consider a composite index; ③. Combined index to make the key query an index overlay as much as possible, its leading column must be the most frequently used column.

2, is null and is not NULL

You cannot use NULL as an index, and any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that 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. It is related to full table scan and range scan. Almost all in-action clause queries are rewritten as subqueries that use exists.

4, in the vast number of queries as far as possible to use the format conversion.

5. When in SQL SERVER 2000, if the stored procedure has only one parameter and is of type output, you must give the parameter an initial value when calling the stored procedure, otherwise the call error will occur.

6. ORDER by and Gropu by

With both the order by and the group by phrase, any index helps to improve the performance of select. Note If there is a null value in the index column, optimizer cannot be optimized.

7. Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

8, IN, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index.

9, SET showplan_all on view execution scheme. DBCC checks database data integrity. DBCC (Database consistency Checker) is a set of programs that are used to verify the integrity of SQL Server databases.

10. Use Cursors with caution

In some cases where cursors must be used, consider moving the qualifying rows of data into a temporary table and then manipulating the temporary table definition cursors, which can significantly improve performance.

Note: The so-called optimization is that the WHERE clause takes advantage of the index, which is not optimized, that is, a table scan or additional overhead. Experience shows that the biggest improvement in SQL Server performance is due to logical database design, index design, and query design. Conversely, the biggest performance problems are often caused by deficiencies in these same areas. In fact, the essence of SQL optimization is that the result is correct, with the optimizer can recognize the statement, fully use 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 in the application level of a manifestation, in-depth research will also involve the database layer resource configuration, network layer flow control and the overall design of the operating system layer.

GO: SQL Server database Optimization Experience Summary