Questions tagged [index-maintenance]
Maintaining the physical storage of indexes
106
questions
1
vote
1
answer
64
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 ...
2
votes
2
answers
129
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 ...
0
votes
1
answer
131
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....
- 3,430
0
votes
1
answer
52
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 ...
2
votes
2
answers
212
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 ...
Jacky.Chueh
2
votes
1
answer
611
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 ...
0
votes
2
answers
752
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 ...
0
votes
0
answers
56
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. ...
- 171
1
vote
1
answer
261
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 ...
- 785
2
votes
1
answer
136
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 ...
- 23
3
votes
1
answer
230
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 ...
- 31
0
votes
2
answers
396
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 ...
- 111
-6
votes
1
answer
170
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 ...
- 3,430
0
votes
1
answer
115
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?
- 3,430
-1
votes
2
answers
228
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 ...
- 3,430
0
votes
1
answer
329
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
49
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 ...
- 3,430
0
votes
0
answers
37
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
665
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 ...
- 5,472
-1
votes
1
answer
88
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%...
2
votes
2
answers
976
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
239
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
279
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,999
1
vote
2
answers
805
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
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://...
- 201
0
votes
1
answer
953
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
7
votes
3
answers
1k
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 ...
- 486
1
vote
1
answer
155
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
1
vote
0
answers
728
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 ...
- 868
0
votes
1
answer
88
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,071
-1
votes
1
answer
528
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 ...
- 606
3
votes
2
answers
250
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
2k
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 ...
- 35.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 ...
- 75
0
votes
1
answer
423
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
305
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 ...
- 235
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 ...
- 123
5
votes
3
answers
579
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
823
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,295
5
votes
2
answers
182
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 ...
- 615
4
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 ...
- 41
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, ...
user180724
2
votes
3
answers
342
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
247
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
874
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
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?
- 441
0
votes
0
answers
414
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
2k
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.
- 219
3
votes
1
answer
1k
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
300
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