Questions tagged [fragmentation]
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.
152
questions
5
votes
2
answers
28k
views
Find out when your index was last rebuilt / reorganized
I've recently come across a database, which has very high fragmentation on large tables.
Page count > 1'000'000 with fragmentation 99,...
I would like to know how long this table has gone without a ...
- 16.4k
0
votes
1
answer
59
views
mysql reuse of deleted rows
Using MySql and innodb with innodb_file_per_table flag ON.
We have a table with a size of ~100GB.
We delete the data and insert fresh data (approx same size) every day (the delete is in chunks by a ...
- 78.9k
0
votes
1
answer
83
views
Fragmentation when creating non-clustered index
When I was dealing with fragmentation, I had a question about a non-clustered index. In non-clustered index actual table data (typically stored in a clustered index, otherwise a heap) is stored ...
0
votes
1
answer
1k
views
Index Maintenance - Reorganize After BIG SQL Server Shrink
I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database.
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@...
CommunityBot
- 1
0
votes
1
answer
2k
views
Reclaiming Disk Space for MariaDB Table
I'm facing a disk space issue with a MariaDB database and would appreciate your insights on resolving it.
Here's the scenario:
MariaDB version: 10.3
OS: Ubuntu 20.04
Engine: Innodb
Table name: '...
- 78.9k
0
votes
1
answer
212
views
How can I monitor and address fragmentation on RDS for Oracle database?
I'm planning to migrate my database from on-premises to AWS RDS for Oracle. However, I'm not sure if Amazon RDS supports tools such as Automatic Fragment Advisor or the Automatic Segment Space ...
- 41k
1
vote
2
answers
73
views
Does clustered index fragmentation happen in relational DBMS?
Is it possible that after a certain pattern of random inserts and deletes leaf data nodes become fragmented with clustered index?
I.e, that the physical order does not reflect the logical order (by ...
7
votes
2
answers
11k
views
Is high Extent Fragmentation a problem?
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents ...
33
votes
3
answers
42k
views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
2
votes
2
answers
2k
views
How defragment compressed partitioned table in oracle?
I have partitioned table by day with compressed query high option for partitions in Oracle 11.2 database.
Delete by key and direct insert was used to populate a data for the table. So the table is ...
- 33
1
vote
1
answer
398
views
What is fragmenting my index on a table with stable traffic?
I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's ...
- 2,201
1
vote
3
answers
579
views
What is SQL Server 2000 spid -2?
I have a SQL Server 2000 machine that has a pretty big database on it - almost a terabyte in size.
There's a particular table that has been a source of some production issues and any attempt to try ...
- 2,201
0
votes
1
answer
7k
views
When to rebuild and when to reorganize indexes [duplicate]
I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query :
SELECT
...
- 2,201
0
votes
2
answers
464
views
Should I defragment InnoDB table with fixed-length fields?
I am running MariaDB 10.2.13 on Linux (Debian).
I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain ...
CommunityBot
- 1
2
votes
1
answer
395
views
Does a fragmented database cause the transaction log to grow faster?
Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the ...
- 3,406
3
votes
1
answer
184
views
Is concept of fill factor useful only when index is rebuilt?
Fill factor is concept that works only when creating a new index or when the index is rebuilt.
Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) ...
- 3,406
0
votes
3
answers
689
views
Will I need to fix fragmentation after shrinking a database full of only HEAPs?
I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example ...
1
vote
1
answer
2k
views
Defrag or Backup+Restore
I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime.
I have a plan that I am not sure will work:
...
- 2,201
44
votes
4
answers
57k
views
Why is my database still fragmented after I rebuilt and reindexed everything?
I have a database which I tried to defragment all the tables at once by running this T-SQL:
SELECT
'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
'ALTER INDEX all ON ' + ...
- 593
0
votes
2
answers
193
views
I think SQL Docs are wrong for sys.dm_db_index_physical_stats
Go to this documentation page:
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15
Then go to ...
- 88.3k
1
vote
1
answer
383
views
How does page split result in index fragmentation?
I am trying to understand the reason behind index fragmentation.
Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that ...
- 47.4k
0
votes
0
answers
42
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 ...
3
votes
1
answer
3k
views
Do we have to run auto update statistics after index rebuild?
I want to rebuild one of my indexes because fragmentation has reached 97 percent.
I want to know if after I rebuild my index, do I have to update the statistics as well? My auto update statistics ...
2
votes
1
answer
3k
views
Checking database files for physical fragmentation
I searched the Internet and did not find clear explanation for when I should try defragmenting physical database files for a DB residing on a SAN. I understand that there are tools and procedures ...
- 88.3k
3
votes
2
answers
8k
views
SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors
In our test lab I've been experimenting with different jobs to keep our critical indexes from becoming too fragmented.
I'm currently using the approach described here: sys.dm_db_index_physical_stats ...
CommunityBot
- 1
1
vote
1
answer
162
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 ...
- 29.7k
0
votes
1
answer
29
views
Is twitter's character limit a way they found to avoid drastic database fragmentation? [closed]
so I just started reading a book about "Mongo schema design" and am currently reading the portion about MMAP and fragmentation.
From what I understood there are different ways to avoid ...
- 32.4k
-1
votes
1
answer
7k
views
How do we select fragmented indexes from postgresql?
Need to select indexes which are fragmented for a certain level. Can someone help me with the query for that?
- 54.9k
-1
votes
1
answer
663
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,904
11
votes
1
answer
752
views
Defragmenting SQL Server data and log files live with MoveFile API
My employer is looking at deploying NTFS defragmentation software that uses the Windows MoveFile API to defragment open files. This would get deployed to thousands of SQL Server servers running SQL ...
- 181k
5
votes
1
answer
3k
views
Disadvantages of reorganizing all tables nightly
I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too ...
- 181k
7
votes
4
answers
3k
views
Manually create index fragmentation
Does anybody have a good approach to manually create a specific fragmentation to an index, ideally without increasing the number of rows in the underlying table?
I need this for the purpose of a ...
- 275
3
votes
3
answers
32k
views
Table and Index fragmentation
Is there any way to check whether a table or index has been fragmented?
How to check if table is fragmented or not?
How to check if index is fragmented?
I searched Google but didn't get ...
0
votes
1
answer
100
views
Index Fragmentation - log issues
I have a few questions that I want to understand in order to take informed decision. I am not a DBA by profession, I have done some administrative tasks in the past and now I would have to create a ...
5
votes
1
answer
2k
views
SQL Server - Very high disk activity after running query?
I'm running SQL Server Express 12.0.4100 on an AWS instance with 16 cores and 64 GB of RAM with a 3TB and 9000 IOPS EBS attached. This has been running for 2 years perfectly with no issues until this ...
- 183
1
vote
1
answer
289
views
Is fragmentation at non-leaf levels a problem?
SQL version: 2017 Standard x64
OS: Windows Server 2016 Datacenter (10.0)
I've just created some minimal indexes on ETL tables which were previously heaps with no indexes. Both R and W performance ...
- 88.3k
2
votes
1
answer
418
views
How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?
In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions:
Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation.
What magnitude of number of rows ...
- 47.4k
3
votes
3
answers
14k
views
Query to see Index Fragmentation on all databases without using SP or temp tables
I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as ...
CommunityBot
- 1
3
votes
1
answer
815
views
Do I need to rebuild heap table after rebuilding a non-clustered index?
Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe ...
0
votes
1
answer
520
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 (...
- 5,791
0
votes
1
answer
729
views
MongoDB Fragmentation affect the Performance in WiredTiger
Im using MongoDB 3.6 I have a huge number of fragmentation in my collections. 300+ GB on one collection. WiredTiger is the storage engine.
I know its bad for the OS and much wasted space. This ...
- 10.3k
0
votes
0
answers
144
views
Problem with calculating an approximate space required for online index rebuild
I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
- 3,904
4
votes
4
answers
328
views
Is fragmention limited to indexes in sql server?
As far as I can see with research the only thing that fragments is indexes no other objects in SQL can fragment.
I have to keep track of fragmentation within multiple databases, would it be correct ...
CommunityBot
- 1
-1
votes
1
answer
581
views
Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse [closed]
I have a question about tables and fragmentation in MySQL
My starting point is this question:
How find and fix fragmented MySQL tables
I have a table with 1 million of rows and 128MB of space. This ...
CommunityBot
- 1
8
votes
6
answers
13k
views
Does index rebuild time depend on the fragmentation level?
Is the required time for index rebuild dependent on the level of fragmentation?
Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented 40%...
- 240
8
votes
1
answer
1k
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
3
answers
597
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/...
- 16.4k
0
votes
1
answer
217
views
Not lowering avg fragmentation
I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after ...
- 3,904
5
votes
2
answers
3k
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 ...
- 88.3k
1
vote
1
answer
289
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 ...
- 16.4k