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.

Filter by
Sorted by
Tagged with
44 votes
4 answers

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 ' + ...
Justin Dearing's user avatar
33 votes
3 answers

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 ...
jrara's user avatar
  • 5,333
12 votes
3 answers

PostgreSQL Initial Database Size

There are 2 parts to my question. Is there a way of specifying the initial size of a database in PostgreSQL? If there isn't, how do you deal with fragmentation when the database grows over time? I'...
CadentOrange's user avatar
3 votes
2 answers

How can I defragment tables in MySQL?

I have a database named COUNTY. COUNTY has a fragmented table GEO. I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the error "Table does not support optimize, doing ...
Krunal Patel's user avatar
12 votes
2 answers

MySQL indexes maintenance

I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries. I am familiar with that formula that calculates the ...
Nicolas's user avatar
  • 241
12 votes
1 answer

How to lower HEAP Fragmentation in SQL Server?

i recently found out that one heap table had more than 70% fragmentation. So i decided to do a ALTER TABLE dbo.myTable REBUILD Funny enough, afterwards i had 20% fragmentation. There was no write ...
tuxmania's user avatar
  • 321
8 votes
6 answers

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%...
Magier's user avatar
  • 4,757
7 votes
1 answer

Cancelling / Stopping ALTER INDEX REORGANIZE

ALTER INDEX [myIndex] ON [dbo].[myTable] REORGANIZE WITH ( LOB_COMPACTION = ON ) I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it ...
Ofiris's user avatar
  • 811
6 votes
2 answers

Determining what objects SHRINKDATABASE will free space from

First off, yes, shrink is bad. Don't use it, ever. I understand. Unfortunately, it's kind of SOP and I'm not in a position to argue since it's usually a stopgap until we can get a maintenance window ...
Drew's user avatar
  • 115
5 votes
1 answer

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 ...
usr's user avatar
  • 7,310
4 votes
3 answers

SQL Server Index Fragmentation

When should you rebuild an index? vs reorganize? What index fragmentation value is considered healthy?
user avatar
2 votes
2 answers

avg_page_space_used_in_percent and fill factor

sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent. How does this fit in with the fill factor of an index? For example: I have index idx_HeaderInfo I create the index ...
Hpk's user avatar
  • 194
1 vote
1 answer

dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons?

SQL Server 2008 RTM (10.0.1600.22) Running a typical index fragmentation report on a table: SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, AS IndexName, s.index_type_desc, ...
mustaccio's user avatar
  • 25.1k
1 vote
1 answer

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: ...
Endy Tjahjono's user avatar
1 vote
1 answer

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 ...
Nick Patsaris's user avatar