Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
If you'll login here with the same ID you did on Stack Overflow the accounts will auto-associate... –  jcolebrand Aug 22 '12 at 15:13
3  
Your last paragraph I think is a separate question altogether. –  Aaron Bertrand Aug 22 '12 at 15:19
    
Agreed @AaronBertrand. That does sound like a second question. For that matter, has that been handled here before already? –  jcolebrand Aug 22 '12 at 15:34
    
Sure, some info in dba.stackexchange.com/questions/9074/… and dba.stackexchange.com/questions/16708/… and dba.stackexchange.com/questions/2626/… and dba.stackexchange.com/questions/19863/… don't know if any of these could be considered dupes or not, though. –  Aaron Bertrand Aug 22 '12 at 15:39
    
Fragmentation isn't necessarily related to the autogrowth rate -- it depends on your usage patterns. Have you run sys.dm_db_index_physical_stats to prove that fragmentation is the problem? There are many other factors in play here, too. –  Jon Seigel Aug 22 '12 at 20:20

2 Answers 2

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.

share|improve this answer
3  
Actually I prefer ALTER INDEX REORGANIZE over the old DBCC style. –  Aaron Bertrand Aug 22 '12 at 15:36
    
Good point, I have included the MSDN link for ALTER INDEX statement if the platform is 2005+ - thanks. –  Mr.Brownstone Aug 22 '12 at 15:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.