Questions tagged [index-maintenance]
Maintaining the physical storage of indexes
2
votes
1answer
47 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, ...
-1
votes
0answers
27 views
Rebuild index with MAXDOP
On a server with MAXDOP set to 0: Database 4TB with around 1TB of indexes.
What behavior can I expect if my maintainece of index is occuring for threshold greater than 60 % wrt index rebuild?
On ...
2
votes
3answers
44 views
SQL Server Index Maintenance-Conditional Rebuilds
I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure ...
1
vote
1answer
28 views
Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server
I am working on a report which will give information for different segments like
blocking process
slow running queries
disk space etc.
I also want to add one more segment where I can get ...
1
vote
1answer
58 views
Azure SQL Database and Database Maintenance
If SQL Agent isn't available for Azure SQL Database then is it possible to maintain (indexes, statistics etc) the databases with Ola Hallengren's maintenance scripts, for example?
0
votes
0answers
31 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
479 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
110 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
95 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 ...
1
vote
1answer
165 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
189 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
343 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
19 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
141 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
41 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
174 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
260 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
150 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
78 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 ...
2
votes
0answers
132 views
Index rebuild blocks itself and runs in serializable isolation level
I’ve implemented Ola Hallengren maintenance solution in one database, and I’m noticing that it’s getting blocked by itself, I don’t know why honestly.
What I have noticed, is that it seems to be run ...
1
vote
2answers
385 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
130 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
219 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
1k 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 ...
5
votes
0answers
191 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
372 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
438 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
462 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
230 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 ...
2
votes
1answer
471 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
131 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
195 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,
@...
3
votes
1answer
119 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
555 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. ...
4
votes
4answers
2k 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 ...
2
votes
2answers
489 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
149 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
107 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 ...
3
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
852 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
371 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
1k 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
2k 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
560 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:
...
-1
votes
1answer
361 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
538 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 ...
