• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Advantages of Moving Tables to another File?

Hi everyone,

We have a few tables in one of our databases that contain customer notes and historical product data. These tables are each in excess of 10 million rows and just shy of 2Gb in size.

Would we see any benefit if we moved these tables out of the main database file and created a new one specifically for them? I'm not too sure about the ins and outs of multiple database files/log files so if anyone could explain it too me or point me to some tutorials that would be great.

Cheers
Danny
0
LFMSupport
Asked:
LFMSupport
  • 6
  • 3
  • 3
  • +1
6 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, having some big tables individually on separate files as well as having the indexes on again other files will give some performance gain, until the disk speed is the bottleneck...
0
 
LFMSupportAuthor Commented:
Once the additional files are created, how do I go about moving the tables/indexes?

Do you have links to any tutorials?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Another better option is to use history tables ..
You can divide your data based on some year or something like that, to split data over different tables. This will improve the performance because, maintaining the index will be lot easier.. But you need to modify your queries ..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Once the additional files are created, how do I go about moving the tables/indexes?

to move a table, simply create / modify a clustered index into the new filegroup (ie you cannot move to a specific file, but only to filegroups, but you can always put each file into a single filegroup
0
 
LFMSupportAuthor Commented:
aneeshattingal: Splitting the data by year in to individual tables and/or even another database has been considered but the reporting and application that use this database are pretty closed in terms of development so it had to be ruled out unfortunately.

angelIII: So I create a new filegroup, create a new file within the new filegroup then "move" the indexes to the new filegroup?


Danny
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angelIII: So I create a new filegroup, create a new file within the new filegroup then "move" the indexes to the new filegroup?
yes
0
 
imran_fastCommented:
also put the new file for indexes on seperate disk. the disk should be seperate physical disk.
0
 
LFMSupportAuthor Commented:
Does the data go in to a new file/filegroup or does that stay on the PRIMARY filegroup?

Danny
0
 
imran_fastCommented:
Data will be there on the primary file group.
0
 
LFMSupportAuthor Commented:
And it can't be/isn't moved on to the other file group?
0
 
LFMSupportAuthor Commented:
What bearing does database replication have on this?

If these changes are made at the publisher are they reflected at the subscriber or are they independent of each other?

Danny
0
 
imran_fastCommented:
<<And it can't be/isn't moved on to the other file group?>>
if you have sql server 2005 then you can do partition useing partition scheme and pertition function.

<<What bearing does database replication have on this?>>
this has nothing to do with replication.
<<If these changes are made at the publisher are they reflected at the subscriber or are they independent of each other?
>>

Yes, they independent of each other.
0
 
LFMSupportAuthor Commented:
Thanks for all the comments and pointers!

Danny
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now