SQL Server Performance

Index

View the index of the Table: sp_helpindex [Table name]

The essence of clustered index is a two-way linked list

The essence of a non-clustered index is a balanced binary tree that uses multi-layer Classification to continuously narrow the search scope and finally find the pointer to the data.

Therefore, if the column data is too large to be indexed, the hash time and comparison time will be increased.

If the data similarity of a column is large, do not index it. Because a multi-layer tree structure cannot be created, the search range cannot be effectively reduced.

 

Page Splitting:

When a data page reaches 8 K capacity, data insertion or update will occur again, which will split the page and the newly created page will be stored in a discontinuous place, this increases the number of physical fragments, seriously affecting disk I/O.

Solution: Specify the column type, predict the capacity, and set a certain fill factor to strike a balance between disk space and performance.