November 14, 2007 at 8:11 am
I put this in the SQL 2000 forum as I have an existing server on 2000 but I am getting a new SQL2005 server for a different set of DBs.
This will be a clean start with two drives as well as C.
Ideally I'd have four disks, one for system DBs, one for User DBs, one for Log files and onr for backups, but I can't so I have to make do with two drives and C.
Drive F is Raid 1+0, drive G is just a drive.
My idea was to move tempdb to C, have all the databases on F and put the log files and the backups on G (I only take a small amount of log backups during the day, the main backups are at night when there are no users).
What would anyone else do?
November 14, 2007 at 4:57 pm
I wouldn't put tempdb on the system drive. The system drive is going to get tagged hard as is with I/O. Also, you don't want a situation where tempdb fills up the system drive, thereby causing you other issues. While the latter can be prevented by limiting the size of tempdb, the I/O contention probably isn't worth it.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 1:29 am
Thanks, do you think I should treat the tempdb the same as the other databases and put the data file on F and Log on G?
November 15, 2007 at 3:06 am
Given that you only have two disks, I would. However, make sure you expand out the sizes of your user databases so that autoexpand doesn't take place. Otherwise, you may get fragmentation around tempdb (and other user databases). Also, try to properly assess the tempdb size so it gets allocated contiguous disk space (or as much as possible) upon each restart of SQL Server.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 3:49 am
I've got quite a bit of space so I had already split tempdb into four after reading this:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1276989,00.html
One per CPU and they are 3Gb each with a 1Gb log file, this is more than enough, do you think there's a problem with the tempdb being too big for it's usage?
I just have to set Uniform allocations enabled (-T1118), or rather find out how to do it. I'll be running the contig utility on the files to find out the fragmentation.
I also put database space monitoring in place so I get alerted if a log file of database file grows above 75% so I can get to it before autogrow.
November 19, 2007 at 10:19 am
The problem with splitting TempDB is that it doesn't work very well if you're putting all the files on the same logical drive. The idea behind there being a different file for each CPU is that you can further enhance performance by putting the files on different drives which splits the I/O up.
I read the article and thought about implementing it at my current workplace, then discarded the idea when I realized that I only have one drive on which the files can go. It's just more of a maintenance headache without the benefits in this particular circumstance.
November 19, 2007 at 8:49 pm
When you say drive G is "just a drive", do you mean single drive with no RAID? If that is the case, you need to consider whether you put anything on it as failure will mean your database is unavailable, one way or another (except backup files of course).
November 20, 2007 at 7:12 am
I'm a bit curios as to what is going on this box?
ie number of db's, their size, the expected usage of these, number of concurrent users etc...
Given the apalling low number of drives, you're probably not going to get any particular performance out of as few drives as three.
/Kenneth
November 20, 2007 at 7:22 am
I'm trying to get a different array controller put on the server so I can use another seven bays but until then I don't have much choice, there are 32 user databases and it has pretty heavey useage as most of the processes aren't just normal user processes but huge reports and bulk data loading, I have pointed out that it's in an appauling state but so far nothing much has been offered, I have been here for less than two months and I am there first DBA, they didn't even have backups running for most of their databases so I have to really work at pointing out what is wrong with it, it runs at a snails pace most mornings, at the moment it's the bane of my life.
The G drive at the moment is just a drive with no mirroring or anything as there is not enough bays for the disks at the moment, until I can get more resource I'm just trying to do the best balancing act I can which is why I've been asking for other peoples points of view about what they would do.
November 20, 2007 at 7:25 am
How many drives is your Raid 1+0 using?
November 20, 2007 at 7:39 am
There's only one drive with RAID 1+0 and that's the one with all the user databases, log files, backups and system databases on at the moment.
November 20, 2007 at 7:44 am
I think Brandie meant how many physical disks are used in the raid 10 configuration. At a minimum it will have 4, so one possible solution would be to turn that into a 3-disk raid 5 drive and use the other disk on the G drive in a raid 1 configuration, which is perfect for your log files.
Obviously if there are more than 4 disks in the raid 10, then you have more options.
November 20, 2007 at 8:36 am
Matt said:
I think Brandie meant how many physical disks are used in the raid 10 configuration. At a minimum it will have 4, so one possible solution would be to turn that into a 3-disk raid 5 drive and use the other disk on the G drive in a raid 1 configuration, which is perfect for your log files.
That's exactly where I was going with my question.
If the Raid can be broken up into other versions of Raid, then there may be better solutions to the current problem than the ones you're actually looking at. Tell us how many physical disks are involved in the Raid 1+0 and we'll see what we can suggest as far as better options.
November 20, 2007 at 8:55 am
Six 300Gb drives, what it the easiest way to split them up with minimal interuption?
November 20, 2007 at 8:59 am
Break the mirror. Configure one side of the mirror for your Raid 5 (for the user databases). Detach the databases and move a copy of their data files over to the Raid 5 drives. Re-attach the databases.
Create 1 mirrored set for the user Log files. Then, I'm tempted to say create another for TempDB, but I know that's wrong... someone help me out here. Would we split the TempDB files between the two remaining drives (leaving them as singles) or what?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply