Hot answers tagged

30

The tables are tiny. The page counts in your tables are: 11, 8, 6, 5, 13, 8, 10 They occupy 480kb in total. There is quite literally nothing to defrag. Edit: This warrants a little more explanation. A new table or index is usually allocated it's first 8 pages from a mixed, rather than uniform extent. So, it's possible for each of the first 8 pages to be ...


25

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 ...


24

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 ...


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 ...


14

Quote from "Microsoft SQL Server 2000 Index Defragmentation Best Practices": "Fragmentation affects disk I/O. Therefore, focus on the larger indexes because their pages are less likely to be cached by SQL Server. Use the page count reported by DBCC SHOWCONTIG to get an idea of the size of the indexes (each page is 8 KB in size). Generally, you should not be ...


11

No, there is no auto-magical defragging of indexes. If you have fragmentation, you need to REBUILD or REORGANIZE. Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Lock durations are short and will cause minimal blocking of queries. Rebuild drops an index and builds a new one. With ...


11

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 ...


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. ...


10

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 ...


9

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


8

Updates on data already there causes rows to be moved and forward pointers added In this test, we can get 65% fragmentation on 115k densely packed rows CREATE TABLE #FragTest ( FragTestID int NOT NULL IDENTITY PRIMARY KEY, SomeString varchar(4100) NULL ); INSERT #FragTest (SomeString) VALUES ('a'); GO INSERT #FragTest (SomeString) SELECT ...


7

Yes you can rebuild while the database is online with active users. It's definitely better to do it off-peak if possible. During the rebuild your queries will run slower, mostly due to the I/O overhead of rebuilding the index. How noticeable this is depends on the specifics of your system. The performance penalty is only during the rebuild - not forever ...


7

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 ...


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, ...


7

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 ...


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

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 ...


6

This is not meant to answer your question, but it will never fit in a comment. You can build this script dynamically without having to copy & paste the output into another window. Taking into account that there is absolutely no reason to REORGANIZE and then REBUILD: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'ALTER INDEX all ON ' + name + ' ...


6

If the user never run REBUILD or REORGANIZE on the database, would the SqlServer engine defragment the indexes? No. Would running REORGANIZE multiple times do the same things as REBUILD Also No. A REORGANIZE reorders the leaf pages (the pages that hold the actual index data) to be in the correct order per the index specifications. Pages can also be ...


6

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 ...


6

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 ...


6

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 ...


5

Rebuilding an index takes more CPU than reorganizing it. It locks the database so that has to taken into account. Indexes should be rebuilt when the fragmentation is more than 40% or so. After that it becomes too slow and cumbersome for the server to reorganize. You should reorganize an index when the fragmentation is roughly 10%-40%. So healthy index ...


5

It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column. Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are ...


5

Have you investigated the MERGE command in SQL 2008? Here is a basic example: merge YourBigTable ybt using (select distinct (RecordID) from YourOtherTable) yot on yot.Recordid = YBT.RecordID when NOT matched by target then insert (RecordID) values (yot.DeviceID) ; This is basically an "UPSERT" command. Update if it exists, insert ...


5

No SQL Server doesn't do any kind of automatic maintenance. Running REORGANIZE multiple times will have no effect after the first successful run (ignoring pages modified since being reorganized or those it could not lock and so skipped on the first attempt). It swaps out of order pages into the correct order so once it has done that and they are all in ...


5

This isn't fragmentation. Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation. If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway. Do you have an index on ...


5

No, backup / restore will keep all the fragmentation. Probably better to add a filegroup with files in the new location, and recreate all of your user tables on the new filegroup (by recreating the indexes with DROP_EXISTING, and as an online operation if possible). You won't be able to eliminate the original files entirely, but if you've moved all the user ...


5

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents. At fewer than 1,000 rows, your clustered index ...


5

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 ...



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