There are two types of fragmentation SQL Server Fragmentation
OS Level Fragmentation
SQL Server knows nothing about this fragmentation since it is managed by the OS. 
You need to use the system defragmentation tool (or file copy) to defrag, which is not easy while SQL Server has the file open. A much better strategy is to avoid OS fragmentation. To do this simply ask SQL Server to grow files at a reasonable size. 1 MB is not a good increment for a terabye database. Also the default of 10% is not very good, since an active transaction that requires additional space will have to wait while the entire 10% is formatted before it can continue. Not a good thing to happen in a high OLTP environment. Notice how the Defrag utility is showing the number of fragments per file. You should be a little bit concerned about database files with over about 100 fragments, and anything over 1000 fragments may well be causing you performance issues.
You need to use the system defragmentation tool (or file copy) to defrag, which is not easy while SQL Server has the file open. A much better strategy is to avoid OS fragmentation. To do this simply ask SQL Server to grow files at a reasonable size. 1 MB is not a good increment for a terabye database. Also the default of 10% is not very good, since an active transaction that requires additional space will have to wait while the entire 10% is formatted before it can continue. Not a good thing to happen in a high OLTP environment. Notice how the Defrag utility is showing the number of fragments per file. You should be a little bit concerned about database files with over about 100 fragments, and anything over 1000 fragments may well be causing you performance issues.The best strategy is to preallocate the space in large chunks, during quiet times, so that no transaction has to wait and you avoid OS 
fragmentation. However, just in case the db file is out of space give it a smallish allocation size of something like 200MB. This is a compromise between not having the file too fragmented and not having online transactions wait for several minutes for formatting.
fragmentation. However, just in case the db file is out of space give it a smallish allocation size of something like 200MB. This is a compromise between not having the file too fragmented and not having online transactions wait for several minutes for formatting. 
No comments:
Post a Comment