Database Underground | Sean McCown » The Multi-Tier Storage Solution

September 12, 2007 | Comments: (0)

The Multi-Tier Storage Solution

TAGS: SQL Server

For many DBAs performance is a huge problem. I know I've currently got systems that have their bottlenecks. One of the bottlenecks in a lot of SQL Server systems is in tempdb. There were a lot of things in SQL2K that relied on tempdb and Yukon increased that dependency by several fold (or made it worse depending on how you want to look at it). And while I don't really know yet if Katmai is actually going to increase it even more, it certainly isn't going to decrease it.
So in a way, Microsoft is actually working to create a bottleneck by not giving us multiple tempdb areas that we can use to segregate our users into. This really is one of those areas where Oracle beats SQL hands down because anyone who's ever tried to manage tons of users or tried to manage a large job in the middle of normal processing knows the advantage of having a separate tempdb area.

Enter tiered storage... I think that these new tempdb requirements are going to drive more and more DBAs to consider tiered storage for their servers. The simple truth is that tempdb needs to be as fast as possible, and it doesn't get backed up, so you've got some wiggle room when it comes to configuring your tempdb area. And to that end, you should start considering SSDs (solid state disks) for your tempdb area. SSDs are magnitudes faster than spindles so performance should go through the roof. And since tempdb services every DB on the server, then it needs to be as fast as possible. And with SSDs you can pretty much guarantee that the bottleneck won't be tempdb.

Don't get me wrong though. MS isn't the only one driving this need. Pretty much any DB could really take advantage of SSDs. The problem with SSDs though is that they're very Very VERY expensive. But look at the difference in raw hardware cost vs maintenance and power consumption. SSDs use considerably less power than hard disks so you'll be saving money on that side. It also takes far fewer SSDs to get good performance than it does hard disks. I can't count the number of times we had to build an array of several dozen spindles just to get the performance of tempdb to where it could actually support the load on the system. That's the perfect application for SSDs. Instead of buying a couple hundred SCSIs, you might be able to get away with just a few SSDs. Of course that's only if you're doing it for performance.

Yukon and Katmai aren't the only ones that can benefit from SSDs either. SQL2K still uses tempdb and while it doesn't use it as heavily as the newer versions do, it's still quite often the bottleneck. So don't be afraid to pop some of those expensive boys in your SAN.

This isn't anything all that new. We're already tiering storage configuration. We don't use the same RAID configuration for every DB or for every file type. You don't use RAID5 for tempdb, and you don't use RAID0 for your logs. So now all we're gonna have to start doing is mixing up the types of disks we use for different purposes. Use your SCSI for your main DB app. Use SSDs for your tempdb and some of your lesser log files. Use SATAs for your backups before they get pushed to tape. You know, start tiering your storage to fit the requirement of the intended application.

So anyway... this is just food for thought. The next time you've got a tempdb bottleneck just consider all of your options before buying 100 more spindles to get the performance you need. And I think as SSD technology gets better, and as the price comes down a little, you'll start to see this being the norm. Of course, I don't think it's the solution for every aspect of your DB server. I still have serious reservations against putting my main data files on SSDs, but I'm sure that too in time will pass.

Posted by Sean McCown on September 12, 2007 09:48 AM

RATE THIS ARTICLE: