Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
51 views

use DBCC SHRINKDATABASE after rebuild / reindex [duplicate]

I have little free disk space. Why shouldn't you use DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) after every rebuild/reindex operation?
2
votes
0answers
36 views

SQL Server one-to-many and index fragmentation

I'm currently writing code to update an entities child collection (one-to-many relationship), and while thinking about how to write code to determine which entities have been added/removed/modified, I ...
0
votes
0answers
15 views

Does rebuilding and reorganizing indexes yield less fruitful results on tables that don't have clustered indexes?

I understand the operations of rebuilding and reorganizing indexes at a high level, but was wondering what they are actually doing on a table that doesn't have a clustered index (i.e. a table stored ...
0
votes
3answers
200 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 ...
0
votes
1answer
59 views

Index Maintenance - Reorganize After BIG SQL Server Shrink

I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database. EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @...
1
vote
0answers
55 views

Fragmentation of indexes with small a database

I'm trying to figure out whether its worth defragging a 2GB mssql database. indexs are above 70% fragmented but queries don't appear to be talking too along. That being said I am having a delay when ...
2
votes
2answers
176 views

How to Limit DTU Percentage Consumption for a Given Session in Azure SQL Database?

We have an Azure SQL Database tier S4 (200 DTU), and there is one big table inside When I run a "alter index rebuild..." statement, it takes about 18 mins to complete This particular nonclustered ...
0
votes
2answers
68 views

Is Ola Hallengrens Stored Procedure what we need and safe to use [closed]

I stumbled across a stored procedure and wanted to clarify a few things first as I’ve been told it needs “rigorous review before being executed on any server”, and “testing before we run it on a ...
2
votes
1answer
79 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?
0
votes
1answer
100 views

Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse

I have a question about tables and fragmentation in MySQL My starting point is this question: How find and fix fragmented MySQL tables I have a table with 1 million of rows and 128MB of space. This ...
1
vote
3answers
94 views

DataBase Performance Issues with Indexes

I'm reviewing a production database with over 400 tables and 658 indexes. I have determined that only 440 of these indexes are being used. Of these, many have fragmentation approaching 100% so I will ...
1
vote
2answers
130 views

Should I defragment InnoDB table with fixed-length fields?

I am running MariaDB 10.2.13 on Linux (Debian). I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain ...
0
votes
1answer
124 views

test data to generate high index fragmentation level

I want to test the rebuild and reorganize operations for the indexes in sql server and for that I need to populate the db with test data that generates high fragmentation levels/percentages. Does ...
2
votes
1answer
58 views

defragment SQL server database

I have deleted some tables on a SQL server. Suddenly the size of the database jumped by 100Gb (whereas the deleted tables were taking 10% of that..). I have read good and bad things about SHRINK ...
1
vote
1answer
495 views

Fragmentation in PostgreSQL 9.1

How can I check for fragmentation in PostgreSQL 9.1?
3
votes
1answer
678 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 ...
5
votes
2answers
263 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 ...
0
votes
1answer
445 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 ...
2
votes
1answer
565 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
vote
0answers
80 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 ...
3
votes
2answers
401 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 ...
2
votes
2answers
73 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 ...
1
vote
0answers
253 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 ...
2
votes
1answer
823 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
1answer
635 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 ...
0
votes
1answer
404 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 ...
1
vote
1answer
396 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
0answers
18 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 ...
1
vote
1answer
122 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 ...
3
votes
2answers
454 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 ...
1
vote
1answer
88 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 ...
2
votes
3answers
381 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. ...
6
votes
1answer
6k 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 ...
1
vote
2answers
2k 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] [...
3
votes
1answer
501 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 ...
1
vote
1answer
166 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. ...
15
votes
2answers
2k 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 ...
1
vote
1answer
94 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
4answers
3k 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 ...
2
votes
1answer
705 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 ...
-1
votes
1answer
165 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 ...
4
votes
3answers
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 ...
4
votes
5answers
3k 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%...
3
votes
1answer
681 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 ...
1
vote
4answers
2k 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 ...
0
votes
1answer
3k 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? ...
0
votes
1answer
67 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 ...
1
vote
1answer
292 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, ...
2
votes
2answers
3k 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 ...
3
votes
1answer
400 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 ...