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
6
votes
2
answers
1k
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
0
votes
1
answer
2k
views
MSSQL: High number of logical reads when inserting into fragmented heap
I'm inserting a batch of rows - using INSERT INTO tblUploadBanking SELECT FROM ... - into a table, which is a heap and have 3 narrow NC indexes.
When I execute the query from SSMS with SET ...
- 335
2
votes
1
answer
2k
views
Is there a way to get index fragmentation without using dm_db_index_physical_stats? [duplicate]
I am using SQL Server 2016 SP1 standard edition.
The view dm_db_index_physical_stats is very slow on big databases. Even if I specify the 'LIMITED' option.
I use the view to get index fragmentation, ...
- 1,999
1
vote
0
answers
248
views
Does disk fragmentation affect DB performance?
In one of the recent interviews I was asked DB question -
There is a DB with ta table having million rows. Over time will it's performance degrade. Why and how?
My answer was due to fragmentation (We ...
- 167
2
votes
2
answers
911
views
Online & Offline Index Rebuild
I had this weird case today.
Inside a database I had about 50 (1000+ pages, most of them 50000+ pages) tables which had their indexes fragmented above 70% even though I am running a nightly rebuild ...
- 1,742
2
votes
2
answers
173
views
Are there any (functional or nuanced) differences between rebuilding an index and a full drop/create? If so, what are they?
I have been working on index fragmentation for indexes that are heavily used and causing performance decreases. Our standard job checks fragmentation levels and reorgs if >5% and <30%, and rebuilds ...
- 95
1
vote
0
answers
647
views
Extreme high index fragmentation in MS SQL Server
I have a problem with CLUSTERED INDEX, every one of my table have extemely high total fragmentation, around 92% with having page fullness in the same time at around 99%.
So in my understanding pages ...
- 145
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
3
votes
1
answer
3k
views
Do we have to run auto update statistics after index rebuild?
I want to rebuild one of my indexes because fragmentation has reached 97 percent.
I want to know if after I rebuild my index, do I have to update the statistics as well? My auto update statistics ...
- 227
0
votes
1
answer
1k
views
Distributed database for worldwide LIDAR point cloud [closed]
I have an idea for a project that would build a free worldwide 3D scan of the real world. You may think of it as a high-definition version of OpenStreetMap (OSM) project. The point cloud data would ...
- 111
1
vote
1
answer
613
views
Index with high fragmentation percentage
I have a very large table with enormous percentage of index fragmentation( almost 98 % !). Obviously, it needs index rebuild. Main problem is my SQL Server is in Standard edition, thats why I cannot ...
1
vote
0
answers
26
views
InnoDB: in what scenarios, if any, is it okay to have random insert WRT primary key?
For now let's ignore the benefits of having a surrogate key for business logic reasons. Let's only consider performance (speed and disk usage).
CREATE TABLE `person` (
`ssn` bigint(20) unsigned NOT ...
- 215
1
vote
1
answer
219
views
What is Heap Fragmentation sans Indexes?
When you talk about fragmentation in a heap that has no indexes, there can't be logical order fragmentation, so does fragmentation in this instance only refer to non-contiguous pages? Edit: no.
Is ...
- 737
3
votes
2
answers
852
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 ...
- 313
1
vote
1
answer
127
views
Considerations for performance comparison with a high fragmented heap?
I am planning to perform some performance comparisons for different structures of one and the same table. The table I want to investigate on is a heap having ~ 1Mio rows and 123 columns.
Setup:
I ...
- 4,757
2
votes
3
answers
2k
views
Update statistics more often and stop defragmenting indexes
I have several vendor databases that have most (99%) of the tables clustered on GUIDs. Currently we are rebuilding the tables every weekend. Most tables are 80% or more fragmented by this time.
...
- 1,527
12
votes
1
answer
19k
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 ...
- 321
2
votes
2
answers
3k
views
Does Clustered Index on GUID create more fragmentation than Non Clustered Index?
I have a simple table:
CREATE TABLE [dbo].[UserTestGroups](
[UserTestGroupId] [int] IDENTITY(1,1) NOT NULL,
[Token] [nvarchar](100) NOT NULL,
[TestId] [bigint] NOT NULL,
[Group] [...
- 141
3
votes
1
answer
2k
views
What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON
The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF."
I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ...
- 69.7k
1
vote
1
answer
574
views
Optimising file shrink and index reorganization operations
I'm archiving my SQL tables. So I am shrinking my files (because I need the free space) and re-organising my indexes(to defragment them). I know shrinking a file causes the indexes to be fragmented. ...
- 535
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,327
1
vote
1
answer
170
views
Effect of logical fragmentation of range scan in SQL Server Table
To the best of my knowledge, this is what happens during a range scan of a table for some data when the table has got a clustered index:
The lower range is first found out, traced to the leaf node ...
3
votes
4
answers
7k
views
Index fragmentation increased significantly after rebuild
I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter. Mine might be such a scenario.
I have an index on a ...
- 629
2
votes
1
answer
1k
views
SQL Server Index, difference between Average fragmentation and Total fragmentation
Using SQL server, If i navigate to a table in the object explorer, go to its indexes, go to its properties, and view the fragmentation tab, the top section has 2 lines for me, the Page fullness in ...
- 325
-1
votes
1
answer
458
views
Verify this TSQL to find the indexes fragmented over 50%
select
OBJECT_SCHEMA_NAME(IPS.object_id) 'schema',
OBJECT_NAME(IPS.object_id) as table_name,index_type_desc,
round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent',
si.name
...
- 131
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,757
8
votes
6
answers
12k
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,757
3
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 ...
- 33
3
votes
4
answers
8k
views
What is the cause of high index fragmentation in SQL server?
We have an application which is using a SQL Server database. As the application is in use for many years, its database is of huge size, resulting in slowness of application. It was noticed that the ...
- 31
0
votes
1
answer
8k
views
Rebuild Indexes on SQL Server Standard edition
We have SQL Server 2008 R2 Standard on a Windows Server cluster. I want to defrag all indexes on a database.
What is the best approach to disable all connections and allow the rebuild of indexes?
...
- 1,528
0
votes
1
answer
82
views
One time database shrink catch-22 solution?
I am developing a plan for SQL Server 2012 EE to properly downsize and type (nchar to char types) some needlessly Unicode nvarchar(max) fields and would like to optimize the database size as part of ...
- 175
1
vote
1
answer
705
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,
...
- 25.1k
3
votes
2
answers
12k
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
3
votes
1
answer
1k
views
Fragmented clustered primary key (sequential GUID) index after processing - SQL Server
I'm in the process of evaluating a schema change on the primary keys of several of our biggest tables in our software.
Till now we used varchar(36) for all of our PKs and FKs and Hibernate generated ...
- 465
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 ...
- 194
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
12
votes
2
answers
17k
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
1
vote
0
answers
162
views
Database fragmentation and heap SQL Server
We have critical performance issues with our production database (its around 300 gb size)
We have run the following script for a few huge tables :
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
...
- 231
2
votes
1
answer
83
views
Index fragementation ? is it unavoidable
This is follow up question to @martin smith excellent answer https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean
After that i have watched MCM video of ...
- 2,959
1
vote
1
answer
435
views
SQL Server : how can I measure database performance increase after index maintenance?
We are using a SQL Server 2005 database.
We currently have no maintenance on our databases indexes. Some of our indexes are showing over 85% fragmentation when looking at the table sys....
- 105
5
votes
2
answers
372
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
1
vote
0
answers
79
views
Defragmentation of SQL Server 2008 indexes
I am having issues defragmenting indexes. I keep using the various defragmentation methods for defragmenting a SQL Server index such as DBCC INDEXDEFRAG and DBCC DBReIndex, but none seem to be ...
CM_Heroman
4
votes
2
answers
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 ...
- 41
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
4
votes
3
answers
6k
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,581
5
votes
2
answers
25k
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,581
7
votes
1
answer
10k
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 ...
- 811
1
vote
1
answer
389
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
2
votes
1
answer
3k
views
Checking database files for physical fragmentation
I searched the Internet and did not find clear explanation for when I should try defragmenting physical database files for a DB residing on a SAN. I understand that there are tools and procedures ...
- 1,163
2
votes
3
answers
917
views
Additional space required for INDEX REORGANIZE
MSDN states:
ALTER INDEX REORGANIZE; however, log space is required.
The database recovery mode is Simple, does that implies the log space is actually NOT required ?.
- 811