Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [index-maintenance]

Maintaining the physical storage of indexes

5
votes
1answer
61 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 ...
0
votes
1answer
34 views

SQL Server hangs on index rebuild, works well after SQL service restart

We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2. SQL Server is limited to 64GB of memory. We are using Ola Hallengren scripts for index rebuilding and optimizations once per week ...
1
vote
1answer
55 views

Index rebuild at partition level on a table

We have functionality in SQL Server to rebuild indexes at partition level using below command ALTER INDEX IX_name ON table REBUILD PARTITION=68 In non-partitioned table we disable the indexes, load ...
6
votes
1answer
174 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 ...
0
votes
1answer
45 views

Daily Index Blocking

There is ONE index rebuild which everyday is blocked causing the job to fail. It is the same index and same stored procedure which blocks. We used an automated stored procedure to rebuild or reorg ...
0
votes
0answers
15 views

Does rebuilding and reorganizing indexes yield less fruitful results on tables that don't have clustered indexes?

I understand the operations of rebuilding and reorganizing indexes at a high level, but was wondering what they are actually doing on a table that doesn't have a clustered index (i.e. a table stored ...
2
votes
1answer
35 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 ...
1
vote
1answer
35 views

How to select info about indexes with specified columns from all databases in the instance using TSQL?

This query selects all the indexes - its names, fragmentation, and some other parameters from the current database. select idx.[name] as [index_name], sc.[name] as [schema_name], obj.[...
0
votes
1answer
105 views

Ola Hallengren Index Maintenance with compressed indexes

If the Ola Hallengren index maintenance solution is installed onto a server which already has compression applied to several of the indexes, will the script respect the current compression level when ...
1
vote
1answer
128 views

Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed. The statement has been ...
1
vote
1answer
107 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 ...
0
votes
1answer
65 views

How to control blocking sessions while an index maintenance job runs?

I would like to know, is there any possibility to control the locks while Ola's index optimize job is running? I have implemented Ola Hallengren's index optimize job and am running it every day, but ...
1
vote
2answers
168 views

Required free Disk Space for Index rebuild offline Operations

I have SQL Server 2017 Standard Editon with two Databases. The current total index size of Database1 is 111 GB and for Database2 is 122 GB. Since I'm on Standard Edition I can only rebuild the ...
3
votes
1answer
81 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?
0
votes
0answers
152 views

Reorganize index maintenance job is more than 20 times slower

I have a reorganize maintenance job which runs daily (I know, this have been altered to weekly) which for the majority of executions it takes around 2 hours however over the weekend has failed with ...
4
votes
2answers
445 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 ...
0
votes
0answers
55 views

Can we maintain stored procedures on a central location and execute them against multiple SQL Server instances?

The company I am working for does not have maintenance solutions implemented so far. I have started two weeks ago and directly suggested them to use Ola Hallengren's scripts. However, they refuse ...
4
votes
0answers
117 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 ...
0
votes
1answer
211 views

Index rebuild suspended due to IO_COMPLETION wait type

I am executing the rebuild index script in SSMS to rebuild a specific primary key index of a major table in our database. It has been running for 13 hours now. sp_who2 shows me that it was suspended ...
7
votes
1answer
282 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 ...
0
votes
1answer
274 views

Does SSD drive make indexes rebuild and update stats useless with SQL Server?

Now that SSD is a standard de-facto. Can the maintenance job of rebuilding indexes and updating statistics, using Ola Hallengren https://ola.hallengren.com/ jobs for instance, be done only on demand ...
2
votes
1answer
177 views

How to best manage Index Builds with Stats Updates?

This is a follow-up to what I read on Is there any reason to stop transaction log backups during a maintenance window? in the answer proposed by sp_BlitzErik. I use Ola Hallengren's indexing script ...
1
vote
1answer
207 views

Problems with a job that rebuilds indexes [closed]

We have a Maintenance Plan (MP) job that rebuilds indexes every week. The last time, the job failed because of a deadlock. Should I rerun the whole job, or do I just need to rerun the rebuild of the ...
2
votes
3answers
101 views

Dumps after CHECKDB error

I have a little problem with a two-node mirror and customer requirements: There is a maintenance window every night from 01:30 to 03:00, within which I have to run rebuild/reorg and checkdb (...
2
votes
2answers
165 views

Index Maintenance on Indexed View using Ola Hallengren Script

I’m using Ola Hallengren's below script in my environment and it fails with the error: USE DBA EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’, @FragmentationLow = NULL, @...
4
votes
1answer
105 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 ...
1
vote
1answer
305 views

Removing InnoDB indexes causes database table almost not accessible and slow [closed]

I have a server with 32GB RAM. I have given almost 70% InnoDB buffer pool to it. I have table with almost 40 million rows. Recently, I tried to remove one of the indexes and replace it with another. ...
3
votes
4answers
1k 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 ...
3
votes
2answers
416 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
vote
1answer
135 views

Index Reorgs Blocking Statistics Updates Blocking Inserts

After upgrading from SQL Server 2014 to SQL Server 2016 last month, we are seeing statistics updates on a table being blocked for hours while a table’s indexes are being reorganized. Inserts are in ...
1
vote
1answer
105 views

Alter Index within a Script

I've been reading about SQL Server Index Fragmentation and how to rebuild/reorganize. I'm curious on how to write a script about it. I have a script that will generate the table with the table index ...
4
votes
1answer
3k 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' ...
3
votes
1answer
660 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 ...
1
vote
1answer
321 views

Rebuild Index Task options [closed]

I have to debug a performance issue on a SQL Server 2016 database. The query was pretty simple - a few temporary tables joined with SQL tables and aggregation. The amount of rows of involved in ...
2
votes
3answers
815 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 ...
0
votes
1answer
1k views

RUNSTATS vs REORG

What is the major differences between: RUNSTATS ON TABLE 'mytable' WITH DISTRIBUTION AND DETAILED INDEXES ALL REORG INDEXES FOR 'MYTABLE'
5
votes
1answer
1k 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 ...
2
votes
1answer
2k 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 ...
0
votes
1answer
525 views

Index maintenance Azure SQL

I have installed Ola's scripts: https://ola.hallengren.com/scripts/CommandLog.sql https://ola.hallengren.com/scripts/CommandExecute.sql https://ola.hallengren.com/scripts/IndexOptimize.sql and ran: ...
0
votes
1answer
127 views

Rebuild or Reorganise on 6TB database

A have a 6 TB database. Index rebuilds or reorganizations take more than two days and it blocks all incoming connections. Right now I'm using Ola's script. The problems: Index fragmentation will be ...
-1
votes
1answer
244 views

Space requirements for Index maintenance

I want to understand space requirements for index maintenance in SQL Server. Basically, what I am trying to find is below: Offline index operation with SORT_IN_TEMPDB = ON Space required in User DB ...
2
votes
2answers
2k 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 ...
0
votes
1answer
389 views

Index Reorganize/ Rebuild Time

I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this ...
5
votes
2answers
995 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 ...
0
votes
0answers
84 views

Creating clustered primary key index with ONLINE option

I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns from which it is composed I need to re-...
7
votes
1answer
4k 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 ...
4
votes
5answers
4k 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%...
5
votes
1answer
357 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 ...
2
votes
1answer
99 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 ...
8
votes
2answers
6k 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 ...