Questions tagged [index-rebuild]
12 questions
5
votes
1 answer
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 '['+OBJECT_SCHEMA_NAME([object_id])+']'+'.'+name AS TableName
FROM…
theindianvenom
- 53
- 4
3
votes
1 answer
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 wish to rebuild the clustered index offline without…
J. Mini
- 1,161
- 8
- 32
2
votes
3 answers
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 the logic, we no longer needed the pdf column.…
vacip
- 133
- 9
2
votes
3 answers
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 had to rebuild the indexes in order to fix a…
Traderhut Games
- 173
- 1
- 8
0
votes
1 answer
Why not make an online index rebuild resumable?
The documentation 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…
J. Mini
- 1,161
- 8
- 32
0
votes
0 answers
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 (table, cluster, or
index) which are needed for…
Ora_en
- 1
-1
votes
1 answer
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, it always happens at the same time and…
Stackoverflowuser
- 1,550
- 3
- 27
- 42
-1
votes
1 answer
SQL Server 2014: Why is Clustered Index Rebuild MUCH faster than initial index creation?
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…
frobot
- 19
- 2
-1
votes
2 answers
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 as per the logical order of keys.
So, what other…
variable
- 3,590
- 4
- 37
- 100
-3
votes
1 answer
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 Server migration, why do the following need to…
variable
- 3,590
- 4
- 37
- 100
-3
votes
1 answer
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?
-6
votes
1 answer
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 or non-clustered index also block user's…
variable
- 3,590
- 4
- 37
- 100