March 28, 2012 at 6:55 pm
I have a 700GB database now and will be growing fast. I would like to create multiple filegroups to boost performance and for high availability. Can someone help me how I should start and plan the architecture? Thank you.
March 28, 2012 at 8:53 pm
Hi,
Currently how much is the file growth of your database file . It will be better if you have a proper analyze on the Database file growth . Based on that you can create the file group . You can refer below URI for creating the file group
http://www.mssqltips.com/sqlservertip/1112/filegroups-in-sql-server-2005/
Regards,
Kuttikrishnan.P
March 29, 2012 at 3:34 am
The first question is what is your goal?
Are you splitting the database for performance reasons and, if so, have you checked and ensured that you really are bottlenecked on IO?
Are you splitting the database for availability and easy restore, so that if the DB needs restoring you can restore just a subset of the filegroups to get users working and restore the rest later? If so are you using Enterprise Edition?
The approach used for those two is very different.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2012 at 3:59 am
This was removed by the editor as SPAM
March 29, 2012 at 5:20 am
Hi Gail,
Yes, it is an Enterprise Edition. The main reason for the split is for IO and of course I would like to take advantage of the database availability from the filegroups.But, I am not sure how to start splitting the primary filegroups. Thanks.
March 29, 2012 at 7:26 am
So you are definitely bottlenecked on IO? Is the IO performance more important than the availability? I ask because the general design of the filegroups (which tables go where) is almost completely opposite between the two, so whichever is most important is the one that you should focus on.
Have you tried optimising the worst queries? That often has a much better return on time than splitting the DB across filegroups.
Do you have stats on which tables have the highest IO load?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2012 at 8:02 am
First off what specifically is slowing you down? Is it loading of new data or querying existing data? Hase you profiled the system to see what is taking up the most IO and look at fixing the quiries themselves? Does some data get utilized more than other data, such as more current dated information? What is the SQL Server hardware specs currently, if you have a large DB but 8 GB of RAM you might be suffering a lot of memory swaping? Is your temdb on a seperate drive and on a different drive controller?
This is just the tip of potential question to ask, look at stuff already provided but you do need to understand what you are addressing versus just saying filegroups might fix all my woes.
March 29, 2012 at 8:09 am
Antares686 (3/29/2012)
This is just the tip of potential question to ask, look at stuff already provided but you do need to understand what you are addressing versus just saying filegroups might fix all my woes.
Indeed. In fact, splitting data across multiple filegroups for performance reasons may not give much if any improvement. Has to be investigated and designed carefully.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply