40

If an index is very small (I believe less than 8 pages) it will use mixed extents. Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes. Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be ...


34

This can also happen with very LARGE indexes. I had some indexes on a table with around 700m rows that I couldn't defragment below around 30%. The issue was not enough contiguous free space inside the database to arrange the index consecutively. To work around a very large index that won't defragment, the BEST solution is to pre-size a new database and ...


18

What does Fragmentation Means in a Heap The fragmentation value in Heap which you get from column avg_fragmentation_in_percent by querying sys.dm_db_index_physical_stats DMV states that Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. Further the same BOL says that This is the percentage of out-...


15

FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%. If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't ...


15

First and foremost you should assess the impact of fragmentation. Much too often fragmentation is painted as the ultimate evil cause of all the server problems w/o any consideration of its actual impact. Fragmentation impacts several aspects: Slow down scans due to impact on read-ahead efficiency and small IO size (page vs. extent) Less efficient IO due to ...


13

The answer to your query is buried in some documentation: Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. The reasons for this are explained in detail in the following MSDN blog post: How It Works: Online Index Rebuild – Can Cause Increased Fragmentation Some options to ...


11

It's going to be logical fragmentation for indexes, and extent fragmentation for heaps. The BOL reference on sys.dm_db_index_physical_stats actually gives pretty good information on the topic: Logical Fragmentation This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next ...


11

Yes, rebuilding indexes (especially on SSD) can cause worse performance. Most high speed SSD prefer many, smaller block requests instead of fewer, larger requests. This is exactly the opposite pattern preferred by traditional, spinning rust. Assume you have a highly fragmented B-tree. Because nothing is ordered on the disk, you will typically issue a lot of ...


11

Are you sure you have positively and accurately identified this system table as the sole source of "unnecessary pressure on the buffer pool and also negatively impacts the performance of operations such as computing the size of all tables in a database"? Are you sure this system table isn't self-managed in such a way that (a) fragmentation is minimized or ...


11

Based on the data that you have provided - there are 67 total pages. Index fragmentation for such a small table would not affect the performance. I would not worry about index fragmentation for the table that you have mentioned. You should update your table statistics so that sql server can generate better query plan. I would start my troubleshooting by ...


10

Personally, I wouldn't want any antivirus or defrag software anywhere near a database file. Ask yourself, is this going to cause more problems than it's going to solve? From A tale of CHECKDB failures cause by 3rd party file-system drivers: At the end of last week, Diskeeper 2009 was upgraded to Diskeeper 2010 on the servers by our server team. ...


9

In my experience, even if you're doing full table scans, it is unlikely that extent fragmentation will affect performance much, and for more typical query patterns it should be negligible at best. That is for queries that use cached data that fits into memory - obviously fragmentation of any kind becomes rather moot if the data is in memory and isn't being ...


9

Ah! It's the image column. [column11] [image] NULL, Online only works on tables without blobs. Guidelines for Performing Online Index Operations


9

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending number, which is guaranteed to (almost) never cause page-splits on the table. Also, don't store the GUID in an nvarchar(100) column, use the data type ...


9

Heaps have a few special challenges that you can't experience with clustered indexes: Forwarded Records Captive Pages I'd suggest running sp_BlitzIndex against your database to find out if either of these things is happening with your Heaps. If not, then leave them alone. If they are, you may need to consider rebuilding them. At this time, you can't ...


8

There are better ways - just reorganizing all indexes nightly can be quite wasteful. Why even bother reorganizing an index that is 12% fragmented? Why reorganize a 10GB index every night if it takes 30 minutes and you only reduce fragmentation by 2% or 3%? How much effort should you spend reorganizing an index that is largely or completely in memory anyway - ...


8

Highly fragmented, the application performs well. After rebuilding indexes, the application performs badly. A probable cause is that the changed (presumably reduced) size of the structures after rebuilding means the optimizer is choosing a different plan. One of the primary inputs to the optimizer's costing model is the number of pages each plan operator is ...


8

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages. This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to 5%...


7

In General Just to be clear at the start: fragmentation refers to when the next logical data page (i.e. the values in the fields for 1 or more rows), is not the next physical data page (the location of page in the data file). Non-fragmented pages (in Physical Page order): Logical Page 1: A1, A2, A3, A4 Logical Page 2: B1, B2, B3, B4 Logical Page 3: C1, C2,...


7

Yes, you can stop a reorganize and it won't cause a big rollback like you are talking about. You will be left with where the operation left off (that's a good thing). It's a rebuild that would have the rollback behavior.


7

Fragmentation can still occur on a table with an ever increasing key even if that key is never itself subject to updates. If the file group is shared with other objects allocations can be interleaved causing fragmentation. Updates that increase the size of rows can cause page splits. Deletes can leave pages nearly empty and cause internal fragmentation. ...


7

Based on guidance from Aaron's answer as well as additional research, here is a quick write-up of the approach I took. From what I can tell, the options for inspecting fragmentation of system base tables are limited. I went ahead and filed a Connect issue to provide better visibility, but in the meantime it seems that the options include things like ...


7

Fragmentation is often called bloat in PostgreSQL. It relates to its implementation of MVCC where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are then made visible or invisible depending on the transaction looking at the data. You can start looking at the wiki Show database bloat for more information on ...


7

Ola Hallengren's index optimize script does not perform Heap defragmentation. In other words table rebuilds are not happening when running the procedure and that is expected. You could run ALTER TABLE dbo.tablename REBUILD; to remove the fragmentation. This also rebuilds the nonclustered indexes on the heap. Better option than rebuilding your heap ...


7

Also "reducing fragmentation" is not per se a performance goal. On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting. I've worked on systems where the difference in throughput between sequential and random IO was 10x or more. As SQL Server attempts to ...


6

Index fragmentation is much overrated. Do not worry about it. Two adjacent, somewhat-empty, blocks are merged together by InnoDB as the natural processing. Random actions on a BTree cause it to naturally gravitate toward an average of 69% full. Sure, this is not 100%, but the overhead of "fixing" it is not worth it. SHOW TABLE STATUS gives you some ...


6

REBUILD of index does not depend on fragmentation. It drops index entirely and creates it from scratch. REORGANZE index - is for reducing fragmentation without index rebuild, so no drop and create. MS advises using Reorganize for 30% fragmentation or less. For higher fragmentation Rebuild is preferred. Here is MSDN article on this: Reorganizing and ...


6

I would suggest that, rather than 'roll-your-own', you save yourself a whole bunch of time and effort and implement Ola Hallengren's Index Maintenance script. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html And, check out Brent Ozar's tweaks for Ola's script https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-...


6

This one is hard to answer without knowing your query patterns, but you will most definitely cause side effects when dropping all clustered indexes and replacing them with something else. I also don't think you are necessarily correct in assuming that having a non-unique clustered index is sloppy table design or by definition sub-optimal. When you add a ...


6

Still i think that the table size is huge for the no. of records. Why? What makes you say this? what it does is stores different version of same entry from another table. So it holds versions, which could be large or small, especially given: [Data] [varbinary](max) NOT NULL, Have you checked the datalength() on the rows to see if you have some large ...


Only top voted, non community-wiki answers of a minimum length are eligible