Hot answers tagged

29

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


24

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


23

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


13

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


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

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


9

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


9

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


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


6

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


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

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

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


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

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

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

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

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

Your query has most of the main elements, but you are missing the important index_id join predicate from sys.dm_db_index_physical_stats to sys.indexes: AND si.index_id = IPS.index_id You might like to compare your query to the similar one in Glenn Berry's Diagnostic Information Queries for SQL Server 2008: -- Get fragmentation info for all indexes above ...


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


5

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most. But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge ...


4

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


4

The disk activity is high because it needs to pull your whole database into RAM to do its analysis. If you call sys.dm_db_index_physical_stats with fewer NULLs, it will be able to run your query on a subsection of the database, which will then run much quicker. Sadly, your TOP 1 isn't stopping it from doing all the calculations, as you're calling the main ...



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