How can I save a severely fragmented database/table?

Question

I've recently made a discovery that might explain my slow SQL run times.

I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 GB this is a phenomenally HUGE issue. The result is that I have a severely fragmented table (and likely the entire db is severely fragmented). It makes updates incredibly slow as SQL must first create the space for the update, before writing it. And to run updates, it has to piece together thousands of 1 MB pieces of information each time before performing calculations.

So my question: is there a way to resolve this and defragment? I've already gone into Properties - Files and reset the mdf growth level to 100MB, and db recovery is on simple. Can I defragment, or do I need to just go in and recreate the table, or re-create the entire db? I know it will be a lot of time and work, but my main goal for this db is time efficiency. It needs to be as fast as possible and if that means starting from the beginning again, so be it.

In addition, it was brought to my attention that rather than having one very large mdf I can create 'filegroups'. I understand this is something like saving multiple mdfs; one for each table, or even index. How can I safely create these filegroups and will they also help optimize query time and efficiency?

Answer 1

If you have a clustered index on the table the fastest way to defragmanet your table would be to drop and recreate the clustered index. On versions prior to SQL Server 2005 you can defrag standard indexes using the DBCC INDEXDEFRAG command:

http://msdn.microsoft.com/en-us/library/ms177571.aspx

Or the more prefered way on SQL Server 2005 and above is by using the ALTER INDEX statement:

http://technet.microsoft.com/en-us/library/ms188388.aspx

If you have no indexes at all on the table then you will need to defragment the file using Windows (although this does not move the data in the file - it only moves fragments of the file closer to each other.)

As for the filegroups question; multiple files can help improve performance when the files are on separate disks. This is because you can take advantage of concurrent read/writes. It's generally accepted as good practice to put large indexes in a different file on a different drive to the table that they belong to.

I hope this helps you.

Answer 2

As Mr.Brownstone pointed out, there are at least two issues at play here. The first issue is physical layout of the file on disk. Which begs the question: Are you storing the database on a single physical disk, a RAID array, or perhaps a SAN? If you are using a SAN then you most likely do not need to worry about the physical properties of the file at all, simply reorganizing clustered indexes will help immensely. If, however, your database is located on a single drive or even a local RAID array, you may see great benefit from using a Windows file defrag utility. I would recommend using PerfectDisk to defrag your drive since it is certified for use with SQL Server.