Tag Info

Hot answers tagged

21

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


10

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

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


9

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


8

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


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

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

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


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

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


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

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

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


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

Moving 400k records per month into and out of tables shouldn't cause all that many problems from a performance perspective. As SQL removes all the data from pages those pages will be reused for something else. You shouldn't be getting inserts or updates in the middle of pages all that often as SQL should just be putting the new rows at the end of the table ...


3

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.


3

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


3

Logical fragmentation occurs when the logically next page is different from the physically next page. In the case of the leaf level of an index with a monotonically increasing key this can happen if extent allocations for the index become interleaved with the extent allocations for other objects. Even without this a small amount of fragmentation will ...


3

Matthew, I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance. 280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried. The above is because when SQL Server creates a new ...



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