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.
15
questions
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 ' + ...
- 2,707
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 ...
- 5,353
12
votes
3
answers
9k
views
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'...
- 783
3
votes
2
answers
13k
views
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 ...
- 55
13
votes
1
answer
20k
views
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 ...
- 331
12
votes
2
answers
18k
views
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 ...
- 241
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%...
- 4,797
7
votes
1
answer
11k
views
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 ...
- 821
6
votes
2
answers
2k
views
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 ...
- 115
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 ...
- 7,340
4
votes
3
answers
5k
views
SQL Server Index Fragmentation
When should you rebuild an index? vs reorganize?
What index fragmentation value is considered healthy?
Jason
2
votes
2
answers
2k
views
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 ...
- 304
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:
...
- 381
1
vote
1
answer
770
views
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,
i.name AS IndexName,
s.index_type_desc,
...
- 26.6k
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 ...
- 113