Questions tagged [index-maintenance]
Maintaining the physical storage of indexes
0
votes
0answers
24 views
SQL Server - Cannot reorganize index on “extended_index” table
I have a maintenance plan that includes an index reorganization step. I am getting the following error in the job history:
Executing the query "ALTER INDEX [SpatialIndex-20180705-165942] ON [dbo......
4
votes
2answers
412 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.
1
vote
1answer
73 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 ...
5
votes
1answer
75 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
53 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
104 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
209 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
16 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
71 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
38 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
127 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
181 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
118 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
71 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
251 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
98 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
172 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
690 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 ...
4
votes
0answers
147 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
272 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
354 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
324 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
204 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
292 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
110 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
179 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
110 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
363 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
442 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
138 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
106 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
4k 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
725 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
342 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
906 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
541 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
144 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
279 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
439 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
1k 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
101 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-...
6
votes
1answer
5k 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 ...
5
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
410 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 ...
