Questions tagged [index-maintenance]
Maintaining the physical storage of indexes
109
questions
70
votes
7
answers
99k
views
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?
- 14.7k
62
votes
4
answers
80k
views
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 ...
34
votes
1
answer
11k
views
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 (...
- 8,397
21
votes
6
answers
72k
views
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. ...
Sriram Subramanian
12
votes
3
answers
2k
views
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 ...
- 123
12
votes
2
answers
17k
views
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 ...
- 241
9
votes
3
answers
2k
views
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 ...
- 536
8
votes
6
answers
12k
views
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%...
- 4,787
8
votes
1
answer
905
views
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 ...
- 1,315
8
votes
1
answer
4k
views
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 ...
7
votes
1
answer
3k
views
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 ...
- 2,826
7
votes
2
answers
2k
views
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 ...
- 173
6
votes
1
answer
3k
views
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 ...
- 73
6
votes
2
answers
8k
views
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 ...
- 373
6
votes
1
answer
10k
views
Rebuild index online without risk to other requests
We have a SQL Server 2012 Enterprise Edition database whose indices fragment very quickly.
We use a maintenance plan that reorganizes those indexes daily, and rebuilds them weekly. The rebuild ...
- 361
6
votes
2
answers
1k
views
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: https://...
- 201
6
votes
1
answer
614
views
Online index rebuild higher fragmentation on intermediate level
I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on ...
- 1,994
5
votes
3
answers
584
views
How likely is fragmentation on a table with 40000 products likely to affect performance
So database administration is not a strong point of mine, so I have a few questions.
This all relates to the performance of a website I'm looking at, and have spent a fair bit of time researching/...
- 165
5
votes
2
answers
2k
views
Indexes getting highly fragmented during normal usage of system
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.
Currently ...
- 51
5
votes
1
answer
316
views
RCSI and index maintenance
I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember. My understanding is that if we were to rebuild the indexes ...
- 194
5
votes
2
answers
196
views
Best way to configure and maintain a table with many insertions and deletions that's empy on average?
There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no ...
- 615
5
votes
4
answers
3k
views
Can Fill Factor be changed on specific tables without index rebuild?
I've been digging around without any luck.
SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table?
For example, if ...
- 323
5
votes
2
answers
2k
views
How to find the specific file in a filegroup in which an object resides
Today, I encountered below error on one my SQL Server which I manage.
Could not allocate space for object '%.*ls'%.*ls in database '%.*ls'
because the '%.*ls' filegroup is full. Create disk ...
- 1,908
5
votes
1
answer
5k
views
PostgreSQL database size doesn't match used space on disk
I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which ...
- 1,040
5
votes
1
answer
1k
views
Influence of index rebuild tasks in tempdb on the transaction log?
I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before...
The goal is to ...
- 4,787
4
votes
2
answers
2k
views
Why does an index rebuild requires a Sch-M lock?
I don't see why an index rebuild would require a Sch-M lock on the given index.
- 219
4
votes
1
answer
729
views
ALTER INDEX REORGANIZE questions
I have few tables with indexes that are highly fragmented (over 90% fragmentation) and are big enough (tens of gigabytes) in size. Goal of defragmenting those is to reduce space consumption by above ...
- 5,589
4
votes
2
answers
3k
views
Columnstore Index maintenance and finding fragmentation
We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a ...
- 75
4
votes
2
answers
978
views
SQL Server Index and Statistics Maintenance performance question
I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours,...
4
votes
1
answer
1k
views
Heap having high fragmentation
Does high fragmentation on a heap impact query performance?
Can I rebuild the heap with ALTER TABLE REBUILD to get rid of the fragmentation?
This is a staging table used for reporting. It does have ...
- 105
3
votes
2
answers
251
views
How do I use the smallest amount database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit
How do I use the smallest amount of database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit
I have a database file size of 7GB but I have two indexes that are on ...
- 33
3
votes
1
answer
628
views
Filter Schema In Index Optimize Script
Is it possible to do index optimization with Ola's scripts and have it filter out a specific schema?
- 31
3
votes
1
answer
250
views
Reducing Table Size after Clearing TEXT Column Precautions
I have 3rd part app with a table that's 450GB which contains file attachments in a TEXT column (yeah, I know). With a document retention angle, I've exported about 35% of the older attachments to ...
- 31
3
votes
1
answer
3k
views
Do we have to run auto update statistics after index rebuild?
I want to rebuild one of my indexes because fragmentation has reached 97 percent.
I want to know if after I rebuild my index, do I have to update the statistics as well? My auto update statistics ...
- 227
3
votes
1
answer
1k
views
nvarchar(max) in Included Columns of NC index
Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?
By "rebuild issues," I mean taking a very long ...
- 87
3
votes
1
answer
2k
views
When do Azure SQL Database index rebuilds occur?
Azure backups occur every few minutes as per the documentation for Automated backups:
SQL Database supports self-service for point-in-time restore (PITR) by automatically creating full backup, ...
user180724
3
votes
1
answer
6k
views
Could not allocate space for object in database because the Filegroup is full
Today, I was hit by the below error on my SQL Server DB having multiple files in multiple filegroups.
Event ID: 1105. Could not allocate space for object in database
because the 'PRIMARY' ...
- 1,908
3
votes
1
answer
282
views
Diagnosing a maintenance plan that's ballooned in execution time
Environment is SQL 2008 R2, 26GB RAM, 4-core Xeon E5-2650 @ 2.6GHz.
Please see the image below, our maintenance plan which reorganises indexes (not rebuilds). This is run on a weekly basis, and ...
- 167
2
votes
2
answers
4k
views
Scheduling index rebuild jobs
I have a MS-SQL 2014 SP1 - two node AlwaysOn AG (One Primary and an read-only secondary) with about 10 databases sizing 20-30GB in total. I have some queries regarding scheduling index rebuild and ...
- 1,896
2
votes
1
answer
710
views
Stats update during Index rebuild
I have a task in hand to move rebuild a big table to move LOB pages to a different filegroup on SQL Server 2017 Enterprise Edition.
I was testing the scripts in a proof-of-concept environment, and I ...
2
votes
3
answers
3k
views
Index Maintenance for varbinary
We have a SQL 2016 database that has a 1.9bn row table which has a varbinary(255) column which we use to store the HashBytes of an nvarchar(2000) field in the same table.
We have a Non-Clustered ...
- 326
2
votes
2
answers
1k
views
Improve performance of index reorganize on volatile table
This is occurring on SQL Server 2016 (Enterprise).
Example Table structure:
CREATE TABLE [dbo].[Foo](
[FooId] [bigint] IDENTITY(1,1) NOT NULL,
[FirstFkId] [bigint] NULL,
[SecondFkId] [...
2
votes
1
answer
200
views
Ola's maintenance solution is not processing sysjob* tables
I have used Ola Hallengren's script to back up and maintain databases.
However, it does not perform any reindex or reorganize operations against certain tables in msdb, such as sysjobhistory, even ...
- 449
2
votes
2
answers
274
views
SQL Server 2012 Standard Edition rebuild index
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 ...
Jacky.Chueh
2
votes
3
answers
8k
views
SQL Server Agent: QUOTED_IDENTIFIER
The scenario is rather simple: A Job fails in production, giving off an error "ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' ...".
The question is, ...
- 1,813
2
votes
2
answers
923
views
Online & Offline Index Rebuild
I had this weird case today.
Inside a database I had about 50 (1000+ pages, most of them 50000+ pages) tables which had their indexes fragmented above 70% even though I am running a nightly rebuild ...
- 1,752
2
votes
1
answer
3k
views
how often to run reorganize index & update statistic maintenance task on sql server 2012?
I know this is a hard question to answer without more details, but I am hoping for some general guidelines or rule of thumb ideas, or perhaps a how-to determine what would be best for my set of ...
- 1,005
2
votes
1
answer
167
views
SQL Server data file creates 50% unallocated bloat when I create clustered index
My company has relatively large datasets with clustered indexes currently being built on a char(40) field. In order to maintain disk space and streamlining backups, I currently have a "one table ...
- 23
2
votes
1
answer
2k
views
Error when using WITH (DROP_EXISTING = ON)
Okay so I've been condensing files off of some tiny files and moving them onto an FG that I created for these miscellaneous tables. I've been moving these tables by recreating the indexes with the ...
- 3,511
2
votes
1
answer
583
views
Index Maintenance for DW- DB ( ETL process )
I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business ...
user141153