SQL Server query plan summary

1. query plan text display
Set showplan_text on

 

Set statistics profile on

 

2. Several metrics

1. [Table scan]: traverses the entire table and searches for matched record rows. This operation will check one row at a time. Of course, the efficiency is also the worst.
2. [index scan]: according to the index, some records are filtered out from the table, and then the matched record rows are searched. The display range is smaller than that of the first method, therefore, it is faster than [Table scan.
3. [index seek]: locate (obtain) the location where the record is stored Based on the index and then obtain the record. Therefore, it is faster than the first two methods.
4. Clustered index scan: Same as table scan. Note: Do not think that there is an index here, so it is different. In fact, it means that each row of records is scanned row by clustered index, because records are stored in the order of clustered index. [Table scan] Only says: the table to be scanned has no clustered index, so these two operations are essentially the same.
5. [clustered index seek]: Obtain records directly based on the clustered index, the fastest!

 

Therefore, when you find that a query is slow, you can first check which operations are expensive, and then check which operations are to find records, whether it is table scan or clustered index scan. If it is related to the two operation types, you need to add an index to solve the problem. However, adding an index will also affect the modification of the data table, because the index of the corresponding field must be updated when the data table is modified. Therefore, too many indexes may affect the performance. Another case is that it is not suitable for adding an index: the status of a field expressed as 0 or 1. For example, if the vast majority is 1, adding an index is meaningless. In this case, only the values 0 or 1 can be considered for separate storage. Table sharding or partition is a good choice.