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.
150
questions
0
votes
3
answers
2k
views
index rebuild/reorganize frequency
I am a dba by accident. I am doing index rebuild/reorganize. I would love to use some scripts once I become comfortable doing these tasks. I did go through some of the forum pages regarding this ...
- 163
1
vote
1
answer
82
views
Options for Index Defragmentations For Critical Tables
I wonder if this is a suitable solution for index defragmentations.
scenario is like this;
There is a table named 'Events' which is very critical and has 4 indexes which are not done maintenance ...
- 37
4
votes
1
answer
467
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, ...
- 621
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
...
- 141
2
votes
1
answer
585
views
Clustered composite key high fragmentation and fill factor
I've recently made a table to hold the language preferences of my users as follow:
CREATE TABLE [dbo].[systemUserLangPreference](
[systemUserID] [int] NOT NULL,
[langID] [int] NOT NULL,
[...
- 143
2
votes
2
answers
1k
views
fragmentation for non cluster index growth very fast to 30
i have table with about 7 milion records.index fragmentation was about 90 .
i try rebuild index for all index. and it works fine but after nightly job fragmentation for one of none cluster index ...
- 188
6
votes
2
answers
7k
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,226
1
vote
1
answer
69
views
Why did my indexes drop in size?
I've got the following SP which I wrote to calculate the size of all the tables on my database
SELECT
name,dbo.nice(rows) as [rows],
dbo.nice(CAST(total-indexes as DECIMAL)/1024) as [data (...
- 113
1
vote
2
answers
347
views
For merge replication, do I need to schedule index maintenance on all sites or just publisher?
I have a very fragmented database that uses merge replication with remote sites. Do I need to schedule index maintenance (reorganize / rebuild) for all sites? Or just on publisher copy?
- 199
1
vote
3
answers
561
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 ...
- 61
2
votes
1
answer
1k
views
InnoDB, Hash Table, Reference Table and Fragmentation
I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format:
CREATE TABLE IF NOT EXISTS `domains_list` (...
- 23
3
votes
2
answers
6k
views
Difference between partitioning and fragmentation [closed]
I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results (...
- 130
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
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
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
2
votes
1
answer
576
views
Equivalent of MRG_MYISAM in databases other than MySQL? [closed]
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?
I know about fragmenting, but this is not quite the same AFAIK. We're using ...
- 121
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 ...
- 251
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
0
votes
1
answer
600
views
What is the significance of root and page level fragmentation in a clustered b-tree index?
In investigating fragmentation on some of our large tables in a SQL Server 2005 database, I was using a query based on sys.dm_db_index_physical_stats. The query returns a row for each level in the b-...
Ken Culver
2
votes
1
answer
4k
views
MySQL Index Fragmentation
Is there any way to find out the fragmentation of indexes in MySQL ?
If such an output can be obtained, it would be helpful to identify which all indexes need to be rebuilt.
I'm using MySQL 5.1 on ...
- 43
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,300
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
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,035
2
votes
2
answers
2k
views
Replacing composite key with surrogate
I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current ...
- 2,591
2
votes
2
answers
427
views
Removing physical fragmentation
If I have inherited db that has grown to 400GB 1 MB chunk at at time. Is an offline defrag the only way to remove all the physical fragmentation. Another issue I have is that the data and log files ...
- 2,591
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
4
votes
2
answers
3k
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
5
votes
1
answer
777
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,674
3
votes
2
answers
70
views
Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?
I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...
- 3,646
12
votes
3
answers
8k
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'...
- 763
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
11
votes
1
answer
740
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,248
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 ...
- 357
33
votes
3
answers
41k
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,333
1
vote
1
answer
951
views
Model for Distributed Database [closed]
I want to design a model for a distributed database solution.
Are there appropriate tools and approaches?
How to include stereotypes for (horizontal) fragments?
- 255
10
votes
2
answers
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 ...
- 17.9k
10
votes
2
answers
351
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
1
vote
1
answer
493
views
How should I distribute my tables for better performance from fragmentation point of view
I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing INSERTs AND DELETEs. There are about 400,000 records @ 4KB/record being inserted or deleted ...
- 11
3
votes
2
answers
4k
views
How to identify fragmentation level of the table data itself not the table indexes, and then defrag
Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself.
Also defragmentation solution is ...
- 31
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
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
31
votes
4
answers
67k
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,403
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
1
vote
1
answer
893
views
How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?
We have a keep-growing table which using MYISAM storage engine.
Almost every half year, this table will crash, and need to be repaired.
I checked the defragment result reported by windows ...
- 113
44
votes
4
answers
56k
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,677
4
votes
1
answer
2k
views
Optimising a Database Shrink (in terms of speed and reducing fragmentation)
We have a large database that has had a lot of older data stripped out, leaving primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb ...
- 32k
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 ...
- 673
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
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