What is Fragmentation and How to Defragment a Table Being Used Consistently?
May 27th, 2008 by Andrew Chen
It has been a long while since last time I wrote about SQL Server. One of the idea in my mind when I set up this blog was to write about the tricks that I know
As you know I am looking for jobs recently. I have been to many interviews and I have learned a lot from the interviews. Whenever I was not sure about the answer of an interview question I would remember the question and did some research on it. It is in that way I pick up the knowledge I needed as an IT professional all these yeas. Whenever there is uncertainties and questions put them down on a note and look it up when time is available. If you can be persistent like this you will be an expert someday.
Anyway one of the interview question I didn’t handle very well is what is fragmentation, how fragmentation occur and how to defragment a able being used 24/7. In fact I feel one of my interview was failing on the last part of the question. So I did some research and the following is the answer in my own language.
Fragmentation regards to data scattering around on the disk instead of being stored contiguously on the disk. When SQL server reads data with more fragmentation the disk head will move more frequently from sector to sector. Since disk head movement is time consuming operation fragmentation will degrade read performance.
There are different type of fragmentation that will affect SQL server read performance. One is file fragmentation. That has nothing to do with SQL server. It has to do with the OS. If OS can not find a contiguous disk space for the data file you want to create then it will break down the data file into several pieces on the disk. Logically it is still a single contiguous data file to SQL server. This kind of fragmentation can be defragmented using the disk defragmenter that comes with window server. However SQL server has to be stopped in order to run the disk defragmenter on SQL server data file.
Another type is SQL server fragmentation. It occurs when data is being inserted, updated or deleted. When a data page (SQL server 2000 uses 8K bytes as one data page) has no room for new data inserted or updated it will do a page split. One data page splits into two so that new data can be fit in and at the same time leaving gaps in the new data pages. The page split operation can also make the originally contiguous data pages now became not contiguous. When data is deleted from the data page it also leave gap within the data page. Eliminating this fragmentation can also improve read performance.
Now the last part. How to defragment a table being used 24/7? A simple answer is to use DBCC INDEXDEFRAG command. It is basically an online operation that will not block update, insert or delete. However on indexes with excessive fragmentation this command will be slow compare to other ways of degramentation. If there is a maintenance window, DBCC DBREINDEX can be used or you can simple drop and recreate the index. Another thing is that DBCC SHOWCONTIG command can be used to show how much fragmentation a table or index has.
Related Posts:
(No Ratings Yet)
Loading ...
[…] my previous post “What is Fragmentation and How to Defragment a Table Being Used Consistently?” I told you that DBCC INDEXDEFRAG command was the solution to defragment in a 24/7 […]
how often should we run DBCC degragmention?
It really depends on the type application running against the database and your environment. If your application is mostly doing single record update and look up then you probably don’t want to run defragmention at all because it will not boost performance. If the application is a reporting type of application like data warehouse then defragmention will help performance. How often to run depends on the size your database, how often your data get changed, how critical your application is and the availability of maintenance windows. I think weekly schedule will work for most business.