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
42
votes
3answers
49k 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 ' + ...
30
votes
3answers
34k 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 ...
27
votes
4answers
48k 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 ...
15
votes
2answers
4k 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 ...
12
votes
3answers
6k 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'...
12
votes
2answers
10k 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 ...
11
votes
1answer
685 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 ...
11
votes
2answers
4k 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%. ...
10
votes
1answer
12k 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 ...
10
votes
2answers
8k 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 ...
10
votes
2answers
332 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 ...
8
votes
6answers
8k 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%...
8
votes
2answers
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....
8
votes
1answer
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% ...
7
votes
4answers
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 ...
7
votes
2answers
6k 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 ...
7
votes
1answer
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 ...
7
votes
1answer
340 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 ...
7
votes
2answers
10k 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 ...
7
votes
1answer
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) ,@...
7
votes
0answers
58 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 ...
7
votes
2answers
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 ...
6
votes
2answers
13k 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 ...
6
votes
3answers
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 ...
6
votes
2answers
2k 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 ...
5
votes
3answers
513 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/...
5
votes
1answer
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 ...
5
votes
1answer
7k 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 ...
5
votes
2answers
511 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 ...
5
votes
2answers
16k 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 ...
5
votes
2answers
347 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 ...
5
votes
4answers
2k 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 ...
5
votes
1answer
577 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 ...
5
votes
1answer
129 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 ...
4
votes
2answers
6k 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 ...
4
votes
2answers
3k 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 ...
4
votes
4answers
258 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
2answers
174 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 ...
4
votes
2answers
2k 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 ...
4
votes
1answer
117 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]....
4
votes
3answers
4k views

SQL Server Index Fragmentation

When should you rebuild an index? vs reorganize? What index fragmentation value is considered healthy?
4
votes
1answer
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. ...
4
votes
3answers
5k 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 ...
4
votes
2answers
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 ...
4
votes
1answer
381 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, ...
4
votes
1answer
2k 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" ...
3
votes
2answers
597 views

SQL Server heap tables fragmentation monitoring

As a SQL Server DBA I have maintenance jobs to regularly rebuild/reorganize indexes. Recently, I ask myself about table fragmentation in SQL Server. Then I have read article regarding heap tables and ...
3
votes
1answer
925 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 ...
3
votes
1answer
1k views

Non-Unique Clustered Indexes Administration

I have been recently assigned to administer a database in SQL Server 2016 and I have discovered that there are many tables in the database with Non-Unique Clustered indexes resulting in Non-Clustered ...
3
votes
3answers
11k 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 ...