Questions tagged [index-maintenance]
Maintaining the physical storage of indexes
95
questions
-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 ...
- 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?
- 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 ...
- 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(...
- 1
-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 ...
- 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 ...
- 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 ...
- 4,408
-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%...
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`...
- 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] [...
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 ...
- 123
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 ...
- 1,939
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 ...
- 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://...
- 201
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 33
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 ...
- 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 ...
- 75
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 (...
- 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 ...
- 81
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 ...
- 123
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/...
- 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 ...
- 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 ...
- 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 ...
- 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, ...
user180724
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 ...
- 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 ...
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,...
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?
- 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......
- 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.
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 11
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 ...
- 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.[...
user159374
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 ...
- 13
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 ...
- 14.8k
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 ...
- 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 ...
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 ...
- 412