December 15, 2003 at 2:45 pm
I have an interesting dilemma. Let us say I have a fixed number of disks
attached to my SQL Server. I need choose between, making a single
volume on those drives and placing data and indexes on that volume
OR dividing them into two volumes and placing data on one and index on the other.
Or does it just depend on the kind of queries that run against the DB?
Like, If I have a lot more write ops in the database and lot
less read operations, which option would be better?
I am leaning towards making single array since it makes better use of my drives.
thanks.
December 15, 2003 at 3:03 pm
Ideally, you should have at least four partitions: system, swap space (virtual memory), SQL log and SQL data. Data should be RAID 5. The others should be RAID 1.
How you divide data is greatly dependant upon the individual database. The idea is to reduce disk contention, so putting all of your indexes together isn't necessarily better than putting all of the indexes and data together. Unless you have a very clear idea of how the data needs to be divided, I would just leave it all on the same partition.
December 16, 2003 at 1:48 am
You don't really gain from creating partitions/volumes across the same set of disks because no matter how you slice and dice your disks each is limited to a number of i/o operations per sec and the head(s) can only be one place at a time. To gain performance from splitting across filegroups you need to place each on a seperate array. I'd say the minimum for a good spec server is o/s and sql on one array, data, logs and tempdb on their own arrays .. you could also create a sep array for backups. in an oltp db use raid 10 .. raid 5 is bad for oltp .. best only used in a read only situation.
So to your Q - one array is best.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply