Creating indexes in SQL Server is a critical part of optimizing database performance. One particular option that can be specified during the index creation is MAX_DURATION. This option is especially useful when we create indexes on large tables in a production environment, where running index operations for a long duration could impact the performance of other queries. This blog post will explore this option, exploring its purpose, syntax, and usage.
What is MAX_DURATION?
MAX_DURATION Is a relational index option that you can specify when creating an index in SQL Server. It’s specifically designed for online index operations and sets the maximum time, in minutes, that an online index operation will run.
This option is particularly useful when you want to control the duration of indexing operations to avoid potential conflicts with other important database activities. For example, prolonged index operations could interfere with these scheduled tasks in a production environment with nightly database backups or ETL processes. By specifying a MAX_DURATION, you can ensure that the index operation will not overstep its allotted time.
Examples
Here’s an example of how to use the MAX_DURATION option in an index creation:
CREATE INDEX idx_EmployeeName ON Employee (FirstName, LastName) WITH (ONLINE = ON, MAX_DURATION = 60); -- 60 minutes
In this example, the idx_EmployeeName index is created on the Employee table, specifically on the FirstName and LastName columns. The MAX_DURATION is set to 60 minutes, meaning that if the index operation doesn’t finish within one hour, it will be rolled back.
You can further follow me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
