I have 2 databases about 10Gb each. Both are using with similar reads and writes. Two physical disks with separates channels. What is the best option to place data and log files? 1) Disk 1 ) OS, SQL, system databases, log files. Disk 2) Data files. 2) Disk 1) OS, SQL, system databases, one database. Disk 2) Other database. 3) You tell me.[]
Nice test , But I guess you have not mentioned if these databases are small or big or if they are 24/7 application running on it with more than 1000 batch req/sec. [:#] Sat
Sorry the size you have mentioned , I would go for second one but would also place system database on disk 2 except for tempdb on another disk. Another disk for log files not place them along with data files or OS since it needs more reliable disk with more spindles and good raid with backup power offcourse. Cheers Sat
Other questions crop up such as what is growth in next 3 to 5 years for these databases, what is the RAID configuation? I incline towards option 1 for keeping user and system databases seperately.
I presume the grow will go to the double in 3-5 years. 4 disk 160GB 15000 rpm, mirrored. Two in one channel, two in the other. Hardware Mirrored. IBM Xeon 2x dual core 2Gh, 4GB RAM. Windows 2003 Enterprise, SQL 2000 Enterprise. SQL dedicated. Actually (with actual server) more than 1000 batches. (no optimization yet)
Well, I have to do this next saturday. So, I'm short of time. That is what I will do: Go with 1 option. The only difference will: Both database access one table a lot, so (in old server a lot of locks) I'll move one of that table to Disk 1 and let the other in Disk 2. Why?. Each user open severals copies of the same application and in each copy make invoices. When the application try to find the next invoice number, search and update one table to find out the next one. So, one user block other and the same user is blocked by itself. Such table means one page, so all the table are blocked. That is the reason to place one table (remember 2 databases) in one disk and the other in the other disk. I'm listening to any other opinion till Friday. Thanks to all.
Is this a third party tool application? Still I go with option1 as it suits better for such type of application (in my exp.) You could avoid putting frequently access table on diff. filegroups in order to reduce such contention. But should avoid any sort of database maintenance optimization jobs for such defragmentation.
Yes it is. Assuming option 1. I already have filegroups for some indexes, and I'll store in disk1. Should I put that table in that filegroup or in a new one?
New is better based onthe data growth and sizes. (the problem is no one else - Mods are not giving their opinion, even I'm interested to hear them) []
I have a theory about that. Mods may assume "one moderator must to know everything" , so why to read one thread from a moderator?[]
