3

I'm configuring a Microsoft SQL Server installation which will use a SAN for storage. For SAN storage I have:

  • A small number of SSD disks
  • A larger number of 10K disks

My SAN supports storage tiering.

I'm on the fence about how to use the SSD disks. Here are the options that come to mind:

  • Option 1: Create tiered storage with the SSDs and 10K disks and use auto tiering to move highly accessed data to the SSDs. Then break that storage pool up into several different drives.

  • Option 2: Place key (I/O intensive) data on the SSDs and throw everything else on the 10K disks.

Questions

I don't have enough SSDs so that everything can reside on SSDs. So what's most important performance wise for SSD storage? tempdbs? LDF files? MDF files? My database is more write intensive than read intensive (if that makes a difference).

Secondarily, are there any best practices I should follow when setting up the drives?

Should I have separate drives for each tempdb? LDF files? MDF files?

What about the system databases like master, model etc., should those go on their own dedicated drives or maybe on the C: drive?

Server disk activiity

By the way, here is a screenshot of our existing database server disk activity. It seems the tempdbs are the most read/write intensive followed by our MDF file and surprisingly the LDF file seems to have the least I/O.

Server Disk Activity

Any advice would be appreciated.

4

First and foremost, look at your SAN vendor's documentation for recommendations regarding storage for SQL Server. This really should be your first step as the vendor hopefully has done a lot of this general analysis for you.

If the documentation doesn't mention hosting databases, do some more digging before you choose to go with Tiered Storage. Understand how data migration between tiers works on your SAN and how frequently it occurs. Beware of anything that will run a scheduled job that tracks how active sectors may be during a time frame (often a 24 hour period, but this if often configurable). I've found that in this scenario when it comes time to migrate the data, that data is often slow to access during the operation. You'll want to insure this process runs during a period of low db activity (which means don't run this during the same time you're backing up your database). If you have a database that's active all day, I'd recommend you NOT use tiered storage at all if this is how tiering works on your SAN. Another problem with migrating data between tiers when this type of analysis is performed is that often times data access patterns change and are not consistent throughout the day. This can result in data living on the faster tiers that really shouldn't be there. For instance, when you run Index Maintenance overnight the SAN may flag that data as hot and migrate it to a higher tier. If those indexes don't get accessed during the next migration analysis window, you're now wasting I/Os on idle data. Depending on your database usage patterns, this could happen quite often where your data is getting migrated to the faster tier only to sit there idle.

Again, look at your vendor documentation. I would hope they clearly outline the recommended approach needed for their hardware. Also, give SAN Storage Best Practices for SQL Server, from Brent Ozar a read. That goes into much more depth on some best practices and is well worth the read.

1

Well, generally:

  • LDF are latency sensitive but linear. A SAN WriteBack cache (or a local Raid controller with BBU and write back) is good enough to handle the latency, the throughput can be handled by a decent HDD.
  • Tempdb CAN be Latency AND throughput sensitive. Generally I would NOT put those on a SAN at all - makes ZERO sense. Use local M.2 NVM discs. Grab a PCIe Raiser card for them if yo u have to. THe point is that TempDB is regenerated on every restart - so there is nothing "lost" when the machine fails over. AND a decent M.2 SSD has a TON of bandwidth for those (onless you se 128g fiber). THat said, most TempDb do not need this as they are never challenged. Depending on what you do with the data, tempdb may have quite a quiet live.

For the rest I would go with storage tiering IF that is sensible. Alternatively use multiple File Groups and put hot ones on SSD if that is by table (using partiioning will get clumsly). Lots depends on the data - but tiering may be the best solution (also from an ease of use point of view).

  • The local server doesn't have SSDs and I probably can't purchase them. Given those constraints and the above screenshot showing tempdb has far more I/O than our MDF or LDF files would you still suggest those shouldn't go on the SAN? – Brad Aug 30 '17 at 17:22
  • You also need to consider server availability when you are considering storing tempdb locally on NVMe drives w/o RAID. If these drives go down your server will go down as well. So there will be downtime unless you have cluster or availability group setup. – Alexander Sharovarov Aug 30 '17 at 17:57
  • FIrst, I never said no Raid. Second, if you want updime you already have a cluster/aoag. And TempDb is reinitialized on ever restart anyway ;) On a HA setup very very little is lost, but altency really drops. – TomTom Aug 30 '17 at 18:03
  • TomTom, I didn't know that you can have M.2 NVM in RAID 1. I've seen them in RAID-0 only. I guess there could be RAID controllers for NVMe now on the market since last time I shopped for storage. ;) Could you please share a model just for my knowledge. Thanks – Alexander Sharovarov Aug 30 '17 at 18:08
  • You can have a ton of them - you can also use sofware Raid 1, which any windows OS Supports ;) NVM is also used in 2.4" form factor these days. The whole high end IO area seems t omove towards it. – TomTom Aug 30 '17 at 18:14
1

Well, I would say an obvious thing first. Test both scenarios and see what's the performance difference between Tiered and dedicated SSD architecture.

Then think about downsides of having key data to be SSDs if SSD storage is limited. How limited the SSD space is? When you expect to run out of space based on your current growth of the "Key data"? What are you going to do when you run out of space on SSDs? You potentially will be wasting some SSD storage to provide redundancy. Compare all that potential headache to the performance difference.

In general, SSDs are good for Random Access operations. The most benefit you get Random Reads. If your application is write-intensive and doing a lot of bulk writes (which is possible since you don't see much load on LDFs) you might see a minimum performance improvement on SSDs compared to fast spinning disks, but if your application does a lot Random Writes then it might be a different story. So you need to test it your production workload.

  • You are aware that he talks abut databases, which - besides the LDF files - pretty much AWLAYS are randomized access? Which is why for generations DBA's have measrured IOPS. And SSD rule there. – TomTom Aug 30 '17 at 18:15
  • Agree. But there is a difference between random 4K writes, "random" 64K or 256K "random" writes. You can write pretty fast 256K random writes on spinning disks. Here is some reading on the topic: blog.purestorage.com/what-is-sql-servers-io-block-size – Alexander Sharovarov Aug 30 '17 at 18:28
  • But I am with you. Most of my DB storage is SSD and I don't complain. But it seems Brad doesn't have a luxury of all SSD. And trying to optimize his resources. And I feel that it might not worth it and Tiered approach would be just fine. – Alexander Sharovarov Aug 30 '17 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.