SQL Server tuning information

Conceptual Index classification

Clustered index: guidelines for designing clustered index structures and clustered Indexes

Non-clustered index: Non-clustered index structure and non-clustered index design guide

 

Performance evaluation criteria

The performance evaluation criteria are mainly based on the number of logical reads performed by the execution operation.

 

Optimize technical index coverage Query

Based on the characteristics of non-clustered indexes, we can design and optimize indexes so that queries can reach index coverage as much as possible, that is: when all the columns in the query are included as key columns or non-key columns in the index, indexes with inclusive non-key columns can significantly improve the query performance. This improves the performance, because the query optimizer can locate all column values in the index, and does not access the table or clustered index data, thus reducing disk I/O operations.

 

Maps non-clustered index searches to clustered index searches based on business logic.

For example, a log table usually contains an incremental Id field and a logtime field. The growth sequence of these two fields is the same. The ID field is a clustered index column, and the logtime field is a non-clustered index. If we map logtime to the ID before searching based on logtime, the speed will be greatly improved. For details, refer to the index structure in the index category.

 

Optimize Query conditions based on business logic

For example, if a log table is used to record the start and end times of a task, and all the tasks recorded in the table are executed sequentially, when we look for the task corresponding to a time, instead of using t> = begintime and T <= endtime, you only need t> = begintime order by begintime DESC