Requirements: If there is a table with tens of millions of data, it is necessary to add a non-aggregated index to the table to improve query efficiency, but the database is already running in the production environment, and many applications are performing adding, deleting, modifying, and querying operations on the table, which also means that multiple threads are performing operations on the table. If you use a normal index addition statement directly on the table, it will cause the program execution in the production environment to be abnormal, such as blocking and execution timeout.
The normal SQL statement to add a non-clustered index is as follows:
The security optimized SQL statement for adding non-clustered indexes is as follows:
Interpretation:
ONLINE = ON Role: Allows users to query, insert, update, and delete tables (i.e., "online" operations) during index creation, supported only by Enterprise Edition.
SORT_IN_TEMPDB = ON Function: Transfer the "temporary sorting data" needed for index creation/reconstruction from your business database to tempdb, not in the business database I am working on.
MAXDOP = 4 Role: Specifies MAXDOP to limit parallelism to ensure that it does not occupy all schedulers, i.e., to limit the index operation to perform in parallel with up to 4 CPU cores (threads).
Reference:
The hyperlink login is visible.
The hyperlink login is visible. |
Posted on10 minutes ago
|
|
|
Landlord|
Posted on8 minutes ago
|
