June 10, 2002 at 11:42 pm
Hi all
I dont entirely agree with Antares686 in regard to the fragmentation issue. First of all, you cant a avoid physical fragmentation of the database files, there is no command in sqlserver to resolve or avoid it, therefore, consider shutting down the instance and doing an OS defrag.
The OS defrag aside, the comment:
>> A table without a clustered index is in heap thus any data added is always
>> added to the end and the data never fragments
well this depends on what you mean by "fragmentation". From what I understand, SQL Server allocates space at a page level within extents, typically creating "mixed extents" where the extent pages are shared by data and index (and other) page types. Now this is where its confusing, apparently there reaches a time where :
>When the table or index grows to the point that it has eight pages, it is >switched to uniform extents (BOL)
so this seems to say that SS2k will magically re-org your database at the extent level. The BOL are vague, it doesnt say that ALL data will be contigious which is zero fragmentation. Even so, from this scheme the extents will be "logically" spread serially within the database file and not mixed throughout (minus OS file fragmentation). This "re-org" doesnt necessarily mean that all will be contigious unless we are talking clustered indexes, even so, I see no reason why SS2k requires this also due to its page pointers at the leaf level of the index.
This is completely seperate from page-splitting concept of course.
Any expansion on this one or anyone traced it?
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 11, 2002 at 5:32 am
We are talking internal frgmentation, file fragmentation is an external issue and yes it can be avoid if you plan properly. First by placing log files and data files on seperate drives. Secound by making a file large enough to accomadate any new data within a give time frame at that point the file will grow and if you grow by hand and defrag you can keep contigious. Also if the file is the only one on the drive it may not be needed to defrag. In addition proper growth will limit fragmentation. TO my knowledge the table will not reorganize and the indexes are stored with a row identifier instead of a clustered index location (which is better and depending can save space with table and index). Indexes and data generally (and I may be wrong but this is the info I was given) are not mixed on the same page (read my statement on the BOL item later). Now as for a heap when data is add a new row is added, rows are added to the end, this is why not having an index on a table is much faster than having a clustered index and why when people load large amounts of data it may be advised to drop their indexes first.
Now this statment
quote:
When the table or index grows to the point that it has eight pages, it is >switched to uniform extents (BOL)
From what I understand, if a table or index is less than 8 pages in size it will be mixed with other items of less than 8 pages into an extent. However if the pages exceeds 8 pages it will be moved from one extent into a uniform extent where only it's data resides and not other objects is mixed in.
So I will say if the table or index for a heap is less than 8 8k pages you can have fragmentation with regards to other tiems. But the moment it grows beyond that with, 2000 at least, it will be moved to it's own pages and extent no longer mixed so fragmentation with the table is 0 again (this is not external file fragmentation).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 11, 2002 at 7:25 am
All that said though, fragmentatiobn will still occur in 64kb chunks? therfore fragmentation will never be zero in well used (many concurrent users and transaction) objects. Surely sql 2k server wont spend all that additional io re-orging all of a tables extents contiguously, this would be detremental to performance no matter how you look at it.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 28, 2002 at 3:45 pm
I just wanted to correct something. It is not true that inserts to a heap will always go at the "end" of the heap. SQL Server maintains PFS (Page Free Space) pages that identify how full pages are; it uses this information when performing actions, such as inserts, to find room to insert data. If there is room on a page, SQL Server can and usually will insert a row onto that page, even when it's not the last page in a heap. Having said that, if all of the pages are full, data will obviously be inserted at the end of the pages and extents allocated to the table, since that would be the only place where space is available. It is important to remember, though, that as data is updated and deleted in a table, pages that were once full can free up space, and SQL Server will reuse this space when it can by inserting rows into that space.
At a higher level, the same could be said of extent allocation. SQL Server does not necessarily have to allocated extents sequentially; when a table needs a new extent, it will search the GAM (General Allocation Map) to locate available extents, and will allocate them to the object. These extents can easily be located earlier in the file than other extents previously allocated to the object. Likewise, extents are not necessarily allocated in a contiguous manner, which is the cause of extent fragmentation.
Matthew Burr
July 12, 2002 at 9:54 pm
Also going back to the clustered index thing you can if you read up on DBCC SHOWCONTIG, if a table does not have a clustred index you can ignore Logical and Extent Fragmentation. And yes you are right about if free space exists it will instert first available position in a heap.
Here is a good link http://www.txsqlusers.com/howto/DBCC_SHOWCONTIG.asp
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 9:55 am
I alos recommend rebuilding the table if possible. A DBCC DBREINDEX on a clustered index does not truly remove the fragmentation of the data pages. There are a lot of types of fragmentation between a table and the OS.
You may also want to look at your growth settings? Is it a percent or a fixed amount? Remember your pages are 8K and your extents are 64K. Try to keep your growth consistent with these numbers. Try to keep them consistent with your OS block size as well.
You may not be able to remove the fragmentation if things are not sized correctly.
"Keep Your Stick On the Ice" ..Red Green
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply