Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2007-03-21 : 12:15:22
|
| I have two questions.I have a table that has approx 5 million rows, That is heavily accessed. I am looking into transferring the table into a different file group from the rest of the database. The table has numerous text column data types. 1) Can some one tell me how this can be done and how it will improve performance.2) Are their any guidelines i.e. shall I move the whole table i.e. the clustered index or just the non clustered indexes to the new file group. Or the whole table.3) When we implement multiple file groups will we have to review the backup strategy.Many thank for your help |
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2007-03-23 : 10:41:10
|
| What is prompting the move?Will the new filegroups be on the same physical disks as the existing filegroups?The reasons for the change will determine what should be moved. FYIThe clustered index stays with the data. It is a good practice to place nonclustered indexes on a separate filegroup(s).Your backups may change. It depends upon your current approach full backups, filegroup backups, etc. You should review your restore scripts to make sure the new filegroups are handled correctlyLance Harra |
 |
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2007-03-23 : 11:20:44
|
| <<What is prompting the move?>>Poor Performance. We have quite a few issues with blocking and long running transactions.<<Will the new filegroups be on the same physical disks as the existing filegroups?>>they will be on the same physical drive. Shoyld they be seperate? |
|
|
|
MohammedU
Posting Yak Master
145 Posts |
Posted - 2007-03-23 : 15:23:12
|
| If you can efford you create 3 filesgroups on 3 different physical drives...1. Clustered index (Data) 2. Non-Clustered indexes3. Text data.If you are using filegroup backups you may need to change your backup startegy otherwise regular full/deff/tlog backup should be fine...MohammedU |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-03-23 : 17:17:40
|
quote:
Originally posted by miranwar
<<What is prompting the move?>>Poor Performance. We have quite a few issues with blocking and long running transactions.
I seriously doubt that creating multiple file groups will solve this problem. More than likely, it is an issue with the queries or elsewhere in the application.CODO ERGO SUM |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2007-03-24 : 13:55:37
|
| You need to determine the cause of the blocking and long running transactions. You can use this information to decide how to improve performance. I would try other tuning methods before adding files and filegroups unless the cause of the poorp perfomance points specificially to some type of IO issue and then you need to determine if it is a sql IO issue or a disk IO issue.Lance Harra |
|
|
|
|
|
|
Multiple filegroup strategy