Tag Info

Hot answers tagged

23

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


14

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

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


11

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


10

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


10

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


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


6

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


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


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

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


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

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

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


5

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


4

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


4

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


4

If it is adding a row at the end of the index it will just allocate a new page for the row rather than split the current end page. Experimental evidence for this is below (uses the %%physloc%% function which requires SQL Server 2008). See also the discussion here. CREATE TABLE T ( id int identity(1,1) PRIMARY KEY, filler char(1000) ) GO INSERT INTO T ...


4

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


4

If you rebuild the clustered index there shouldn't be any need to rebuild the table. If the table was a heap (didn't have a clustered index) then you might want to rebuild it.


4

What you're doing is you're using a table as a queue. Your update is the dequeue method. But the clustered index on the table is a poor choice for a queue. Using tables as Queues actually impose quite stringent requirements on the table design. Your clustered index must be the dequeue order, in this case likely ([DataType], [DataStatus], [ProcessDate]). You ...


4

I would also strongly recommend looking at Ola's Index Maintenance scripts. No reason to recreate the wheel. Its a very flexible script and many DBA's in the community use it and recommend it.


4

If your problem is file fragmentation then no, there isn't. In Postgres each table gets it's own file, or set of files if it uses TOAST, in the file system. This differs from, say, Oracle (or apparently MS-SQL) where you create pre-sized tablespace files to drop your tables into-- although even there you could have file system fragmentation issues if the ...


4

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


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

SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. That can be found using the STATS_DATE function. You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per ...


3

If you are looking for data models to start with, I'd recommend you take a look at: http://www.databaseanswers.org/data_models/index.htm This is the most comprehensive list of data model examples that I found so far. In fact I have used some samples as starting point for some of my projects. These models won't show you how to take horizontal fragmentation ...


3

Tables in SQL Server can be either organised with a clustered index or have no CI in which case they are a heap. You need to look at sys.dm_db_index_physical_stats. Despite the name this also does analysis of heaps too (though logical fragmentation does not apply to these, pages cannot be out of logical order as there is no "correct" ordering in a heap). ...



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