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
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
33
votes
4
answers
70k
views
What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?
I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:
delete rows and insert new ones, or
update already existing rows
For me it ...
- 2,433
16
votes
2
answers
5k
views
Can SQL Server system tables be defragmented?
We have several databases in which a large number of tables are created and dropped. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that ...
- 8,407
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
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
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
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 ...
- 4,268
11
votes
2
answers
6k
views
How to prevent daily index fragmentation of 99%
I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
- 1,027
10
votes
2
answers
9k
views
REBUILD - Clustered Index, TABLE, or both?
I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.
I have a very large table that we had to add a column to. The clustered index is ...
- 18k
10
votes
2
answers
358
views
Index fragmentation while continuously processing
SQL Server 2005
I need to be able to continuously process about 350M records in a 900M record table. The query I'm using to select the records to process becomes badly fragmented as I process and I ...
- 101
9
votes
2
answers
8k
views
Can rebuilding indexes cause worse performance after the rebuild is finished?
We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is ...
- 199
9
votes
1
answer
1k
views
B-tree node split strategy in SQL Server for monotonically increasing value
Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% ...
- 591
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
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 ...
- 1,335
8
votes
2
answers
2k
views
Defragmentation - Rebuilding Indexes SQL Server 2005
I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for:
ALTER INDEX ALL ON mytablename
REBUILD WITH(ONLINE = ON)
When I pull the info from sys....
- 209
7
votes
4
answers
4k
views
How does SQL Server reduce index fragmentation?
If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes?
MSDN suggests that if an index is over 30% fragmented, it is recommended to run ...
- 729
7
votes
2
answers
15k
views
SQL Server clustered index high fragmentation
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.
Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
- 173
7
votes
1
answer
1k
views
How to set SQL Server index pages per fragment?
I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx)
The ...
- 171
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 ...
- 241
7
votes
1
answer
2k
views
Ola Hallengren Index Script not Reindexing
First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on.
I have Ola's script set as
@Databases nvarchar(max)
,@...
- 93
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
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 ...
- 4,797
7
votes
0
answers
59
views
Is Logical Fragmentation Meaningful in the age of huge SANs? [duplicate]
So, every time I read that a data file shrink will cause fragmentation or a developer rave on that the 95% frag he sees is the cause of all life's misery, I wonder what the problem really is. We no ...
- 1,437
7
votes
2
answers
2k
views
Different results rebuilding an index online and offline
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run ...
- 173
6
votes
2
answers
8k
views
What is "Index Fragmentation Percent" a percentage of?
I am familiar with the concept of index fragmentation and how to fix it, but I'm not really sure of one specific item.
When you look at index fragmentation, it's always reported in terms of ...
- 2,236
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
6
votes
3
answers
1k
views
SQL Azure Fragmentation / Database Size
I have a database running on SQL Azure that is currently 280mb. It is a test database for a system that we're going to be putting into production soon so the data frequently gets deleted in batches ...
- 683
6
votes
2
answers
3k
views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
- 61
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/...
- 165
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 ...
- 51
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
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 ...
- 2,631
5
votes
2
answers
377
views
Shrink or no Shrink dilemma
I have databases that I am tasked to move two databases DBName1 and DBName2 to SSD and I only have limited space on the SSD to fit these databases.I do not want to shrink log or data files for the ...
- 177
5
votes
1
answer
837
views
I have Fragmented Index on a empty table!
I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
- 3,694
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
4
votes
2
answers
4k
views
How can an identity primary key index become fragmented?
From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.
Example:
ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
- 227
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 ...
4
votes
1
answer
1k
views
Fragmentation Level for Heaps
I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
- 3,904
4
votes
2
answers
3k
views
Join on dm_db_index_physical_stats very slow
I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and ...
- 41
4
votes
1
answer
1k
views
Datetime Clustered Index Keeps getting fragmented
I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a ...
- 43
4
votes
1
answer
277
views
Huge Heap Table and table compression on SQL Server 2016
My database size is huge, and recently I noticed that a new table added few months ago is the culprit.
Here is the table script.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]....
- 1,335
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
4
votes
1
answer
2k
views
Index fragmentation increase as more row being deleted
I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low.
This table has high transaction throughput. ...
- 729
4
votes
3
answers
7k
views
ONLINE vs OFFLINE index rebuilds
I am looking for an opinion, which I know is usually frowned upon, so my apologies in advance.
I'm currently in an environment where every instance is SQL Server enterprise edition.
The maintenance ...
- 2,631
4
votes
2
answers
4k
views
Oracle: deleting data from tables leads to data file fragmentation?
When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?
- 41
4
votes
2
answers
2k
views
Why index REBUILD does not reduce index fragmentatation with a page count > 1000?
This is kind of a followup to the existing question on this site "Why index REBUILD does not reduce index fragmentatation?". The accepted answer to that question, and all other resources I have found ...
- 1,045
4
votes
1
answer
4k
views
Is high index fragmentation an issue on performance
Below is a small sample (top 20ish) from over 8000 indexes in avg_fragmentation_in_percent descending order. Will this cause any performance issues?
- 219
4
votes
1
answer
497
views
How do databases deal with file system fragmentation?
Especially analytic databases that try to optimize for queries that scan large portions of tables instead of randomly accessing specific rows. For example Redshift has a concept of a sort key that, ...
- 641
4
votes
1
answer
3k
views
How can I *actually* defragment my InnoDB tables?
After a recommendation by mysqltuner I've run mysqlcheck --optimize --all-databases successfully (all of the tables reported "note : Table does not support optimize, doing recreate + analyze instead" ...
- 403