Questions tagged [index-rebuild]
The index-rebuild tag has no usage guidance.
11 questions
3
votes
1
answer
176
views
Does rebuilding a clustered index offline require extra space for each non-clustered index?
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 ...
- 823
-1
votes
1
answer
207
views
Identify which index's rebuild operation is causing the log file to grow
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,...
- 1,550
2
votes
3
answers
322
views
Azure SQL index rebuild after huge size reduction
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 ...
- 133
5
votes
1
answer
780
views
Rebuild of DB fails, yet size of the DB has doubled
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 '['+...
-1
votes
1
answer
326
views
SQL Server 2014: Why is Clustered Index Rebuild MUCH faster than initial index creation? [closed]
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, ...
- 9
-3
votes
1
answer
860
views
Why do indexes, statistics, and full text need to be rebuilt/updated/repopulated after migration?
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 ...
- 3,499
-3
votes
1
answer
267
views
After mass deletion of data, what should be done first: Index rebuilding or shrinking filegroups?
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?
0
votes
0
answers
554
views
Object necessary for warmstarting database cannot be altered ORACLE
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 (...
- 1
-6
votes
1
answer
235
views
Does offline index rebuild of clustered or non-clustered index block user's SELECT/UPDATE/INSERT/DELETE queries?
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 ...
- 3,499
-1
votes
2
answers
328
views
What other than `delete and re-creation of the index` happens when an index is rebuilt which does not happen in the reorganize command?
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 ...
- 3,499
2
votes
3
answers
3k
views
Constant rebuilding indexes to fix performance - why?
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 ...
- 173