April 8, 2009 at 4:37 am
At present we are going through a huge migration process to consolodate a number of databases into one (and schema them) so we can add IR as all these DBs relate to each other in one way or another (please see a previous post http://www.sqlservercentral.com/Forums/Topic638206-146-1.aspx).
The issue i now have is that the SAN the original structure was built on is not configured how i thought. Basically, the SAN is structured like this:
28 disks
4 RAID groups
--> 0 = RAID 5 using 5 disks (400GB)
--> 1 = RAID 5 using 7 disks (800GB)
--> 2 = RAID 1 using 2 disks (37GB)
--> 3 = RAID 1/0 using 14 disks (935GB)
Now, this was all setup before my time with the company and i believe it was setup by DELL themselves and configured for sql server optimum performance. The problem i have is that the current setup has a number of logical drives set and these use all the RAID groups (mainly 1/0) which means that we have both data and log files split across the RAID 1/0 - i thought each logical drive was its own array. I undersand the theory behind "floodfilling" the disks, but correct me if im wrong, the tlog is sequential and having it on the same array as the data files (even though its striped across the RAID 1/0 disks), it will not perform optimally???
So, with the new structure we're looking to use schema's / filegroups along with the idea of splitting the NC indexes from the data. However, will i get any performance benefit from doing this if in the end i'm going to be using these filgroups on the same RAID 1/0 array? I can get around the tlog issue by trying to re-configure the RAID 1 to be slightly larger as 37GB is not enough for our consolodated DB but i'm curious as to whether i'm wasting my time filegrouping the indexes from the data for performance. I think i understand the principle of doing this anyway so that i can piecemeal the restore to get the data up and available quicker and add index filgroups as and when.
Has anyone had any experience of SAN's designed in this way? How did you configure your database?
any help is greatly appreciated 🙂
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
April 9, 2009 at 5:33 am
anyone??
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
April 10, 2009 at 8:08 am
1) Putting the tlogs on the raid10 will probably be faster than on the raid5 sets, assuming the raid10 isn't saturated
2) having too many file groups/partitions on disks (raid or not) can cause significant performance delay from excessive head thrashing.
3) has anyone monitored the existing raid groups to determine their IO utilization?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 11, 2009 at 3:37 pm
Hi, thanks for the response.
1) Will putting the tlogs on the raid10 be still as quick even though there are numerous log and data files on there? Can i ask what you mean by "assuming the raid10 isn't saturadted"?
2) Would having 8 filegroups (16files) be any worse than having 20 database / log files on the same raid group?
3) I've not got any specific stats at hand but can say which db's are the biggest IO hitters. These DB's are split across all raid groups. Anything specific i can provide?
cheers
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
April 13, 2009 at 6:30 am
Swirl80 (4/11/2009)
Hi, thanks for the response.1) Will putting the tlogs on the raid10 be still as quick even though there are numerous log and data files on there? Can i ask what you mean by "assuming the raid10 isn't saturadted"?
2) Would having 8 filegroups (16files) be any worse than having 20 database / log files on the same raid group?
3) I've not got any specific stats at hand but can say which db's are the biggest IO hitters. These DB's are split across all raid groups. Anything specific i can provide?
cheers
1) "isn't saturated" means it isn't already slowing down from having too much IO requests against it (such as from having too many files already). You should run IO stall checks on the existing files there to see if they are stalling out under current load. You can also run your own IO tests using SQLIO or IOMeter.
2) Not sure you really understand what a file group is here. But this is truly an "it depends" type of question. In general you should avoid having database and log on same spindles.
3) What you need to do is get file IO stalls from the DMV for that, wait for a bit (I typically use 5 minutes as a default), then get the IO stalls again and do a differential analysis on them. I have a sproc I created for that purpose. You need to do the difference because IO stalls are cumulative.
My best advice to you if you really want to get things right is to hire a performance tuning professional for 2-4 days and have him/her give your existing systems a review and then offer recommendations for how to configure your new storage optimally. You would also gain the benefit of mentoring from this person so you will be better positioned to monitor your own systems going forward. Believe me, there are SOOOO many ways you can do things suboptimally here. You simply cannot do this right via a few back-and-forth forum posts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2009 at 7:00 am
Cheers for that,
2) Please refer to my original post, whoever set our san up originally (before my time - believe it to be DELL themselves) created 4 RAID groups with numerous LUNs and logical drives. Now, we currently have 20+ databases and these split across all raid groups, this includes numerous mdf and ldf files being on the same raid groups. My plan is to consolodate these DBs into one (possibly even 2) databases and using schemas so we can start adding RI on them which should have been there from the start as each of these databases "refer" to each other but the only RI on them is the odd function here and there which does a check that a particular record exists in another DB - can you see my situation?
So, i've decided to split the schemas into 8 filegroups (4 holding relevant data only and 4 with their corresponding NC indexes - each of these filegroups will have 2 files = 16 ndf's, may not even be necessary to have the 2 files in this instance but building for the possibility of a file restore). The primary reason for splitting them this way is so i can piecemeal a restore if it ever became required, adding IDX filegroups after the data is available meaning i can have a system up and running quicker. This also now means i can move the ldf's to seperate raid groups and keep the RAID10 for the data / IDX filegroups
So, my original question really was, in general is having numerous filegroups (data and IDX) on a RAID10 group (14 disks) going to cause issues? I know "it depends" but reconfiguring the SAN is not possible and i want to know if i'm actually going to get any benefit performance wise out of splitting the data and idx's into seperate filegroups if they're going to be on the same RAID group?
thanks for your input so far
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
April 13, 2009 at 7:58 am
Swirl80 (4/13/2009)
Cheers for that,2) Please refer to my original post, whoever set our san up originally (before my time - believe it to be DELL themselves) created 4 RAID groups with numerous LUNs and logical drives. Now, we currently have 20+ databases and these split across all raid groups, this includes numerous mdf and ldf files being on the same raid groups. My plan is to consolodate these DBs into one (possibly even 2) databases and using schemas so we can start adding RI on them which should have been there from the start as each of these databases "refer" to each other but the only RI on them is the odd function here and there which does a check that a particular record exists in another DB - can you see my situation?
So, i've decided to split the schemas into 8 filegroups (4 holding relevant data only and 4 with their corresponding NC indexes - each of these filegroups will have 2 files = 16 ndf's, may not even be necessary to have the 2 files in this instance but building for the possibility of a file restore). The primary reason for splitting them this way is so i can piecemeal a restore if it ever became required, adding IDX filegroups after the data is available meaning i can have a system up and running quicker. This also now means i can move the ldf's to seperate raid groups and keep the RAID10 for the data / IDX filegroups
So, my original question really was, in general is having numerous filegroups (data and IDX) on a RAID10 group (14 disks) going to cause issues? I know "it depends" but reconfiguring the SAN is not possible and i want to know if i'm actually going to get any benefit performance wise out of splitting the data and idx's into seperate filegroups if they're going to be on the same RAID group?
thanks for your input so far
I told you what to do if you want to know if your proposed solution will be optimal: do testing with SQLIO or IOMeter, or hire a consultant.
Without, much more information, I will say that I doubt having 2 files per file group will help performance much. I will also say that your desire to have "piece meal" restoration capability is not going to be accomplished here. My guess is that you will have multiple tables/indexes per file group, and that will almost certainly void the usefulness for your intended restore feature.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2009 at 8:28 am
If you're stuck with the SAN configuration that you've got then at least consider the following:
- Assuming your workload is typical OLTP (e.g. 80\20 reads\writes) put your data files onto the fastest set of disks. That's going to be your RAID 10 drives.
- Segment your transaction logs from your data files. Without knowing your recovery model, space requirements, or I/O load on the logs I couldn't tell you whether to put it on the RAID 1 or RAID 5, but generally speaking RAID 1 is recommended for transaction log files because of the write performance.
- You can get some benefit from putting your nonclustered indexes into a separate filegroup. If you put your data files on RAID 10 that would mean NCs on the RAID 5, but you're going to suffer write performance whenever they need to be updated. RAID 5 writes can be up to 66% slower than RAID 10 writes, so be careful.
- Perhaps a better strategy would be to look at your database usage and put the read-only DBs and DBs that see relatively few writes onto the RAID 5 arrays, and let the write heavy databases (including their nonclustered indexes) live on the RAID 10.
- If you can, make sure to set the proper partition offset and format using 64K block size.
If you haven't already read this, check out the series I did on my blog about OLTP performance on RAID 1\5\10. It was written with local drives and DASD in mind but is still applicable to your situation: Disk Performance Hands On
April 13, 2009 at 8:40 am
check with the SAN vendor since they all their own best practices
we have a lot of databases on an EMC DMX-3 and they mostly 3+1 RAID5's on the backend and then presented to the host. EMC says you can put different servers on the same spindles as long as you mix hi I/O and low I/O on the same spindles
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply