Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [index-maintenance]

Maintaining the physical storage of indexes

92 questions
61
votes
6 answers

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (SQL Server)? Is there a case for rebuilding indexes on a regular basis?
Nick Chammas
  • 14,170
  • 17
  • 73
  • 119
54
votes
4 answers

SQL Server: How to track progress of CREATE INDEX command?

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…
Jonesome Reinstate Monica
  • 3,199
  • 7
  • 34
  • 54
31
votes
1 answer

Guidelines for full-text index maintenance

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)…
Geoff Patterson
  • 8,147
  • 1
  • 25
  • 52
20
votes
6 answers

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. The main reason I want to reorganize is to…
Sriram Subramanian
12
votes
3 answers

Queries and updates extremely slow after IndexOptimize

Database SQL Server 2017 Enterprise CU16 14.0.3076.1 We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren IndexOptimize. The default Index Rebuild jobs had been running for a couple of months without…
Martin Bergström
  • 123
  • 6
12
votes
2 answers

MySQL indexes maintenance

I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries. I am familiar with that formula that calculates the ratio between the max space available for a table VS…
Nicolas
  • 241
  • 1
  • 3
  • 6
8
votes
1 answer

Database maintenance rebuilding indexes daily

My database is around 2.5TB. Is it a good practice to run rebuild indexes daily? We delete data daily around 20 GB & at the same time we delete partitions & we will add new partition. To run rebuild index is taking 8 hours, sometimes 24 hours.…
Rateesh reddy
  • 95
  • 1
  • 7
8
votes
6 answers

Does index rebuild time depend on the fragmentation level?

Is the required time for index rebuild dependent on the level of fragmentation? Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented 40% takes 1 minute? I am asking for the RUNTIME (for…
Magier
  • 4,585
  • 6
  • 37
  • 75
7
votes
2 answers

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run the same rebuild index offline, the average…
RLT
  • 173
  • 5
7
votes
1 answer

Index fragmentation problem after disabling page-level locking for an Index

I have an Index which has page level locking disabled and now I have index fragmentation issues for that index. I was aware that I won't be able to Reorganize the index, but I believed that I would be able to rebuild the index. Now I think rebuild…
user9516827
  • 1,265
  • 1
  • 8
  • 27
7
votes
1 answer

Problem reading data from secondary when reorganizing clustered index

We have an AOAG in SQL Server 2014 SP2 CU5 (3 nodes). There is a database with Read Committed Snapshot Isolation level ON. We have a large table compressed. Some of our bigger queries on this table are performed into the secondary. Then there is a…
Yaroslav
  • 2,776
  • 3
  • 23
  • 40
6
votes
2 answers

Identifying Unused Indexes on SQL Azure

I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information. See:…
Eoin Campbell
  • 201
  • 1
  • 3
6
votes
3 answers

Online Index Rebuild of large table takes exclusive lock

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…
Clement
  • 446
  • 1
  • 4
  • 13
6
votes
1 answer

Rebuilding indexes for a partitioned table having 300 partitions

Scenario Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded. Issue While examining the query plan of rebuilt indexes, I can…
Rsola
  • 73
  • 5
6
votes
2 answers

Rebuild Index not freeing up space

I had a situation yesterday where my indexes were rebuilt and database size doubled in size (50 % of the new size was unused). Sort in tempdb is set to off and I get the impression that this rebuild caused it (index rebuild not in tempdb). Is there…
Krishn
  • 333
  • 1
  • 4
  • 19
1
2 3 4 5 6 7