Questions tagged [index-maintenance]

Maintaining the physical storage of indexes

Filter by
Sorted by
Tagged with
-6 votes
1 answer
52 views

Does offline index rebuild of clustered or non-clustered index block user's SELECT/UPDATE/INSERT/DELETE queries?

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M). Does an offline index rebuild of clustered ...
user avatar
  • 2,145
0 votes
1 answer
21 views

Index rebuild/reorganize fragmentation percentage mean the internal (inside the page) or external (amongst pages)fragmentation?

Fragmentation of 5% - reorganize Fragmentation of 30% - rebuild Index rebuild/reorganize fragmentation percentage mean the internal (inside the page) or external (amongst pages)fragmentation?
user avatar
  • 2,145
-1 votes
2 answers
23 views

What other than `delete and re-creation of the index` happens when an index is rebuilt which does not happen in the reorganize command?

When an index reorganize command is fired, then it will not drop and re-create the index. However just like a rebuild it will defragment the data and ensure that the physical ordering of the page is ...
user avatar
  • 2,145
0 votes
1 answer
53 views

Order of execution in Ola Index maintenance and Stats collection

I was thinking of performing The index maintenance and stats collection jobs in the below order for all databases in an Instance. step 1.Perform all index maintenance activity for all required Indexes(...
user avatar
-1 votes
1 answer
38 views

Which dmv can help identify the cost of index maintenance for exisitng and missing indexes?

I want to identity: Indexes that are costly to maintain. Missing indexes along with their maintenance cost. By maintenance I mean - the index maintenance during insert/update/delete. Which dmv can ...
user avatar
  • 2,145
0 votes
0 answers
23 views

Running Index defragmentation with SQL Job (looks like every night!)

After running sp_blitzcache (over time) I have found that everyday my plan cache is compiling new execution plans. 100% of my plan cache is built within the last 24 hours (each day). I think these ...
user avatar
  • 1
4 votes
1 answer
321 views

ALTER INDEX REORGANIZE questions

I have few tables with indexes that are highly fragmented (over 90% fragmentation) and are big enough (tens of gigabytes) in size. Goal of defragmenting those is to reduce space consumption by above ...
user avatar
-1 votes
1 answer
43 views

ALTER INDEX ALL did not fix the problem, but deleting and recreating a non-clustered index does the trick. Why?

My SQL Server version is 2014. The table has over 21 million rows. One of its indexes is a non-clustered index on an integer field that is referenced a lot. I did a mass update that affected about 20%...
user avatar
0 votes
0 answers
615 views

MySQL: message "Incorrect key file for table" "try to repair it"

I'm using MySQL 5.7.10 I have a table like this, with 100M rows and a size of 16GB . CREATE TABLE `my_table` ( `id` DOUBLE NOT NULL AUTO_INCREMENT, `entity_id` DOUBLE NOT NULL, `concept_id`...
user avatar
  • 141
2 votes
2 answers
406 views

Improve performance of index reorganize on volatile table

This is occurring on SQL Server 2016 (Enterprise). Example Table structure: CREATE TABLE [dbo].[Foo]( [FooId] [bigint] IDENTITY(1,1) NOT NULL, [FirstFkId] [bigint] NULL, [SecondFkId] [...
user avatar
1 vote
1 answer
77 views

Questions on updating statistics and index maintenance jobs

Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions: For ...
user avatar
1 vote
2 answers
107 views

Custom index maintenance percentages for a very large table

I'm using Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
user avatar
1 vote
2 answers
354 views

Do frequent DML operations on a table with indexes and huge data affect SELECT query performance?

If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where ...
user avatar
  • 13
6 votes
2 answers
522 views

Identifying Unused Indexes on SQL Azure

I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information. See: https://...
user avatar
0 votes
1 answer
151 views

@TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set

We start our indexing at 10:00 PM every evening and we want to allow it run until 4:30 AM, the next day. Therefore, we set the @timelimit variable / parameter to 23400 seconds. The issue is that the ...
user avatar
  • 1
6 votes
3 answers
609 views

Online Index Rebuild of large table takes exclusive lock

I'm trying to rebuild the clustered index of a large table (77GB) on Azure SQL Database. There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option. This has worked ...
user avatar
  • 446
1 vote
1 answer
86 views

Index maintenance for AG DBs

I wanted to get some opinions on the index maintenance we currently do at the company where I work. One of our production SQL 2012 clusters that consists of 4 nodes, with two instances on each, has ...
user avatar
  • 53
0 votes
0 answers
267 views

pg_repack binaries under windows

We use postgres to manage our data in windows. Most of our installations run 9.x branch of postgres, although all versions of the database software is used (however pretty much all installations run ...
user avatar
  • 820
0 votes
1 answer
67 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 ...
user avatar
  • 1,051
-1 votes
1 answer
240 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 ...
user avatar
  • 455
3 votes
2 answers
228 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 ...
user avatar
0 votes
1 answer
360 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 ...
user avatar
  • 24.2k
4 votes
2 answers
2k 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 ...
user avatar
0 votes
1 answer
219 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 (...
user avatar
  • 163
1 vote
2 answers
176 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 ...
user avatar
12 votes
3 answers
1k 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 ...
user avatar
5 votes
3 answers
547 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/...
user avatar
  • 165
7 votes
1 answer
553 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 ...
user avatar
  • 1,285
5 votes
2 answers
127 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 ...
user avatar
  • 505
4 votes
2 answers
558 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 ...
user avatar
  • 41
3 votes
1 answer
1k 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, ...
user avatar
2 votes
3 answers
280 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 ...
user avatar
  • 245
1 vote
1 answer
147 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 ...
user avatar
4 votes
2 answers
619 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,...
user avatar
1 vote
1 answer
852 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?
user avatar
  • 259
0 votes
0 answers
192 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......
user avatar
  • 45
4 votes
2 answers
1k 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.
user avatar
  • 99
3 votes
1 answer
733 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 ...
user avatar
  • 87
5 votes
1 answer
232 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 ...
user avatar
  • 194
1 vote
2 answers
2k 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 ...
user avatar
  • 111
1 vote
1 answer
1k 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 ...
user avatar
  • 73
6 votes
1 answer
2k 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 ...
user avatar
  • 73
0 votes
1 answer
52 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 ...
user avatar
2 votes
1 answer
961 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 ...
user avatar
  • 3,491
1 vote
1 answer
84 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.[...
user avatar
1 vote
1 answer
499 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 ...
user avatar
1 vote
1 answer
941 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 ...
user avatar
2 votes
1 answer
442 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 ...
user avatar
  • 189
0 votes
1 answer
236 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 ...
user avatar
2 votes
0 answers
198 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 ...
user avatar
  • 412