I'm running...

ALTER INDEX PK_MyTable 
    ON fct.MyTable 
    REBUILD PARTITION = 261 
    WITH (
        ONLINE = ON (
            WAIT_AT_LOW_PRIORITY (
                MAX_DURATION = 30 minutes, 
                ABORT_AFTER_WAIT = SELF
            )
        ), 
        RESUMABLE = ON, 
        MAX_DURATION = 355 
    )
;

...and I expect it **not** to cause any blocking, but it does. It is asking for a LCK_M_SCH_M lock. My statement is blocked by an INSERT INTO statement, which is what I expect; but it is also blocking many other statements that are asking for a LCK_M_SCH_S lock, and I was not expecting that. At the same time there is a BACKUP running of a database that has FILESTREAM. Could this be the reason? What else could cause this blocking? Have I completely misunderstood WAIT_AT_LOW_PRIORITY? RCSI is on.

[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?

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.

We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow. I have log file growth event alerts enabled, it always happens at the same time and continues for a few hours and stops later, so quite sure it is the same index all the time. I'm not sure whether it is a clustered or non-clustered index but suspect a clustered index on a large table, of which there are a couple, is causing this. After the maintenance job completes, I shrink the log and it stays stable for the whole week. How can I identify which index is causing the file to grow? Prefer to avoid logging solutions such as extended events or audit.

We have a table in an Azure SQL database that used to have an nvarchar(max) column storing pdf files. (The wonders of external developers.) The table grew to 156 GB. It has 476000 rows. After changing the logic, we no longer needed the pdf column. The only reasonable way to get rid of the data in it was to drop the column and recreate the column (in case some weird process was still referencing it). However, the table size is still reported as 156 GB. The backup table I just created (SELECT INTO) is 128 MB, so that seems to be the real size of the data. I let an index rebuild (ONLINE) run overnight on the clustered PK index. It failed with a TCP error sometime between 8 and 12 hours. The index is still 95% fragmented, the size is still reported as 156 GB. Is there an explanation why this is so eye-wateringly slow? **Is there a better way?** Production database, table is used by a website, has to be accessible, so can't do it OFFLINE unless it takes less than 10 minutes - which nobody can guarantee. Can I just *build all the indexes on the backup table, drop the original table and rename the backup?* That sounds risky (small risk of losing a record created just the wrong time). ----- I'm trying to make Azure realize it is no longer used. Allocated, I'm OK with that. Used, not so much: The table in question: Again, it's not the reserved space that is the issue, it is the used space.

We have a database server (2016 SQL Server), that we have added a step of 'rebuilding indexes' to the deployment process. In decades of working with MS SQL Server at a many companies, I've never ONCE had to rebuild the indexes in order to fix a performance problem. Yet, we do it at least every 2 weeks, and often times more often than that. And I'm told "yep! that fixed the problem!" It seems to me, it is much more likely to have fixed a symptom. I know, I've had an issue with a database where a query would run > 10 minutes the first time, and in a few milliseconds after it had completed once. (In this case, it had built a temp index on Right(field,8) that someone was using in the query, and adding an index on that fixed it) I'm thinking maybe a rebuild is causing everything to be loaded into memory, so the server has the entire index right there and ready to use? Has anyone else seen this, and is this an indication of another issue that maybe we can fix? (More RAM, better disks, or something?)

I attempted to rebuild all the indexes of a DB using the query,

USE [DB_Name];
GO
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT '['+OBJECT_SCHEMA_NAME([object_id])+']'+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

The query executed for around 3.5 hours and then it threw the error message, saying that it ran out of disk space, which was not true, but possible that the DB reached its size limit. But the only issue is that the database actually grew in size, by almost 100% , without any of the indexes being rebuilt. The main reason why I chose to rebuild was that most of the indexes were fragmented beyond 75% . Now , restoring from a backup is not an option unfortunately, as we have new data being written in already, and it's been hours. Will another rebuild with sufficient disk space solve it ? If so , do i still go by the thumb rule of 1.5 times the space of current DB ?

I have a table where rows consist of a few GUID ids and then a huge byte array of data. There is a partitioned clustered index on this table, which orders data by the GUIDs. The table is large, containing ~3tb of data, most of which is the byte array of data, which is not a part of the index. When first creating the index, it took well over a day, but the index rebuild (not reorganize) took under an hour. **Why did the rebuild go so much quicker?** My understanding is it would have to drop and re-create the clustered index, which would involve it re-writing this large table all over again. It's might be worth noting that when lookin at size reports for the table, the table itself takes ~3tb but the index is 1gb or less. Also worth noting, the table wasn't partitioned until that initial index create I did previously, while the rebuild was done on an already partitioned table. Not sure if that makes an impact. Again, just trying to gain understanding. Why would my rebuild be so significantly faster than the initial create? EDIT: It is the **clustered index** I initially created and was re-building. There are no other indexes on this table

I have created a new server, restored the system dbs and presented a copy of the data/log disks from the previous machine (or alternatively maybe I restore the user dbs using the .bak files). Post SQL Server migration, why do the following need to be performed: 1. Index rebuild on all databases 2. Statistics update on all databases 3. Repopulation of all full text indexes (or rebuild all full text catalogs) Example: https://www.sqlservercentral.com/articles/sql-server-migration-1 Does the answer differ whether the upgrade was done in-place vs by restoring from bak files? And server version vs different version?

When we delete huge amounts data from a table, what should be the best thing to follow: Shrink the file group first and rebuild indexes, or Rebuild indexes then shrink the file group?

I have this error when I want to rebuild system index : 00701. 00000 - "object necessary for warmstarting database cannot be altered" *Cause: Attempt to alter or drop a database object (table, cluster, or index) which are needed for warmstarting the database. *Action: None. the command for rebuild : ALTER INDEX I_IND1 REBUILD ONLINE TABLESPACE SYSTEM; I tried also : alter index I_IND1 rebuild; but the same message. Please any help .

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M). Does an offline index rebuild of clustered or non-clustered index also block user's SELECT/UPDATE/INSERT/DELETE queries?

When an index reorganize command is fired, then it will not drop and re-create the index. However just like a rebuild it will defragment the data and ensure that the physical ordering of the page is as per the logical order of keys. So, what other than delete and re-creation of the index happens when an index is rebuilt which does not happen in the reorganize command? For example: The page adheres to the fill factor upon rebuild thereby freeing up space in each page. What else?