Welcome back to SQL University! I hope you enjoyed your Spring Break last week. Today’s topic is all about storage, so let’s get started.
tempdb
The first thing you need to understand is that SQL Server reads all data into memory in order to perform operations. Often you will hear people say how they could load their entire database into memory and that should make everything fast. Well, yes and no. See, ultimately the data needs to be written to, and read from disk at some point. That means you need to know more about the storage subsystem that your database uses.
Do you know your RAID levels? If not, now is the time to learn. But do not just learn their definitions, learn which ones are best for write activity and for read activity. Chances are you will be asked your opinion on aligning disks for a server at some point, and you may need to use a less than desirable RAID level, but you need to understand the differences before things get built. A great resource for you to get started on digesting is this blog series by Kendall van Dyke on RAID levels and performance.
While we are on this topic I want you to go to one of your servers right now and examine the RAID configuration. Use the Disk Management console and just browse around to see how your server was built. If you didn’t build it yourself, I bet you find that someone simply slapped together a bunch of disks as RAID5 and gave you some logical partitions to share for your data and log files (assuming you asked to have those on separate disks).
Start poking around, and start asking some questions.
msdb
Once you are familiar with the basics you need to learn how to measure disk performance. There are a lot of ways for you to get the job done here using DMVs and/or Perfmon for example. But I like to keep things simple, especially if I have a few hundred instances to manage. If that is the case then I cannot spend time reviewing all of those results in great detail to determine if there is a problem.
So what I like to do instead is to configure a policy using PBM to alert me when my disk response time is exceeding a specified threshold. How do I do this? Simple, I follow these instructions in Bart Duncan’s blog post. I suggest you do that as well.
Configuring the policy is a good way for me to be alerted to and focus on the servers that are having slower than desired response times. If you have a lot of servers to manage, using policy-based management for this is very efficient.
model
At this level you have learned the basics of RAID and also some basic monitoring. Now go one step further: learn about SAN storage. I am not asking for you to become a SAN administrator, but you should have a basic understanding of how your SAN is configured in your shop.
If you want to be at the model level then just knowing about storage is not enough; you also need to know about how database design can affect performance. No, not just data and logs on different disks, I am talking about things like filegroups and partitions. Some people think that in order to increase your disk I/O throughput you need to rebuild or reconfigure the disk subsystem. And while that is certainly one possible solution what most people do not realize is that some modifications to their database design could have a dramatic increase in disk I/O throughput as well.
Do yourself a favor and read that whitepaper. You could save yourself and your shop a lot of time and money that would have typically been spent on new hardware.
master
Like any good master of a craft, you should have some level of hands on experience. Go find yourself an opportunity to rack a server yourself. Sit by your SAN Administrator when they are configuring LUNs for your database server. This is especially true if your shop is switching over to be more virtual using Hyper-V or VMware. Become familiar with the different types of storage that may be connected to your server, and don’t overlook disk partition alignments.
And once you feel comfortable that you have enough knowledge, start sharing it with others.
resourcedb
Whenever I have questions about storage I just think about the group I call “Five Guys Storage and Fabric”. They are, in no particular order, the following:
1 thought on “SQL University – Storage Week”