July 13, 2012 at 8:16 am
I'm trying to come up with a suitable SQL storage plan to maximize performance. I have three RAID arrays to work with, storage space isn't a concern, so I'm just listing specs:
SYSTEM - RAID10 (In Server) - 2 x 10K RPM DP SAS
DATA1 - RAID10 (In Server) - 4 x SSD SATA3
DATA2 - RAID10 (10Gb iSCSI SAN) - 12 x 15K RPM DP SAS
The storage is for our OLTP database server (4-cores, 64GB RAM), and so far I plan on creating the following volumes for the following types of data:
OLTP Data User Tables -> 1 file on "DATA2" Volume -> "DATA2" RAID
OLTP Data Indexes -> 1 file on "DATA1" Volume -> "DATA1" RAID
OLTP Log User Tables -> 1 file on "LogData" Volume -> "DATA1" RAID
Distribution Data -> 1 file on "DistroData" Volumn -> "DATA1" RAID
Distribution Log -> 1 file on "LogData" Volume -> "DATA1" RAID
TempDb Data -> 2 files on "DATA1" Volume -> "DATA1" RAID
TempDb Data -> 1 file on "DATA2" Volume -> "DATA2" RAID
TempDb Data -> 1 file on "TempData" Volume -> "SYSTEM" RAID
TempDb Log -> 1 file on "LogData" Volume -> "DATA1" RAID
My question isn't really around data partitioning, or how I've separated the indexes onto SSD, I'm just specifically looking for an answer for the Master, Model, and Msdb databases. I'm thinking of doing:
Master Data -> 1 file on "SystemData" Volume -> "SYSTEM" RAID
Master Log -> 1 file on "LogData" Volume -> "DATA1" RAID
Msdb Data -> 1 file on "SystemData" Volume -> "SYSTEM" RAID
Msdb Log -> 1 file on "LogData" Volume -> "DATA1" RAID
Model Data -> 1 file on "SystemData" Volume -> "SYSTEM" RAID
Model Log -> 1 file on "LogData" Volume -> "DATA1" RAID
What type of storage would be best to use for those system databases? I don't want to place them along with the user tables or indexes (on "DATA1" or "DATA2" RAIDS) if it means that they will impact performance of those. I also don't want to place them on the "SYSTEM" RAID if a 2-drive 10K SAS array won't be able to keep up to the requirements of the system databases. My thoughts are that having them on the "SYSTEM" RAID, if it is fast enough, would allow queries on OLTP data to pull system data from one RAID, indexes from another, and table data from another, simultaneously. And writing data would involve the logs on one RAID, table data on another (some index writes on the same RAID as the log), and system data to be read from a separate RAID. Maximizing all three RAIDs, but is there anything I'm not considering? I know that often the storage arrangements depend on type of data, usage, load, etc, so I'm just looking for a general best practices on system data really.
I've analyzed I/O stats on filegroups in our current storage environment, and the system databases don't look to be very heavy for I/O at all. Does that mean they would be more than sufficient on the slowest of the three RAIDs? Or do the system databases generally require high IOPS with less throughput, making them better suited for the SSD even though they would be sharing it with Logs, Indexes, and 50% of the TempDb data?
Kevin Kembel
July 13, 2012 at 2:47 pm
my 2 pence worth, tempdb apart, don't bother separating system database data and log files, as you have seen yourself they are lightly used databases so there is no performance benefit, and why go to the pain of moving them post install. Resilience is more important than speed.
some people say separate msdb log, but not me personally, you would have to have a lot of jobs, SSIS and backups running to get anywhere near worthwhile.
I would definitely seperate system databases from user databases, so have them on their own drive or share with tempdb.
---------------------------------------------------------------------
July 14, 2012 at 5:22 am
Adding my 2 cents as well.
I would like to reemphasize as to what has already been mentioned regarding moving of system database.
The benefits (if any) would not outweigh the risks when it comes to moving master database.
you may or may not find that out the hard way when you end up SQL Server startup issues particularly with the resource database after a hotfix \ cumulative patch or a service pack is installed.
I would suggest that location of system databases should be planned before installation and ideally should not be changed afterwards.
But overall, database file distribution plan listed in the post looks great.
July 14, 2012 at 5:41 am
Point taken, I think I'll go ahead with placing the master/msdb/model database and logs all on the same SYSTEM RAID, separate from everything else.
Thanks for the input!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply