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 |
|
charcoll
Starting Member
1 Post |
 Posted - 2006-08-11 : 20:18:55
|
| We have 4 logical drives (each RAID 10) on a new DASD, which we plan to strategically span our database files for a very busy database. These are for data files only, which we already have a dedicated RAID1 for the transaction logs. The database is approaching 300GB (not too large, but growing fast). The goal is to improve I/O performance by taking advantage of the disk I/O by striping the database horizontally across multiple data files. Also, we hope to obtain a more scalable physical architecture and to improve recoverability by taking advantage of filegroup backups. Our very busy database is currently comprised of a single filegroup with 1 .mdf. After running an extensive usage analysis on how the application uses the database objects, we determined that the database tables and indexes can be categorized into 8 relational filegroups, at the most granular level (above the actual tables). Here’s where the questions arise. Given that we have 4 logical drives to spread our data across, should we:1) Create 4 .ndf’s per each of the 8 filegroups, and place 1 .ndf on each logical drive? This would be 32 total .ndf’s spread accross the logical drives. 2) Create 2 .ndf’s per filegroup and strategically place the .ndf’s based on the usage between filegroups? IE, Users and Click data are heavily used in parallel, thus Users .ndf’s would be placed on X: and Clicks .ndf’s on Y: 3) Forget the 8 groups and create 1 additional filegroup only (PRIMARY, and a USER_FG for all user objects), and then create 4 .ndf’s for the USER_FG, and place only 1 .ndf per logical disk? Option 1 seems like a little much to me, perhaps “over-engineering”. But I wanted to get an expert opinion. Looking forward to your insight. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-11 : 22:25:28
|
| I usually take approach number 3, because it is not easy to predict the usage pattern for different tables, usage can change at different times of the day, and usage can change as the business or application changes. Approach 1 would do the same thing, but you would have a lot more files to deal with.If you take approach 3, filegroup backups will not have any advantage over database backups, because all the data will be in one large filegroup. Approach 1 will be more suited to doing filegroup backups. You could also have fewer filesgroups, say 4, to reduce the number of files.CODO ERGO SUM |
 |
|
|
|
|
|
|
|
Filegroups and data file placement