I’ve implemented Ola Hallengren maintenance solution in one database, and I’m noticing that it’s getting blocked by itself, I don’t know why honestly. What I have noticed, is that it seems to be run within a serializable isolation level, which is weird since the default isolation level is read committed, and the maintenance script does not change it explicitly. So, the questions are: - Could the isolation level be related to this locking issue? - Assuming that indexes are rebuild in sequential way….why would the script block itself within the same SPID? Blocked process report sample can be found here . Output of @LogToTable='Y' can be found here . The process does complete without errors. This is still a problem, due to the fact that all running user requests are put on hold while the reindex process is running. Duration would not be an issue if no locks were generated. Since a lot of locks are generated, then duration (2 hours) is indeed an issue on a heavily used database.

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the method below, which at least shows me movement (that the index creation is not blocked). But I have zero idea if I am %10 through the process or %99. I tried the method described here: https://dba.stackexchange.com/a/102545/6229 but it shows a clearly wrong est completion time (it basically shows 'now' for a 60+ minute process that I am 10 min into) How can I get a clue? SELECT percent_complete, estimated_completion_time, reads, writes, logical_reads, text_size, * FROM sys.dm_exec_requests AS r WHERE r.session_id @@SPID AND r.session_id = 58

On a table with lots of deletes spanning over the past six months, I have an **extremely** fragmented non-clustered columnstore index. It is about 500 GB and has 50 partitions. If I either rebuild it online, re-create it with DROP_EXISTING, or just drop and recreate it the hard way, it goes down to 80 GB. When testing this, I've observed the following: * If I drop the columnstore index and recreate it offline (no DROP_EXISTING, just drop and create), it takes one hour * If I re-create it with DROP_EXISTING offline, it takes two hours. * If I rebuild the columnstore index online, it takes two hours * If I reorganize the columnstore index without any special settings, I get bored and quit after five hours and see no evidence that the size of the index has decreased. * If I reorganize the columnstore index with COMPRESS_ALL_ROW_GROUPS = ON, then it completes after four and a half hours. The index does not get smaller. It gets 20 GB **bigger**. I'm not doing anything smart with the partitions. I'm using a one-liner to hit the entire index. I am on SQL Server 2022, Enterprise Edition. The clustered index is 2 TB rowstore. The biggest column in the table is 8 bytes. The non-clustered index only consists of two columns, one of which is the partitioning column. The biggest of column in the index is an int. I have seen no evidence that the tuple mover is disabled, so I cannot explain how the fragmentation got this bad. My only guess is that I happen to know that the rows were deleted based on the column that is not the partitioning column, so it could be the case that the alignment of the columnstore index is so terrible that no rowgroups crossed [whatever threshold it is that the background merge task uses](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#reorganize-an-index) (I am unsure if that merge is part of the tuple mover or not). I have no idea why REORGANIZE made the columnstore index bigger. [Niko Neugebauer said during the pre-release for SQL Server 2016](https://www.nikoport.com/2015/12/25/columnstore-indexes-part-74-row-groups-merging-cleanup-sql-server-2016-edition/) that you have to invoke the tuple mover twice to really delete from a non-clustered columnstore index, but I did not think that behaviour even lasted until the real release of 2016 (I'm far past that, I'm on SQL Server 2022). My question is this: **Why would rebuilding a columnstore index online be dramatically faster than reorganizing it?** Would it have anything to do with the extreme fragmentation? For what it's worth, this same table has me suspicious of SQL Server [bugs](https://dba.stackexchange.com/q/345635/277982) such as [this](https://dba.stackexchange.com/q/345953/277982) .

[The documentation](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver16#resumable-index-considerations) makes resumable index rebuilds sound like magic. For example, it claims > Generally, there's no performance difference between resumable and nonresumable online index rebuild. and > Resumable index create or rebuild doesn't require you to keep open a long running transaction, allowing log truncation during this operation and a better log space management. Personally, I've [had them save the day once before](https://dba.stackexchange.com/a/344483/277982) . So, assuming: - SQL Server 2022 - Enterprise Edition - I have already decided to do my index maintenance online Why would I ever decide to rebuild an index without making said rebuild resumable?

### Context Buried deep in [this documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-ver16#arguments) , we find > a log file specified with unlimited growth has a maximum size of 2 TB Suppose that I want to rebuild a single non-partitioned 5 TB index ONLINE that belongs to a table in a database with the FULL recovery model on my Enterprise Edition box. The recovery model cannot change because it is in an AAG and is also not my decision to make. ### The Problem Does the limit on the size of the transaction log render it _impossible_ to rebuild such an index? If it is not impossible, then should any steps at all be taken to mitigate dramatically growing the transaction log? ### What I've Tried I've tried to test this myself, but the non-production clones of this server are shared with hundreds of other people and don't have transaction log backups. This means I'm just as scared of this rebuild breaking them as I am of breaking the actual production box. Very early in my career, I saw production boxes broken by lack of care with the transaction log. What tests I've done certainly indicate that running such a rebuild causes the transaction log to grow rapidly. *I'm scared*. I also lack the hardware to test this on my personal machines. I don't have 10 TB of storage sitting on my desk! I've tried to Google around the problem and found nothing to help. It is almost as if the problem that I'm worried about doesn't even exist.

Suppose that my server has a job that sometimes rebuilds indexes based on factors that I don't control (e.g. fragmentation). Suppose also that I am sure that it will one day rebuild an index that I'm interested in changing. Is there any way to instruct SQL Server that I want to change the WITH of an index upon its next rebuild, without rebuilding it right now? For example, suppose that PK_HugeTable is set to fill factor 25. On its next rebuild, I want to set it to 100. If I do not want to rebuild the index right now, what do I do? ALTER INDEX [...] SET works for some options, but not fill factor.

I have an uncompressed clustered primary key. It consumes 63.6 GB in the main part of the clustered index with 17.9 GB LOB. The table's only non-clustered index is 57.3 GB also with 17.9 GB LOB. I wish to rebuild the clustered index offline without the database growing. Running sp_spaceused with no arguments reports 66 GB in its "unallocated space" column. Is what I wish to do possible? Or will rebuilding the compressed index force a rebuild of the non-clustered index, thus taking at least 100 GB? Despite my best effort, I have found an answer in neither the documentation or Stack Overflow. My experiments with a small database that I had handy suggest that rebuilding the clustered index does not force a rebuild of the non-clustered indexes.

I've always been told that REORGANIZE does nothing to fix poor page fullness because all that it does is reorder pages on disk. To investigate this, I read the documentation. However, I've found it contradicting itself. _Optimize index maintenance to improve query performance and reduce resource consumption_ [claims](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#reorganize-an-index) > Reorganizing also compacts index pages to make page density equal to the fill factor of the index. and [the docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16#reorganizing-indexes) for ALTER INDEX support this > Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. but this is contradicted by [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16#fillfactor--fillfactor) for fill factor > An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The Database Engine doesn't dynamically keep the specified percentage of empty space in the pages. So, what's going on? Does REORGANIZE compact pages and respect fill factor? Or is it just moving leaf pages and not compacting?

We usually use Ola Hallengren's solution that takes care of Update Statistics which is inside the Index optimize job that we have. We have it scheduled for every Sunday midnight in all servers (1500+) as a standard practice. Now, there are a few servers where there are a lot of performance troubles during the middle of the week and we run sp_updatestats for that particular database when an incident is raised for performance issues. We have a huge environment with all versions in use, 2000 to 2016. It is the 2012 & 2014 versions where we have been running the update stats manually for specific database mid-week. Recently, we also had to schedule sp_updatestats for every day for some very active databases due to recurring troubles. My questions : 1. How often should we have it scheduled? 2. What are the drawbacks for scheduling it too often? 3. Is there a way of evading the drawbacks with the regular updates running so frequently as I heard it takes more time to compile and might degrade performance for sometime? Please help me with the experience you had with this.

I have some query that till now helped me to check index rebuild progress but it doesn't show the REORGANIZE progress. I mean reorganizing index (at this case clustered) like this:

ALTER INDEX IX_Index_CL ON TableName
REORGANIZE

Queries that I found at internet forums didnt help. The only query that shows something is:

SELECT percent_complete, * 
FROM sys.dm_exec_requests 
WHERE session_id ='my session'

But looks like it shows the wrong info.

I am currently building out a few features for a project where customers are able to change up their database schema via an UI and we handle all the nitty-gritty details on running the required statements to update the database (PostgreSQL). I did a bit of research on what some gotchas could be and one of the gotchas we've ran into are the index names, especially those that are auto-generated when creating UNIQUE constraints. We allow the users to declare which column they want indexed, but we didn't realize that we now create two indexes whenever they set indexed to true on a UNIQUE column. This is solved now already, however this raised the question on what happens when columns are renamed. As far as our testing goes, renaming columns does not change the index names and when then creating a new column with the previously occupied name an index with a number appended gets created:

CREATE TABLE users (
		user_id SERIAL PRIMARY KEY,
		email VARCHAR(255) UNIQUE NOT NULL
);

ALTER TABLE "users" RENAME COLUMN "email" TO "email_one";
ALTER TABLE "users" ADD COLUMN "email" INTEGER UNIQUE;

/*
postgres@localhost:postgres> \d users
+-----------+------------------------+----------------------------------------------------------+
| Column    | Type                   | Modifiers                                                |
|-----------+------------------------+----------------------------------------------------------|
| user_id   | integer                |  not null default nextval('users_user_id_seq'::regclass) |
| email_one | character varying(255) |  not null                                                |
| email     | integer                |                                                          |
+-----------+------------------------+----------------------------------------------------------+
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email_one)
    "users_email_key1" UNIQUE CONSTRAINT, btree (email)
*/

Since I am not a database expert and haven't maintained large databases at all I am unsure if this could possibly ever lead to issues. I asked a friend of mine and he gave me the following function that I could use instead of RENAME TO:

CREATE OR REPLACE FUNCTION rename_column_and_index(
    tbl_name TEXT,
    old_col_name TEXT,
    new_col_name TEXT
) RETURNS void AS $$
DECLARE
    index_record RECORD;
    old_index_suffix TEXT;
    new_index_name TEXT;
BEGIN
    -- Find all indexes that contain the old column name
    FOR index_record IN
        SELECT indexname
        FROM pg_indexes
        WHERE tablename = tbl_name
          AND indexdef LIKE old_col_name || '%'
    LOOP
        -- Start a savepoint
        EXECUTE 'SAVEPOINT before_rename';

        BEGIN
            -- Extract the suffix from the old index name
            old_index_suffix := substring(index_record.indexname from length(quote_ident(old_col_name)) + 1);
            -- Create the new index name
            new_index_name := quote_ident(new_col_name) || old_index_suffix;

            -- Rename the column
            EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) || 
                    ' RENAME COLUMN ' || quote_ident(old_col_name) || 
                    ' TO ' || quote_ident(new_col_name);

            -- Rename the index
            EXECUTE 'ALTER INDEX ' || quote_ident(index_record.indexname) || 
                    ' RENAME TO ' || new_index_name;
        EXCEPTION
            WHEN OTHERS THEN
                -- Rollback to the savepoint in case of error
                EXECUTE 'ROLLBACK TO SAVEPOINT before_rename';
                RAISE;
        END;

        -- Release the savepoint
        EXECUTE 'RELEASE SAVEPOINT before_rename';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- SELECT rename_column_and_btree_index('users', 'email', 'email_one');

Now my question is: Am I too fearful of this issue? Is this function any good / makes future maintenance easier? Or can I just stop worrying about the automatic indexes completely?

What guidelines should be considered for maintaining full-text indexes? Should I REBUILD or REORGANIZE the full-text catalog (see BOL )? What is a reasonable maintenance cadence? What heuristics (similar to the 10% and 30% fragmentation thresholds) could be used to determine when maintenance is needed? (Everything below is simply extra information elaborating on the question and showing what I've thought about so far.)

**Extra info: my initial research** There are a lot of resources on b-tree index maintenance (e.g., this question , Ola Hallengren's scripts , and numerous blog posts on the subject from other sites). However, I have found that none of these resources provide recommendations or scripts for maintaining fulltext indexes. There is Microsoft documentation that mentions that defragmenting the b-tree index of the base table and then performing a REORGANIZE on the full-text catalog may improve performance, but it doesn't touch on any more specific recommendations. I also found this question , but it is primarily focused on change-tracking (how data updates to the underlying table are propagated into the fulltext index) and not the type of regularly scheduled maintenance that could maximize the efficiency of the index. **Extra info: basic performance testing** This SQL Fiddle contains code that can be used to create a full-text index with AUTO change tracking and examine both the size and query performance of the index as data in the table is modified. When I run the script's logic on a copy of my production data (as opposed to the artificially manufactured data in the fiddle), here is a summary of the results I am seeing after each data modification step: Even though the update statements in this script were fairly contrived, this data seems to show that there is a lot to be gained by regular maintenance. **Extra info: Initial ideas** I'm thinking about creating a nightly or weekly task. It seems that this task could perform either a REBUILD or REORGANIZE. Because the full-text indexes may be quite large (tens or hundreds of millions of rows), I'd like to be able to detect when indexes within the catalog are sufficiently fragmented that a REBUILD/REORGANIZE is warranted. I'm a little bit unclear on what heuristics might make sense for that.

Noticed REDO thread getting blocked for the whole period of running following query, what could be the reason? It is on SQL Server 2019 CU21, The blocking was for almost more than 1hr+, for the entire period the physical stats on that DB is done. And, the blocked resource is on the same object, curious why it is only one specific object when the index fragmentation stats collection is happening across all objects in that DB. Can this be controlled or avoided to not keep the redo fall behind for such long duration? Query: INSERT INTO #index_fragmentation_log SELECT GETDATE() AS [LogDate], DB_NAME() AS [DatabaseName], SCHEMA_NAME(o.schema_id) AS [SchemaName], OBJECT_NAME(ips.object_id) AS [TableName], i.name AS [IndexName], i.type_desc AS [IndexType], p.partition_number AS [PartitionNumber], ips.alloc_unit_type_desc AS [AllocUnitTypeDesc], ROUND(ips.avg_fragmentation_in_percent, 2) AS [AvgFragmentationInPercent], ips.fragment_count AS [FragmentCount], ROUND(ips.page_count * 8 / 1024, 2) AS [IndexSizeMB], p.[rows] AS [Rows], i.fill_factor AS [FillFactor], p.data_compression AS [DataCompression], NULL AS [Status], NULL AS [ExceptionLog] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id JOIN sys.partitions AS p ON ips.object_id = p.object_id AND i.index_id = p.index_id AND ips.partition_number = p.partition_number JOIN sys.objects AS o ON ips.object_id = o.object_id WHERE ips.avg_fragmentation_in_percent > 0.0 AND ips.index_type_desc NOT IN ( 'HEAP' ) AND o.schema_id IN ( SCHEMA_ID('dbo')); Blocking Details: | session_id | command | blocking_session_id | wait_time | wait_type | wait_resource | |------------|------------|---------------------|-----------|--------------------------|------------------------| | 29 | DB STARTUP | 0 | 43 | REDO_THREAD_PENDING_WORK | | | 33 | DB STARTUP | 0 | 59 | REDO_THREAD_PENDING_WORK | | | 37 | DB STARTUP | 1843 | 2564627 | LCK_M_X | OBJECT: 9:309576141:15 | | 38 | DB STARTUP | 0 | 74 | REDO_THREAD_PENDING_WORK | | | 48 | DB STARTUP | 0 | 61 | REDO_THREAD_PENDING_WORK | | | 190 | DB STARTUP | 0 | 105 | REDO_THREAD_PENDING_WORK | | | 195 | DB STARTUP | 0 | 12 | REDO_THREAD_PENDING_WORK | | Details the DB fall behind because of Redo thread blocked. | database_name | recovery_lsn | truncation_lsn | last_hardened_lsn | last_received_lsn | last_redone_lsn | last_redone_time | |-------------------|------------------------|------------------------|------------------------|------------------------|------------------------|-------------------------| | db01 | 4656563000100725600001 | 4656602000033369600004 | 4656602000039955200001 | 4656602000039954400001 | 4656602000039947200042 | 2024-01-19 18:15:45.363 | | db02 | 87431000001092000001 | 87445000001880800001 | 87445000001892800001 | 87445000001892000001 | 87445000001892000001 | 2024-01-19 18:15:28.773 | | blocked_db03 | 271295000189302400001 | 271577000159532800001 | 271579000053756000001 | 271579000053755200001 | 271577000176204800080 | 2024-01-19 17:24:56.883 |

(SQL Server 12.0.2000.8 running in Azure) It has been my understanding that REORGANIZEing an index should not interfere with other operations (that is, it should not block queries on the table undergoing index reorganization, and it certainly should not block queries on other tables). However, I have a nightly index maintenance job that appears to be blocking other queries while it runs. The query causing the block is in the format: ALTER INDEX [indexName] ON tableName REORGANIZE It is causing other queries to wait, even simple ones like: SELECT * FROM tableName WHERE indexedColumn = @value I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by. And again, the table undergoing index maintenance and the table in the SELECT are completely unrelated (they're even in different *schemas*; FWIW the table being reorganized is in dbo). The table being reorganized has almost 500 million rows. The index being affected is a non-clustered, non-unique index on a single bigint column used by a foreign-key. The table itself consists of two bigint columns, one tinyint, and a couple small nvarchars. Doesn't seem like anything too extraordinary, but I cannot figure out why it's blocking other queries. Is there some hidden dependency that I'm missing?

I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I have a single job that is running long (4+ hours) and have a couple of questions: 1. For setting this up, I assume the simplest method would be to add @DatabasesInParallel='Y', to my parameters list on my existing SQL Server agent job, then in SSMS perform a Script Job As > Create To... on that job so I'll have a script to create an exact copy and then just change the @job_name in the script to the new job name so that the new job gets created with the same parameters and uses the same schedule? 2. Can I set up 3 or more jobs to run in parallel, or is it limited to 2? 3. The server I want to set this up on has 5 user databases on it. If I set up parallel jobs for index maintenance on @databases='USER_DATABASES', can two of the jobs perform indexing on the same database simultaneously? For instance, Job 1 is working on a large database with a lot of heavily fragmented indexes, and Job 2 works through the other 4 databases fairly quickly, if Job 1 is still running, will Job 2 end, or will it start working on the database that Job 1 has already been working on? Alternatively, I may set up separate jobs and instead of using @databases='USER_DATABASES' on them I'll specify what user databases each should perform index maintenance on and not use @DatabasesInParallel.

I'm trying to rebuild the clustered index of a large table (77GB) on Azure SQL Database. There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option. This has worked well for smaller tables; however, when I run it on this large table it seems to take exclusive locks on the table. I had to stop it after 5 minutes because all transactional activity was timing out. From session with SPID 199:

ALTER INDEX PK_Customer ON [br].[Customer] 
REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

From another session: A bit further below in the same results: * Object 978102525 is the clustered index. * Object 1125579048 is the table. I understand that online rebuild can take locks for a 'short' duration at the start and end of the process. However, these locks are taken for several minutes, which is not exactly a 'short' duration. ### Additional info While the rebuild is running, I ran SELECT * FROM sys.index_resumable_operations; but it returned 0 rows, as if the rebuild hadn't started at all. The smaller tables also have a PK potentially > 900 bytes and the same ALTER statement worked without any long blocking so I don't think it's related to PK size. These smaller tables also had a similar amounts of nvarchar(max) columns. The only real difference I can think of is that this table has many more rows. ### Table definition Here is the full definition of br.Customer. There are no foreign keys or non clustered indices.

CREATE TABLE [br].[Customer](
	[Id] [bigint] NOT NULL,
	[ShopId] [nvarchar](450) NOT NULL,
	[accepts_marketing] [bit] NOT NULL,
	[address1] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[address2] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[city] [nvarchar](max) NULL,
	[company] [nvarchar](max) NULL,
	[country] [nvarchar](max) NULL,
	[country_code] [nvarchar](max) NULL,
	[email] [nvarchar](max) MASKED WITH (FUNCTION = 'email()') NULL,
	[first_name] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[last_name] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[note] [nvarchar](max) NULL,
	[phone] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[province] [nvarchar](max) NULL,
	[province_code] [nvarchar](max) NULL,
	[state] [nvarchar](max) NULL,
	[tax_exempt] [bit] NOT NULL,
	[verified_email] [bit] NOT NULL,
	[zip] [nvarchar](max) NULL,
	[multipass_identifier] [nvarchar](max) NULL,
	[created_at_local] [datetimeoffset](7) NOT NULL,
	[updated_at_local] [datetimeoffset](7) NOT NULL,
	[tags] [nvarchar](max) NULL,
	[address_phone] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[address_firstname] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
	[address_lastname] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[ShopId] ASC,
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [br].[Customer] ADD  DEFAULT ('0001-01-01T00:00:00.000+00:00') FOR [created_at_local]
GO

ALTER TABLE [br].[Customer] ADD  DEFAULT ('0001-01-01T00:00:00.000+00:00') FOR [updated_at_local]
GO

### sp_WhoIsActive I investigated further today (24 Sep) and ran SP_WHOISACTIVE @get_locks = 1, which clearly shows all UPDATE/INSERT/DELETE transactions blocked by the session running the ALTER INDEX. Locks held on Customer table by query running the ALTER INDEX:

Locks from session running UPDATE on same table:

When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and doesn't proceed further? We're using SQL Server 2017 Enterprise Edition with an Always On Availability Group.

SELECT query requires a SCH-S lock. This can be shared with other read queries. UPDATE query requires a SCH-M lock. This cannot be shared with other read/update queries. Link: https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/ The example shows an index reorg (Sch-S) lock blocks the statistics update's Sch-M lock. But why does it also block the subsequent Sch-S lock requested by the SELECT query? Aren't Sch-S locks shared?

I'd like your opinion about Index Maintenance in Distributed availability groups. In this way, the size of the databases is at the limit of terabytes, and this process is supposed to be transferred to one or more other sites. In your opinion, what best practices should we consider here? These sites are connected synchronously; we should not lose data. Now we want to execute the Rebuild index operation. Considering the type of waits and network bandwidth, etc., what is the best way to manage indexes and perform these processes?

We use SQL Server 2012 Standard Edition. Is there a way to rebuild the index, trying not to cause data table locking, and reducing the impact on performance? When we rebuild the index, it causes the program accessing the database to time out. I am hoping to avoid or reduce the performance impact.