December 29, 2003 at 2:28 am
Can anyone help?
I'm working with a data warehouse, which currently contains around 120GB of data. This is split into around seven or eight filegroups, which mainly reside on a SAN that consisting of a RAID 1 and a RAID 5 array grouped as one drive, but partitioned into 4 logical drives. The reason for having all of the filegroups was initially for backup and restore purposes, but i would like to keep the separate filegroups, as this may give me greater opportunities for scaling out the database if necessary.
The problem that i have is this:
The Log file for the data warehouse is stored on an internal mirrored drive as i think that this should give better performance than placing it on the SAN along with the data files.
I need to create a "replica" of the data warehouse on the same server and having filegroups in the same places (i won't go into why we're doing this on a production server, but we do need to). The internal mirror is not big enough to hold all of the log files, and so i'm wondering what the implication would be of moving the log files for the warehouse and this replica database onto the SAN from a performance perspective. If peformance will be seriously impeded then i'll just get more internal drives on the server, but i want to weigh up the options first.
Thanks in anticipation.
Sean
December 29, 2003 at 5:50 am
I'm wondering if you even need the log of the "mirrored" DB. ? OR... for that matter, not simply truncating both logs on check point. Unless the datawarehouse has a lot of "real time" updating rather than a nightly/weekly update, transaction logs don't have a lot of meaning, at least with the SQL Server Datawarehouses I have worked with.
December 29, 2003 at 7:18 am
What kind of SAN do you have? My experience is with an EMC Symetrics (RAID 1+0) connected by fiber. It was much, much faster than any internal disk. Therefore, I'm confused about your performance concerns.
December 29, 2003 at 2:09 pm
Sean,
Sorry About the Format...Since you have provided very basic disk information in relation to the SANs and The server it is difficult to provide or offer a improved performance solution. I would recommend the SQL Server 2000 Perf. Tuning Book. I am not recommending that you make any changes but here ya go:
Questions I would ask as include the following:
1.What are the Total Disks in the SAN’s
a.Disk Types Ultra3 15K RPM?
b.I/O rates of each disk?
2.How are the RAID Sets (you stated RAID 1 & 5) configured?
a.You stated 4 logical Drives
i.Is E & F RAID 1?
ii.Is G & H RAID 5?
b.Total disks in each RAID Set
c.Data <MDF>/ Log <LDF>files distributed.
i.Move All Log Files From the Local Mirror
1.No performance Benefit if the Drive Set 0 is handling the Pagefile and the Log files and is the minimum 2 disk requirement for RAID 1
2.Remember the golden DBA / Hardware Rule Spread the I/O’s across as many disks as possible.
3.Better to add disks to the SANs than to the Local server as this again increases the total I/O Pool for your SANs thus improving your performance (Not In all cases mind you)
4.Move All Log files to your Highest performing Drive Sets
a.You have an issue since you use RAID 5 The read performance in not as fast as RAID 1 (0+1) due to the ability of RAID 1(0+1) to support split seeks and the Write cost (in I/O’s is higher)
b.When at all Possible I would move my static data files (MDF’s to the RAID 5 Drive Sets) Since the static data will not be altered (insert, update etc.) en masse this will provide space utilization on the RAID 5 Disk Sets leaving your higher performing RAID 1 for Logs and the Non-Static Data File groups.
Jbabington
Jbabington@hotmail.com
December 30, 2003 at 2:47 am
Thanks all for your suggestions. Here's the actual spec. Forgive me if it might seem a little vague, but really i'm a developer, and not an expert on the hardware side of things. The hardware was all set up by our IS department, although i can make suggestions as to changes:
Database Server
Dell PowerEdge 8450
4 x P3-Xeon 700
4GB RAM
2 x 36GB RAID-1 SCSI-U3 (system drive)
This is partitioned into:
C: 12 GB
OS and applications, Paging file.
D: 22 GB
Database log files.
Then the SAN as follows:
Dell EMC CX400 DPE Base
2gb Cache
2 x 36gb FC2 EMC 15k Disks
9 x 146gb FC2 EMC 10k Disks
The 15k disks are configured as RAID1, whereas the 10k disks are configured as RAID5. However, apparently they both share two controllers, switching as necessary, and are treated as one physical disk. I've tried to get more info, but it seems that even our harware department aren'e that clued up(think i need to do some more reading).
Anyway, i agree that i probably need to do soemthing about the Virtual Memory, but not too sure about the log files. However, here's what i did with the filegroups:
NC indexes are on separate filegroup to Clustered Indexes (really for join performance).
Static data is on a different filegroup to everything else (initially for backup purposes).
Fact data is on a separate filegroup to dimension data (again for joining purposes).
Slowly changing dimension data is on a separate filegroup to fast chaging dimensions (slowly changing dimensions only change on a monthly basis, as opposed to nightly, again for backup purposes).
SQL Server set to use 3GB of the 4GB of available RAM (had problems in the past when allowing SQL Server to manage this).
Simple recovery model (TruncateLogOnCheckpoint).
Log file set to be static at 2GB (alerts to truncate when necessary, bit of a catch-all really).
My real issue was that as the SAN uses two controllers and we have another controller on the internal mirror would there be a benefit in having the Log on the internal mirror. However, as Jbabbington states, there can hardly be a benefit if the mirror also contains the O/S files and the pagefile.
Thanks all, i think i need to review the setup (as well as do a little more reading).
December 30, 2003 at 9:55 am
I think SAN's are a bit of a black art and "must have item" regardless of their good or bad points with SQL Server. I've never found a SAN to outperform a well configured SQL Server. To be honest filegroups across a single raid 5 give you no advantage .. filegroups come into their own when they are on seperate sets of spindles. Most SQL Server Books & MS have techy bits about spindles and disk i/o's - have a read
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply