Database Administrators

Questions tagged [index-maintenance]

Ask Question

Maintaining the physical storage of indexes

73 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
1answer
76 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 ...
0
votes
1answer
39 views

IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation

I'm working on a project that requires a database shrink. Following the shrink, I checked sys.dm_db_index_physical_stats and can see a lot of my indexes are over 99% fragmented (...
1
vote
1answer
76 views

Index Fragmentation Monitoring: Can I make this query faster?

I have a query that is run against each database on two of my servers using a PowerShell script to initiate the script and throw the results into a CSV thus the need to have temp tables. Is there a ...
12
votes
3answers
412 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 ...
5
votes
3answers
509 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/...
7
votes
1answer
261 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 ...
5
votes
2answers
92 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 ...
4
votes
2answers
110 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 ...
3
votes
1answer
212 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, ...
2
votes
3answers
82 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
59 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 ...
4
votes
2answers
210 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,...
1
vote
1answer
176 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
74 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
605 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
213 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
132 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
481 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
372 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
478 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 ...
2
votes
1answer
303 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
43 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
272 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
379 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 ...
2
votes
1answer
232 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
83 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
145 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
596 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
180 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
320 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 ...
5
votes
2answers
2k 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
250 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
506 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
713 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 ...
1
vote
1answer
639 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
282 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
701 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
169 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
213 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
139 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
844 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. ...
5
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
577 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
164 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
109 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
5k 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
1k 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
420 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 ...

1 2