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
2 votes
1 answer
118 views

Is concept of fill factor useful only when index is rebuilt?

Fill factor is concept that works only when creating a new index or when the index is rebuilt. Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) ...
  • 2,870
0 votes
3 answers
227 views

Will I need to fix fragmentation after shrinking a database full of only HEAPs?

I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example ...
  • 193
0 votes
2 answers
134 views

I think SQL Docs are wrong for sys.dm_db_index_physical_stats

Go to this documentation page: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15 Then go to ...
1 vote
1 answer
128 views

How does page split result in index fragmentation?

I am trying to understand the reason behind index fragmentation. Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that ...
  • 2,870
0 votes
0 answers
27 views

Running Index defragmentation with SQL Job (looks like every night!)

After running sp_blitzcache (over time) I have found that everyday my plan cache is compiling new execution plans. 100% of my plan cache is built within the last 24 hours (each day). I think these ...
  • 1
2 votes
1 answer
99 views

Does a fragmented database cause the transaction log to grow faster?

Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the ...
1 vote
1 answer
108 views

Index maintenance for AG DBs

I wanted to get some opinions on the index maintenance we currently do at the company where I work. One of our production SQL 2012 clusters that consists of 4 nodes, with two instances on each, has ...
  • 53
0 votes
1 answer
29 views

Is twitter's character limit a way they found to avoid drastic database fragmentation? [closed]

so I just started reading a book about "Mongo schema design" and am currently reading the portion about MMAP and fragmentation. From what I understood there are different ways to avoid ...
-1 votes
1 answer
3k views

How do we select fragmented indexes from postgresql?

Need to select indexes which are fragmented for a certain level. Can someone help me with the query for that?
-1 votes
1 answer
304 views

How to practically run sys.dm_db_index_physical_stats against 460 THOUSAND tables?

I've been tasked with maintaining an old (SQL 2005; no, I can't upgrade it!) server which has 461,000 tables and 1.15M indexes. How do I go about analyzing all those indexes in bite-sized chunks? My ...
  • 508
0 votes
1 answer
63 views

Index Fragmentation - log issues

I have a few questions that I want to understand in order to take informed decision. I am not a DBA by profession, I have done some administrative tasks in the past and now I would have to create a ...
  • 23
1 vote
1 answer
157 views

Is fragmentation at non-leaf levels a problem?

SQL version: 2017 Standard x64 OS: Windows Server 2016 Datacenter (10.0) I've just created some minimal indexes on ETL tables which were previously heaps with no indexes. Both R and W performance ...
  • 125
5 votes
1 answer
717 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
2 votes
1 answer
275 views

How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?

In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions: Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation. What magnitude of number of rows ...
  • 26.7k
3 votes
1 answer
404 views

Do I need to rebuild heap table after rebuilding a non-clustered index?

Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe ...
  • 1,193
0 votes
1 answer
269 views

IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation

I'm working on a project that requires a database shrink. Following the shrink, I checked sys.dm_db_index_physical_stats and can see a lot of my indexes are over 99% fragmented (...
  • 163
0 votes
1 answer
417 views

MongoDB Fragmentation affect the Performance in WiredTiger

Im using MongoDB 3.6 I have a huge number of fragmentation in my collections. 300+ GB on one collection. WiredTiger is the storage engine. I know its bad for the OS and much wasted space. This ...
  • 1,728
0 votes
0 answers
100 views

Problem with calculating an approximate space required for online index rebuild

I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
  • 1,193
4 votes
4 answers
281 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 ...
5 votes
3 answers
556 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
0 votes
1 answer
80 views

Not lowering avg fragmentation

I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after ...
7 votes
1 answer
592 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,295
4 votes
2 answers
680 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 ...
  • 41
4 votes
1 answer
217 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,295
1 vote
1 answer
515 views

Is this database fragmentation or something else? What can be done?

I have the table below in PostgreSQL (automagically generated with insertions using Marten): create table mt_doc_order ( id uuid ...
2 votes
3 answers
7k views

Query to see Index Fragmentation on all databases without using SP or temp tables

I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as ...
  • 273
1 vote
1 answer
160 views

Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

I am working on a report which will give information for different segments like blocking process slow running queries disk space etc. I also want to add one more segment where I can get ...
3 votes
1 answer
4k views

Table Fragmentation

If I execute the following: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent as ...
  • 123
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 ...
0 votes
2 answers
482 views

Will frequent creation and deletion of TABLE (not indexes) cause fragmentation in database?

Does frequent creation and deletion of physical table in database causes fragmentation? I am frequently creating and deleting database for loading data warehouse load. I am using these table to hold ...
0 votes
1 answer
2k views

MariaDB defragmenting with new option innodb-defragment=1

I try to defragment table using innodb-defragment=1 https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/ But it was a bad experience : - locks on table - no a good defragmentation ...
0 votes
1 answer
260 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?
3 votes
2 answers
282 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 ...
3 votes
3 answers
21k 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 ...
user avatar
0 votes
1 answer
637 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, @...
  • 189
1 vote
0 answers
59 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
2 answers
866 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
2 answers
96 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 ...
  • 219
4 votes
1 answer
2k 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
-1 votes
1 answer
489 views

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

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 ...
  • 141
1 vote
3 answers
227 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 ...
0 votes
2 answers
407 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 ...
1 vote
1 answer
911 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
1 answer
386 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 ...
3 votes
1 answer
7k views

Fragmentation in PostgreSQL 9.1

How can I check for fragmentation in PostgreSQL 9.1?
3 votes
1 answer
2k 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 ...
6 votes
2 answers
856 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 ...
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 vote
0 answers
220 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 ...