We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Advantages of Moving Tables to another File?

LFMSupport
LFMSupport asked
on
Medium Priority
257 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Once the additional files are created, how do I go about moving the tables/indexes?

Do you have links to any tutorials?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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 ..
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>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

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>angelIII: So I create a new filegroup, create a new file within the new filegroup then "move" the indexes to the new filegroup?
yes
Top Expert 2006
Commented:
also put the new file for indexes on seperate disk. the disk should be seperate physical disk.

Author

Commented:
Does the data go in to a new file/filegroup or does that stay on the PRIMARY filegroup?

Danny
Top Expert 2006
Commented:
Data will be there on the primary file group.

Author

Commented:
And it can't be/isn't moved on to the other file group?

Author

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
Top Expert 2006
Commented:
<<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.

Author

Commented:
Thanks for all the comments and pointers!

Danny
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.