Secondary Data file best practice

Last Post 15 Oct 2008 08:57 AM by PaulMcKibben. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
PaulMcKibben
New Member
New Member

--
14 Oct 2008 09:19 AM
While doing some research, I came across a best practice that I wanted to ask how is it done?

The best practice is to have a secondary data file and store data and objects in that secondary file. How is that done?

Thank you in advance
PaulMcKibben
New Member
New Member

--
15 Oct 2008 08:57 AM
I understand how to create a secondary file. I have not worked with them before so my question should be:

Once secondary files are created, how does SQL "know" to put/get the data from the secondary files?

Thank you
Homebrew
New Member
New Member

--
29 Oct 2008 06:46 AM
quote:

Originally posted by: PaulMcKibben
I understand how to create a secondary file. I have not worked with them before so my question should be:

Once secondary files are created, how does SQL "know" to put/get the data from the secondary files?

Thank you


There are 2 considerations

You can have a second physical file that's part of your primary file group. This allows you to spread the data over multiple drive letters. You can do this on the fly, and when the first file, sucah as E:\....\DATA.MDF fills up, it will spill over automatically into F:\....\DATA_2.NDF. There is no logical splie, SQL still sees it as one big file and tables and indexes are randomly spread across both, but you get a performance improvement because you have 2 drives instead of 1.

You can also create a new FILEGROUP with it's own data file. This is separate from the PRIMARY filegroup and you have to explicitly put objects into it, because the PRIMARY is the default. For instance, I just created a secondary FILEGROUP called INDEX_GROUP, and within that I created INDEX.DNF. On the weekend I will move indexes out of the PRIMARY file group and into INDEX_GROUP on a new drive. This will help performance by leaving data on one drive and putting indexes on another drive.

Some people also split data into different file groups on different drives. You might put a heavily accessed ORDERS table in E:\....\DATA.MDF , and CUSTOMERS in F:\....\DATA.MDF Then when you do big, frequent joins between the 2, you will get the benefit of the data on 2 separate drives for better performance.


Acceptable Use Policy
---