| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-06-25 : 16:29:27
|
| First, my base question. How do you allocate your data and log files for your SQL databases in relation to spindles?The premise of my question is this. I support both Oracle Standard 10gR2 and SQL Server 2005 servers. Now, in our production environment we have an Oracle RAC cluster (just 2 nodes) and a SQL Server 2005 active / passive failover cluster. Both of these share the same internal fiber network and SAN (but no disks are shared between the two). SQL Server has it's block of disks and Oracle has it's block of disks.Now my question is this... What metrics would you use to identify when to put different datafiles and logs on the same or different drives?To describe my situation a bit, in our SQL Server system I support 14 databases. I'd put them in the ranges of 5 small, 3 medium and 6 large databases in terms of size and activity. Small being in the range of <10 MB to a few GB, medium being 5-9 GB and large from 10-45 GB. The bigger the database, the more activity it gets in my case.We currently break out our data files for each database into 3 files - 1 for data, 1 for indexes (to enable doing refreshes easier when copying across the wire) and 1 for logs. I'm inclined to allocate for the biggest databases 1-RAID 5 array for both the data and index files and 1-RAID 1 array for the logs. This would be for each of the of the large databases.Also, what about the system databases? I'm inclined to dump them on 2 drives - again data files on 1-RAID 5 array and logs on 1-RAID 1 array. From what I understand it is good to keep them off the OS drive and obviously off the same spindle as user databases.Then there is tempdb... I have proposed splitting this datafile into 4 data files, each on their own RAID 1 array.Any thoughts? |
|
|
saurabh811
Starting Member
33 Posts |
Posted - 2008-06-26 : 13:18:31
|
| well,first thing i want to tell you that for SQL Server 2005 active / passive failover cluster just seperate your system databases and user databases for better I/O and performance.also seperate your log files on seperate drive if you have.ex. S: drive for log files,R: drive for system database and P: drive for user database.about data files.thats totally depend on your no of cpu.i mean if you have 8 cpu in your server then you can add 8 * 1.5 = 12 files per file group.thats all for better performance.in that way you can divide your I/O.also dont make your file auto growth.otherwise you endup with lots of trouble later,when your database will grow.this is just guideline.before apply anything check your company policy and standards.thankssaurabh DBA,WDC US |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 13:43:35
|
| "about data files.thats totally depend on your no of cpu.i mean if you have 8 cpu in your server then you can add 8 * 1.5 = 12 files per file group.thats all for better performance.in that way you can divide your I/O.also dont make your file auto growth."12 files per FG won't help you unless you can spread accross Multiple disk and you don't need 12 files/FG (That is wrong suggestion). You should also make your file autogrowth but not by percentage .(This way if file needs to grow it doesn't grow in %). Try to put log files and TempDB on its own disk. |
|
|
|
saurabh811
Starting Member
33 Posts |
Posted - 2008-06-26 : 13:54:19
|
| so what you suggest sodeep...if i put autogrowth on and then my file grow to 50GB.so let me know which one better for I/O if my file size 50Gb or 3 file each with 17GB... |
|
|
|
saurabh811
Starting Member
33 Posts |
Posted - 2008-06-26 : 13:56:04
|
| second thing i m not suggesting that u can have 12file /FG..thats max you can have...according to your no of cpu. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 13:59:46
|
| Therefore I specify not to enable autogrowth with Percentage.I/O is related to how many files you can spread across disk. I think you are assuming having multiple FG with 100s of files will give you better performance but it won't unless you spread accross multiple disks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 14:00:55
|
| We have almost TB of database but never have 12 files in 1 FG. |
|
|
|
saurabh811
Starting Member
33 Posts |
Posted - 2008-06-26 : 14:06:22
|
| thanks for your input i understand what you say...so you think if i have 1TB database.and i have only one FG/1 file on one driveand have 3 FG per FG/3 files on one drive give me same performance. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 14:18:48
|
| It doesn't make sense to create 3 FG with Each FG/3 files if you have only 1 drive? Well you can make add one Secondary FG on top of primary FG and make Secondary FG default so it doesn't compete with system resources. If you are creating FGs which contains heavily-accessed table or index,its better to put them in Separate drive. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-06-26 : 16:28:30
|
| Everyone, thanks for the input.First, I agree that there is no performance benefit of having multiple file groups on one drive (with the exception of tempdb). The only benefit I can even think of is in our case, we have data in one FG, indexes in another filegroup. Both FGs reside on the same disk. The only reason we do this is we need to do backups and restores to other servers that are geographically dispersed. We only copy the data FGs and just rebuild the indexes once the data is restored on the target.Ok, back to my original topic... I think I might have elaborated too much so let me pose these basic questions (I'm really looking for confirmation or a slap in the face asking what the heck I'm thinking :)):A) Is splitting tempdb into as many FGs as you have core processors really beneficial? Assume all FGs stay on the same spindle.B) Is splitting tempdb into as many FGs as you have core processors really beneficial? Assume all FGs are put on separate physical spindles.C) I know both A & B the answers are yes, but how much of a difference can you get between both implementations? This is where I need to factor in drives.D) Is RAID 1 sufficient for tempdb data FGs? What do other DBAs and hardware techs have running?E) Regarding system databases... is there really a benefit to having them on their own physical spindle? And to extend this, should their log files be on their own spindle?F) Regarding user databases... what threshold would you apply to determine if the FGs for multiple databases can share a spindle or if they need to be put on their own spindle? The same goes for the associated log files.I appreciate any input you can provide. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 16:34:54
|
| For A and B, even splitting the tempdb data files into as many as you have CPU when you only have on disk is beneficial due to the algorithm that is used.For C, we can't estimate that.For D, I can't remember. For E, it really only matters to split tempdb away from the other databases.For F, I don't know.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-06-27 : 13:31:07
|
| Tara, thanks for the input. I totally missed anything on the tempdb algorithms - thanks for pointing that out. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-28 : 00:31:16
|
| For D, raid 1 is fine for tempdb since it gets more write than read usually.For E, put log files (system dbs' and user dbs') on separate array.For F, depends on db activety, you have to get benchmark. |
|
|
|
|
Datafile topics