Disk Configuration Question
-
I have some disk configuration questions for my Database Server.
The database server specs are:
Quad processor
4 GB of RAM
Windows Server 2003 Std
SQL 2000 Enterprise
The server has databases that are used for reporting (a data warehouse db and several smaller reporting dbs). The databases have been configured with 2-3 filegroups that separate the largest tables in the DB to different logical drives.
Currently the logical drives on the SAN are 300 GB each. The physical disks are also 300GB. I'm wondering if I would get better performance by changing each physical drive to be two 150GB logical drives. In other words, doubling the number of logical drives but keeping the same number of physical drives. Adding additional file groups would be next.
My assumption has always been performance is really linked to the phyiscal dirve.
For example: If I have two 150GB logical drives (F: and G
created from one 300GB physical drive, creating a filegroupA on F: and filegroupB on G: wouldn't provide much of a peformance increase because it's the same physical drive. Is that a fair assumption? If so, would the same assumption apply to a SAN? I'm assuming the SAN space is only being used by my database server.
Any help would be appreciated.
-
Your assumption is correct. When looking at using file groups to spread your I/O out, you need to look at the physical disks. Spreading I/O over multiple physical disks allows for multiple disks and spindles to process work at the same time. Splitting one physical disk into 2 logic drives will not actually spread the I/O operations over different physical resources.
-
You would probably see a performance decrease by splitting a single drive, because you've probably added a lot more head movement as the disk has to constantly jump back and forth between partiions.
A SAN is harder to make blanket statements about because of the larger cache structure and configuration flexibility. Imagine you have 25 disks in a SAN, in 5 RAID groups and each RAID group is a separate LUN. If you put one file/filegroup on each LUN it will have 5 physical disks that are independent of the other filegroups. If the I/O is spread across all filegroups evenly this will probably perform very well. An alternative would be to create a meta-LUN from the first 20% of each RAID group, another from the next 20% of each, etc. This would allow 5 filegroups of the same size where each one has a piece of 25 disks. If the I/O tends to be more clustered and hits one filegroup at a time, this configuration might perform better.
One thing to note is that you don't want to mix SQL Server data I/O and log I/O on the same physical drive, and you don't want file server I/O or anything else on the same drives either. If you use a SAN, ideally every LUN in the same RAID group (whether assigned to the same server or not) is used for same purpose (SQL data, SQL logs, or none-of-the-above).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply