SAN Drive configuration for best performance
-
Good afternoon,
We are in the planning stage of drive allocation for a SAN for our new SQL Servers. We currently have over 30 SQL Servers configured as follows:
C: (RAID-1) System and SQL .exe files
D: (RAID-5) SQL default data drive
E: (RAID-1) SQL Database Logs
F: (RAID-1) SQL TempDB
G: (RAID-5) SQL Backup drive (for SQL Dumps)
I read "somewhere" that because of the sheer nature of a SAN, the above configuration is obsolete and replaced by something else (read: less drive and RAID 10 Configuration)
Can this be confirmed?
Thanks
DespaG
-
If your budget will accommodate, definately go for the RAID 10. Performance and redundancy are excellent. There are other raid options out there, for example RAID7, which is a beefed up version of RAID5. Here's what I'd recommend (based upon my usage, other people may have differing opinions, it very much depends upon the circumstances):
Local:
C: (RAID1) Boot & OS
D: (RAID1) Pagefile
SAN:
E: (RAID10) SQL System DBs
F: (RAID10) SQL Databases
G: (RAID10) SQL Logs
H: (RAID10) SQL Backups
Optionally you can add another drive for indexes. While, due to the way the SAN works, there is less need to seperate the drives in this manner it makes physical design sense to seperate things out still.
-
Thanks for the prompt response.
This would be the ultimate in regards to performance and I thought about suggesting it. I was looking for an alternative, if any, to this option. But hey, don't get me wrong! Unless my Server administrators can prove me otherwise (so far, I've been able to prove my case and point in regards to performance), I'll bring this one to them. We'll have to come to a compromise with its balance tilting towards me.
-
I've found that you always throw at them the optimal solution, that way when you finally get the hardware it might possibly be something you could actually use...
-
We have setup configurations similar to yours, with the following exceptions:
- SQL Data on Raid 10. On a stress test, having SQL databases on raid 10 will perform 10-15% than raid 5.
- Make sure that SQL Data and SQL logs are on separate controllers/raid arrays, so that simultaneous writes to data and log can occur.
- SQL Backups on raid 5 is good. However, with multiple SQL servers, we setup one drive on one server with all backups, so that they are in one place for tape archival and sql restore.
Mark -
You should work with your SAN vendor in deciding which type of RAID to use. We are installing some HDS 9970v SANs, and have confidence in Hitachi's recommendation of formatting everything as RAID 5. We also plan to configure all LUNs as 10GB, using multiple LUNs to a drive letter where we need the capacity.
You also need to look at LUN backup and replication policies. In our shop, we will have 4 main policies. A given LUN will be assigned to one of these policies, and all data on the LUN will be governed by the policy: 1 - No backup, no replication, 2 - No backup, yes replication, 3 - yes backup, no replication, 4 - yes backup, yes replication. We will be using SAN facilities for LUN backup and replication.
The end result is we will be using more drive letters than originally planned, to cover the differing policies. For instance, we plan to use the same drive letter, mapping to a policy 1 LUN, for TempDB, \Temp directory, and Windows pagefile. Databases will be on policy 1 or policy 2 LUNs, depending on requirements. Database backups will be on policy 3 LUNs, etc.
Putting SQL Server on to a SAN requires different thought processes to putting it on to DAS disks. If you just apply DAS thinking to the SAN, you will end up with sub-optimal use of the SAN.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Question: I'm using a SAN. I put the data on the SAN but kept the logs and temp on a RAID 5 of the server. Was this a good idea. Originally on the server, the temp and log where on the server but on different array controllers. But now they share the same controller. Should I move the log on the SAN???
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
I set up some heavily accessed OLTP databases and Data Warehouses on SANs (seperate ones), and here's one tip from my experience. I was running into very high Average Disk Queues on the windows drive that the SQL Server user databases were on. The Raid controllers, etc. were all showing normal usage.
I spread my databases over three SAN drives (I created three files, one per disk and put them into the same filegroup. Then I moved all of my database objects to that filegroup). I noticed the bigest performance improvement from this alone (and this was after we had Compaq and Microsoft analyze the usage and they couldn't determine what was causing the high disk utilization). It looks like the best thing was to let Windows utilize as many windows disks as possible, which makes sense.
BTW. This was Win2k AS and SQL2K EE.
-
Again personal opintion, but I would move all of the database objects (system, db, logs) on to the SAN and just leave the OS, pagefile and binaries on the local raid.
-
It is not possible to give generic advice about placing TempDB data and logs on the same controller. You will have to test this using your own hardware.
However, you can get some quick answers about if this is causing you a problem: Does your system meet its performance SLAs? Is there any indication that the placement of TempDB data and logs on the same controller is threatening your SLAs? Is your TempDB under 2GB in size?
If you are meeting your SLAs and have a small TempDB, then you will probably gain very little by moving it. There are almost certainly bigger gains to be had for the same effort in fixing bad application SQL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
There is very little on newsgroups or in Microsoft documentation about how W2000 or W2003 manages disk I-O. Certainly this is true when compared to mainframe z/OS, where ther are reams of information about how heavily you can load I-O, how it degrades, etc.
However, there are indications that like most other operating systems, Windows has to do some serialisation of I-O to ensure write integrity. This means that all I-O requests to a given mount point are serialised through a common control block in memory. This is done to ensure two write commands to the same disk sector are performed in the order issued, and that any interleaving read requests get the correct copy of data.
This stack is not infallable, as searches for 'stale reads' and 'lost writes' and 'heavy load' will show.
What all this means is that Windows has an effective limit to the amount of I-O it can handle via a single mount point, because of the serialisation. As the serialisation involves only CPU and memory, a faster CPU will give you greater throughput (but multiple CPUs will not...). Also, performance will follow a standard queue curve, with performance getting worse exponentially as you get near the throughput limit.
Therefore, although the actual performance metrics are not known, it is possible that splitting your database into multiple files, each on a separate mount point, will give you better performance than using a single file on a single mount point.
However, if you do this, you will probably need a SAN to see the improved performance. Most DAS systems operate near to their hardware I-O limits and splitting I-O over many drive letters will often hit the the queue curve wall.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Does anyone have a good handle on interpreting physical disk performance monitoring statistics in a SAN/disk array environment. I'm trying to figure how our current SAN setup (three RAID-5 SAN logical drives, one housing our largest database mdf, one housing smaller mdf's including system db's plus transaction logs, and one housing our bak files) might be affecting large delete and insert jobs--from reading the available information I have, it appears that we should place no value in high % disk time values and place more value on the current and avg. disk queue lengths (adjusted for the number of spindles, which is 6 for our disk set plus one for parity = 7)---so as long as these values are under 12-14, then those two values appear to be OK.
Is this a false assumption on my part?
NOTE: this is a Win2K server (4 Xeon processors, 4 GB memory) running SQLServer 2000. We have little control over some aspects of the database itself because it's a 3rd party application and database with intentionally little indexing going on--no primary keys, no foreign keys, no clusters--due to concerns about backward compatability and large data imports (as far as I understand the motivation) so I'm looking to max'ing the hardware environemtn as much as possible since I have some control.
-
thanks Ed. This is not causing a problem, performance is quite good. I was just wondering if it was a good idea.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
I think everyone is on the right track with the RAID levels to use for data, log and tempdb, but for the best performance you need to look at what makes up a SAN LUN that is presented to the OS. Is the LUN one disk of 32 gig or is the LUN made up of 16 disks of 2gig stripes.
If the LUN is made up of 16x2gig stripes, what shares the other stripes on each of the 16 disks. Imagine what the performance would be like if the other 15 stripes on the disk were for a data warehouse that allowed adhoc user queries that were always being hammered. Your OLTP application would perform like a dog.
Unfortunity the dba doesn't realise this and when he requests additional disk space from the SAN administrators he will be given the next available LUN regardless of what else is on it. This results in inconsistent and bad performance. The vendors of SAN systems say the performance will even out with the use of cache. This is true to a point. You need to get the best out of the disk and then let the cache provide additional performance.
The added difficulty is that perfmon can't drill down to the disk (spindle) level in a SAN environment. so you cannot see that one particular spindle is overloaded. This can only be done with the help of your SAN admin people and there admin software.
The point I'm trying to make is that you need to work with the SAN administrators and plan the layout of data of all applications. Stripe the LUNs across as many disks as possible.
In our SAN we use 32gig LUNs, each of these is made up of 8 stripes of 4gig on separate disks. We then stripe these at the OS, using either 4 or 5 LUNs (128gig or 160gig), so our 160 gig LUN could be across 40 disk spindles. when we do a backup we get a sustained throughput of 143mb/sec, and I have seen as high as 153mb/sec. Our SAN is shared by a number of SQL databases, and our mainframe. The mainframe uses 2 gig stripes.
I would be interested in hearing from other users of SANs, what the sustained throughput is on their systems. This can be achieved by performing a SQL Server backup and looking at the output from the backup command. Be careful to flush the cache first by performing a backup of another database first. If we perform a backup of the same database twice in a row the second backup throughput is in the order of 350mb/sec as it coming from cache. You also need to be able to off load the data faster than what you can read it at. We use litespeed to compress the data to 25% of its original size as it is written to disk.
I hope to see some perfomance numbers and I have provide you all a bit of food for thought.
Mark S
-
I would like to thank all of you for sharing your experiences with me.
It will help me in setting up the SQL environments on the SAN.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply