Skip to main content

Questions tagged [index-maintenance]

Maintaining the physical storage of indexes

Filter by
Sorted by
Tagged with
1 vote
1 answer
44 views

How to check the index REORGANIZE progress

I have some query that till now helped me to check index rebuild progress but it doesn't show the REORGANIZE progress. I mean reorganizing index (at this case clustered) like this: ALTER INDEX ...
Maria Benich's user avatar
-1 votes
1 answer
99 views

Redo thread getting blocked while capturing index physical stats

Noticed REDO thread getting blocked for the whole period of running following query, what could be the reason? It is on SQL Server 2019 CU21, The blocking was for almost more than 1hr+, for the entire ...
S.D.'s user avatar
  • 746
3 votes
2 answers
262 views

Is automatically renaming indexes when renaming columns relevant for database health/maintenance?

I am currently building out a few features for a project where customers are able to change up their database schema via an UI and we handle all the nitty-gritty details on running the required ...
Firat Oezcan's user avatar
1 vote
1 answer
247 views

Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?

I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I ...
Wayne Cochran's user avatar
2 votes
2 answers
439 views

What happens if you stop the IndexOptimize job, because it is running long and causing blocking?

When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and ...
veena ranganath's user avatar
0 votes
1 answer
216 views

Why does a SELECT query (Sch-S) get blocked due to an existing Sch-S lock held by index reorganize command?

SELECT query requires a SCH-S lock. This can be shared with other read queries. UPDATE query requires a SCH-M lock. This cannot be shared with other read/update queries. Link: https://www.mssqltips....
variable's user avatar
  • 3,566
0 votes
1 answer
70 views

Index maintenance in distributed availability group

I'd like your opinion about Index Maintenance in Distributed availability groups. In this way, the size of the databases is at the limit of terabytes, and this process is supposed to be transferred to ...
Ali varzeshi's user avatar
2 votes
2 answers
305 views

SQL Server 2012 Standard Edition rebuild index

We use SQL Server 2012 Standard Edition. Is there a way to rebuild the index, trying not to cause data table locking, and reducing the impact on performance? When we rebuild the index, it causes the ...
user avatar
2 votes
1 answer
738 views

Stats update during Index rebuild

I have a task in hand to move rebuild a big table to move LOB pages to a different filegroup on SQL Server 2017 Enterprise Edition. I was testing the scripts in a proof-of-concept environment, and I ...
udhayan dharmalingam's user avatar
0 votes
2 answers
1k views

AG secondary is out of sync for hours after index maintenance

We have a 3TB OLTP database running Siemens Opcenter MES software at one of our manufacturing plants in China. This is setup in an Always-On Availability group with one secondary node. As this is a ...
Zack Soderquist's user avatar
0 votes
0 answers
59 views

Index reorg question

Database table is defined using FillFactor=0 and that was not changed since database deployment several years ago. FillFactor=0 is also a server setting and maintenance job is running every night. ...
Mainecoon21's user avatar
1 vote
1 answer
390 views

Deadlock with Reorganize Index

I was running alter index reorganize on some columnstore indexes in SQL Server 2016 and got a deadlock message. Will reorganize always be the victim? Because I do not want to cause other jobs to fail ...
xhr489's user avatar
  • 807
2 votes
1 answer
186 views

SQL Server data file creates 50% unallocated bloat when I create clustered index

My company has relatively large datasets with clustered indexes currently being built on a char(40) field. In order to maintain disk space and streamlining backups, I currently have a "one table ...
oldemanry23's user avatar
3 votes
1 answer
257 views

Reducing Table Size after Clearing TEXT Column Precautions

I have 3rd part app with a table that's 450GB which contains file attachments in a TEXT column (yeah, I know). With a document retention angle, I've exported about 35% of the older attachments to ...
JordanT's user avatar
  • 31
0 votes
2 answers
431 views

Does sp_BlitzIndex uses Query Store data?

SQL Server 2019 When running sp_BlitzIndex does the result generated is using Query Store data? Trying to understand if Query Store affects sp_BlitzIndex report. sp_BlitzIndex has multiple ...
Ben's user avatar
  • 111
-6 votes
1 answer
205 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 ...
variable's user avatar
  • 3,566
0 votes
1 answer
133 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?
variable's user avatar
  • 3,566
-1 votes
2 answers
296 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 ...
variable's user avatar
  • 3,566
0 votes
1 answer
429 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(...
abi ram's user avatar
-1 votes
1 answer
51 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 ...
variable's user avatar
  • 3,566
0 votes
0 answers
38 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 ...
Steve's user avatar
  • 1
4 votes
1 answer
775 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 ...
Aleksey Vitsko's user avatar
-1 votes
1 answer
114 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%...
user79284's user avatar
2 votes
2 answers
1k 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] [...
Preben Huybrechts's user avatar
1 vote
1 answer
269 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 ...
user1673665's user avatar
1 vote
2 answers
343 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 ...
Danielle Paquette-Harvey's user avatar
1 vote
2 answers
965 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 ...
Mahen's user avatar
  • 13
6 votes
2 answers
1k 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://...
Eoin Campbell's user avatar
0 votes
1 answer
1k 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 ...
RDSago's user avatar
  • 1
9 votes
3 answers
2k 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 ...
Clement's user avatar
  • 536
1 vote
1 answer
159 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 ...
bn25's user avatar
  • 53
1 vote
0 answers
891 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 ...
arthur's user avatar
  • 888
0 votes
1 answer
94 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 ...
JohnG's user avatar
  • 1,081
-1 votes
1 answer
629 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 ...
RonJohn's user avatar
  • 647
3 votes
2 answers
251 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 ...
craigster's user avatar
0 votes
1 answer
3k 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 ...
J.D.'s user avatar
  • 38.1k
4 votes
2 answers
3k 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 ...
Jane Dilbert's user avatar
0 votes
1 answer
493 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 (...
Tom.Wheater's user avatar
1 vote
2 answers
342 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 ...
Data Dill's user avatar
  • 245
12 votes
3 answers
2k 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 ...
Martin Bergström's user avatar
5 votes
3 answers
588 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/...
chris c's user avatar
  • 165
8 votes
1 answer
961 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 ...
user9516827's user avatar
  • 1,315
5 votes
2 answers
210 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 ...
Triynko's user avatar
  • 615
5 votes
2 answers
2k 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 ...
Michal's user avatar
  • 51
3 votes
1 answer
2k 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
348 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 ...
Jim's user avatar
  • 245
1 vote
1 answer
277 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 ...
deep kachhawa's user avatar
4 votes
2 answers
1k 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,...
Joerg Rueschenschmidt's user avatar
1 vote
1 answer
2k 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?
Geezer's user avatar
  • 513
0 votes
0 answers
469 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......
anon's user avatar
  • 45