Feeling fragmented? Relax . . . this blog post will bring the pieces together. This post can help you get better performance from Teamcenter running on SQL Server.
When you mention "fragmentation" to database administrators, they automatically think of index fragmentation. But database fragmentation comes in two main forms: external and internal.
External fragmentation, also called physical fragmentation, is fragmentation at the file level. When you create a database file and the disk is already fragmented, Windows may place pieces of that file in as many holes in the current storage as possible. This level of fragmentation at the file level causes the disk head to jump around while it retrieves data during execution of SELECT statements, which introduces latency and performance degradation.
You can also cause external fragmentation when you grow a database file. While many of us use the autogrow abilities of SQL Server, a database that constantly grows due to space pressures can cause external fragmentation. Autogrowth on drives that have no other files on them should not cause fragmentation because the new file segments can simply be created after the old ones.
On the other hand, if you shrink your database file, you can cause gaps between the files that the new segments may fit into when the database grows. This will then cause external fragmentation.
Here's an easy best practice that will save you lots of wondering as well as prevent fragmentation: size the file to its maximum known size when you create the database.
And here's another one: if you need to grow a database, either do so on a defragmented drive or create a new database file on an empty drive volume.
Although you can use a Windows defragmentation tool to defragment your database files, these tools simply move chunks of files around to get them contiguous. This moving of chunks of files can cause internal fragmentation that you might not be able to resolve easily.
Internal fragmentation, also called logical fragmentation, occurs inside the database file. This level of fragmentation can happen at one of three levels:
· Extent allocations between tables (table fragmentation)
· Extent allocations on a heap or index B-tree out of logical order
· Pages of an index out of order as they are contained on an extent
Table fragmentation occurs when two or more SQL Server tables have the logical order of their allocated extents mixed up.
For example, table A is contained on extents 1 and 5, table B is contained on extents 2 and 4, and table C is contained on extents 3 and 6. Having all the extents mixed together causes the disk head to jump around and prevents SQL Server from reading more than one extent of data at a time. (We'll write more about sequential reads in another post.) Each of these conditions increases the amount of time it takes to read data for queries.
Table fragmentation is often ignored in discussions of SQL Server fragmentation. It is hard to spot because it involves searching through SQL Server management views, and it is often hard to correct.
To resolve table fragmentation, you often have to rebuild the table or clustered index in a different filegroup. Then, you have to move it back to its original filegroup when no other file growth is happening and in a sequential, nonparallel manner. (See why you don't want to cause internal frag if you can help it?)
Internal table extent fragmentation is the ordering of the extents for one table. If the extents are all together in the file but are not in logical order, the same conditions are caused as in table fragmentation. The disk head has to jump around, and SQL Server cannot read larger chunks of data.
Page-level fragmentation is the logical ordering of the pages in a table. If page 1 is on extent 5, page 2 on extent 6, page 3 on extent 7 and so on, SQL Server must read from all the extents. This causes either the disk head to jump around or SQL Server to read all the extents, which creates more memory pressure. Either way, SQL Server has to do more work to retrieve the data it needs.
Luckily, you can resolve both internal table extent fragmentation and page fragmentation by rebuilding your indexes or by building and dropping a clustered index on a heap table.