Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am having a relatively small database today, and I am expecting that my db size will grow drastically in coming months. What are the precautions I should take to get better performance when the db size grows larger? In this case, will splitting the primary file group of my database in to multiple secondary solves my problem? Please advise.

share|improve this question

migrated from stackoverflow.com Oct 20 '10 at 17:20

This question came from our site for professional and enthusiast programmers.

2 Answers 2

Splitting a database across multiple filegroups permits you to take advantage of the I/O bandwidth for each physical device / path that you've placed filegroups onto. Placing multiple filegroups on the same physical disk / LUN wouldn't give you any advantage, but placing them on multiple disks / LUNs can give you a multiple of the bandwidth of a single disk / LUN (provided the underlying physical connectivity allows it).

You can get very detailed, storing parts of the database that are less frequently acccessed onto slower "nearline" types of media, and keeping the higher activity pieces (very heavily used indexes, tables, etc) onto the higher-cost faster storage.

You're not necessarily going to get significantly worse performance as your database gets bigger, depending on your design. A good indexing strategy can allow growth of the underlying database without dramatically increasing query times. If you do find that you're saturating the I/O that hosts your filegroups then adding addt'l filegroups on addt'l disks / LUNs can increase your performance. The key to deciding if multiple filegroups is going to help you is going to be in monitoring I/O performance. Multiple filegroups won't do anything to help you if you're CPU bound or choked for RAM. Filegroups only really help with I/O utilization, which is only one axis of comprehensive performance monitoring.

share|improve this answer

Great answer by @Evan. However there are additional benefits to using multiple filegroups in addition to I/O. Check out this article on the list of key indicators to move to multiple file groups: Using Multiple Filegroups in a SQL Server Database

share|improve this answer

Your Answer

 
discard

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