Resumable Online Index Creation: A Must-Know Feature for SQL Server 2019

-

 


Creating large indexes on production databases is a common task for database administrators. However, it can be a pain, especially when you need to create the index within a short window of time or manage the log growth. In such cases, you may need to change the recovery model to simple, take a full backup after changing the recovery model to full, and enable log dumps. All of these steps can be time-consuming and complex.


Thankfully, SQL Server 2019 introduced a new feature called Resumable Online Index Creation that makes index creation easier and more manageable. This feature allows you to pause the index-building process, truncate or backup the log, and then resume the process in chunks. With Resumable Online Index Creation, you can create large indexes without using as much log space and without long-running transactions that block other maintenance activities.


Resumable Online Index Creation supports the following scenarios:


Resume an index creation operation after a failure, such as after a database failover or after running out of disk space.

Pause an ongoing index creation operation and resume it later, allowing you to temporarily free up system resources as required.

Create large indexes without using as much log space and without long-running transactions that block other maintenance activities.

To use Resumable Online Index Creation, you can simply pause and restart the build of an index at the point where it was paused. For example, if you create an index only to get complaints from users or to manage your log growth, 


you can pause the process by killing the SPID creating the index or by running the command

ALTER INDEX MyResumableIndex ON MyTable PAUSE. 


To restart the process, you can execute the command 


ALTER INDEX MyResumableIndex on MyTable RESUME or simply re-execute your CREATE INDEX statement.


One thing to keep in mind is that SORT_IN_TEMPDB=ON is not supported when using RESUMABLE=ON. Additionally, SQL Server 2019 comes with a new system view called "sys.index_resumable_operations," which shows the percentage complete, current state, start time, and last pause time of the index-building process.


Resumable Online Index Creation is an essential feature for anyone dealing with large databases, especially in OLTP environments. It makes index creation faster, easier, and more manageable. If you're using SQL Server 2019, it's definitely a feature you should explore and use to its fullest.

Comments

Post a Comment

Popular posts from this blog

Interview Questions on AOAG

-
1.What are the prerequisites for setting up AlwaysOn? 2.How do you set up replication in AlwaysOn environment? 3.How do you manage replication during Windows patching or failover if replication has been set up in AlwaysOn? 4.How do you sync logins in AlwaysOn? 5.How do you sync users in AlwaysOn secondary? 6.How do you add database files in AlwaysOn? 7.How do you perform an in-place upgrade of SQL Server in a AlwaysOn environment? 8.What is the procedure for SQL Server patching in AlwaysOn? 9.How do you failover a mirror server if replication has been set up? 10.What is the SPN concept in AlwaysOn? 11.What is file share in AlwaysOn? 12.How do you create multiple AlwaysOn listeners? 13.How do you check latency in AlwaysOn? 14.What is the command used to check latency in replication without using GUI? 15.What are DNS issues in AlwaysOn? 16.If a user is connecting to the primary and not able to connect to the secondary, and the secondary is in read-only mode, how do you fix the issue in A
Read more

All about SQL Server Execution Plan

-
Introduction: SQL Server execution plans hold the key to optimizing query performance. By understanding the various operators and costs within an execution plan, you can uncover hidden inefficiencies and enhance the speed and efficiency of your queries. In this blog, we will dive deep into common operators like Index Scan, Index Seek, Nested Loops, Merge Join, and Sort, and demystify their associated costs. Index Scan: Scans the entire index to locate the requested data. The cost is directly proportional to the number of rows in the index. A high cost may indicate inadequate index utilization or non-selective queries. Index Seek: Directly seeks into the index for specific rows. The cost is proportional to the number of retrieved rows. A high cost might indicate non-selective queries or underutilized indexes. Nested Loops: Joins tables by iterating through each row in one table for matches in the other. The cost is determined by the product of rows in both tables. A high cost could sugg
Read more

Understanding SQL Server Patching: GDR versus CU

-
Introduction: Are you curious about the distinctions between GDR and CU when Microsoft releases a KB article with new fixes or security updates? In this article, we'll delve into the details and provide some helpful tips along the way. Setting the Stage : GDR versus CU: Let's begin by understanding Microsoft's explanation: Microsoft defines the General Distribution Release (GDR) and Cumulative Update (CU) designations as two different servicing options for SQL Server baseline releases, which can be either an RTM release or a Service Pack release. Determining the Baseline: To determine your SQL Server's baseline version, you can use various methods. One way is by executing the following T-SQL code: SELECT @@VERSION; Alternatively, you can check the version by examining the sqlserver.exe file in the corresponding \Binn directory of your installation. Another option is using the SERVERPROPERTY() function for more detailed version information, but that is beyond the scope
Read more