SQL Server Performance Optimization Research Report

Take the stored procedure for lesson selection as an example:

To add to the jargon, a simple personal understanding: physical reads "read from memory", logical reads "Read through logical Structure", and physical reads are much less efficient than logical reads.

1. Add index in selected timetable (large amount of data)

SELECTExperimentalprojectname fromExperimentalarrangementWHEREIdinch(SELECTExperimentalarrangementid fromExperimentopenWHEREIdinch(SelectExperimentopenid fromCourserecordWHERETermid=(SELECTId fromSemesterWHERETermstate=1) andStudentID=33371))

View Code

View index:

Sp_helpindex ' Courserecord '

Query efficiency in the case of an index:

Results of Database Engine Tuning Advisor

Test sales of queries after deleting an index

DROP INDEX courserecordindex on Courserecord;

Optimization results for not adding semester IDs in the index

Only semester ID is added to the index without the optimization result of student ID

As a result, only adding termid as the result of the index is the same as the result of not adding an index, and the prediction should be that the amount of data in the semester table is very small, which results in the optimization effect is not obvious.

Conclusion: How to build index to improve efficiency, the first index of the Table object data volume must be huge, so that there will be obvious efficiency improvement effect; Furthermore, indexed objects, such as the data volume of the main table corresponding to the external code, are huge.

SQL Server Performance Optimization Research Report