The Multi-Tier Storage Solution

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 incr

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.