Improved SQL Server performance benefits from logical database design

Index 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 in the application level of a embodiment, 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.

Principles of SQL statement optimization:

1, use the index to traverse the table faster

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: ①. A clustered index can be considered if a large number of duplicate values are available, and there is often a range query (between, "," =, "=") and an order by, group by-by-occurrence column; ②. Multiple columns are frequently accessed at the same time, and each column contains duplicate values that can be considered to establish a composite index; ③. Composite indexes try to make the key query an index overlay, and the leading columns must be the most frequently used columns. Indexes can help improve performance but not the more indexes the better, just the opposite. Too many indexes cause the system to be inefficient. When the user adds an index to the table, maintaining the collection of indexes will update the work accordingly.

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.

A cursor 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.

Ways to optimize 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 unexpired.

Serial

5, clean up the deletion log.

Summarize:

Optimizations are WHERE clauses take advantage of the index, and there is a table scan or extra overhead that is not optimized. The greatest improvement in SQL Server performance has been validated by the logical database design,