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?
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