Database Administrators

Questions tagged [index-maintenance]

Ask Question

Maintaining the physical storage of indexes

77 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
1answer
22 views

SQL Server online maintenance - False publicity?

I'm being challenged since we have customers that are seeing different types of errors while running what is supposed to be online maintenance. errors like timeouts, scan stopped due to data movement ...
-1
votes
1answer
62 views

How to practically run sys.dm_db_index_physical_stats against 460 THOUSAND tables?

I've been tasked with maintaining an old (SQL 2005; no, I can't upgrade it!) server which has 461,000 tables and 1.15M indexes. How do I go about analyzing all those indexes in bite-sized chunks? My ...
3
votes
2answers
198 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 ...
0
votes
1answer
68 views

How can I check index fragmentation in the quickest way possible? [duplicate]

Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index ...
2
votes
2answers
854 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
67 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
89 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
689 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
514 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
343 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
103 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
180 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
564 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
137 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
73 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
339 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
280 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
112 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
729 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
329 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
157 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
2answers
820 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
599 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
682 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
46 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
451 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
47 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
315 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
533 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
297 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
118 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
165 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
773 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
219 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
380 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 ...
6
votes
2answers
3k 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
279 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
627 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
1k 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
792 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
305 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
809 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
190 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
237 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
153 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
998 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
649 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
177 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
110 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 ...

1
2