SQL Server: Improving Storage Subsystem Performance

This course shows how to properly configure SQL Server storage for the best performance, scalability, and reliability with several different types of workloads, and how to measure your storage performance.
Course info
Rating
(45)
Level
Intermediate
Updated
Aug 3, 2016
Duration
2h 13m
Table of contents
Course Overview
Introduction
Measuring and Analyzing Storage Subsystem Performance
Testing and Benchmarking Storage Subsystems
Understanding Storage Types Suitable for SQL Server Workloads
Understanding the Effects of RAID Levels on SQL Server Workloads
Improving Storage Subsystem Performance
Description
Course info
Rating
(45)
Level
Intermediate
Updated
Aug 3, 2016
Duration
2h 13m
Description

Storage subsystem performance can often be the biggest factor affecting a SQL Server workload. This course, SQL Server: Improving Storage Subsystem Performance, shows you how to detect and correct important and common storage misconfiguration issues with SQL Server. You'll get to see how to measure and analyze storage subsystem performance, as well as and how to change SQL Server configurations for the best performance. You'll also get to see common RAID levels and how to choose an appropriate one for the SQL Server workload. By the end of this course, you'll be prepared to properly design and configure SQL Server storage subsystems for various types of SQL Server workloads for the best performance, scalability, and reliability. The course is relevant to all current versions of SQL Server, and demonstrates some new features from SQL Server 2014 and SQL Server 2016.

About the author
About the author

Glenn works as a Principal Consultant at SQLskills.com. He has been a SQL Server MVP since 2007, and he is also an Adjunct Faculty member at University College - University of Denver. He is the author of the book SQL Server Hardware (Redgate 2011), and he wrote chapters for both SQL Server MVP Deep Dives books.

More from the author
More courses by Glenn Berry
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
My name is Glenn Berry, and welcome to my course, SQL Server: Improving Storage Subsystem Performance. I'm a principal consultant with SQL skills as well as a Microsoft data platform MVP, and this topic is one that I've been presenting and writing about for many years, because I feel so passionate about this subject. In the course of my career as a database administrator and consultant, I've never had anyone tell me that their database or database server was too fast. It's all too common for people to complain about slow application performance, and often jump to the conclusion that any performance or scalability issue must be caused by your database server. Quit often, application and database performance issues are actually caused by poor storage subsystem performance. I want to give you the concepts and tools that you can use to help prevent your storage subsystem from being a performance or scalability bottleneck. I also want to show you a number of techniques that you can use to reduce the load on your storage subsystem. We cover how to measure and analyze your storage subsystem performance, how to test and benchmark your storage subsystem, and how to improve your storage subsystem performance. If you work with SQL server in any capacity, whatever your level, this course is relevant to you. Experience with hardware and storage subsystems is helpful, along with a basic understanding of how SQL Server works. I hope you'll join me as we explore the world of storage subsystems with the SQL Server: Improving Storage Subsystem Performance course here on Pluralsight.

Introduction
This is Glenn Berry with sqlskills. com. I'm recording this course for Pluralsight, and this is SQL Server Improving Storage Subsystem Performance. So what are we going to to talk about in this module? Well, first we'll be talking about the overall importance of storage subsystem performance for SQL Server. So many instances that I see out in the field have major storage bottleneck. So this is a really important subject for SQL Server. Then we're going to talk about the three main metrics for storage performance. Then we'll get into some more detail about what the rest of the course will cover. Storage subsystem performance is absolutely critical for SQL Server. Database performance is very often limited by your storage performance. You can have the very best processors and lots of RAM, but often, you just are limited by your storage performance. So that's why this course is very important. Many SQL Server workloads just won't fit inside the SQL Server buffer pools, so that means you're going to be have to pulling data in off of the storage subsystem, into the buffer pool, on a pretty regular basis. Also, different types of SQL Server workloads have different storage requirements, whether it's really good sequential performance, or really good random performance. And you're going to see different things when different activities are happening with your workload. Finally, many DBAs are just simply not very knowledgeable about storage. Storage is some mysterious black box for them. And I want to try to break through that and give you the knowledge you need to analyze and improve your storage subsystem performance. So the bottom line for all this is that don't ignore your storage. It's extremely important for your overall performance, and that's going to affect how your users perceive your system and how happy you're going to be in your day-to-day work.

Measuring and Analyzing Storage Subsystem Performance
This is module two, measuring and analyzing storage subsystem performance. So what am I going to cover in this module? We'll start off talking about some of the different methods you can use at the operating system level and at the SQL Server level to measure your storage performance. These include things like PerfMon and Resource Monitor that are operating system tools, then we'll show you some things that you can do inside of SQL Server with DMV queries and other queries to measure your I/O subsystem performance.

Testing and Benchmarking Storage Subsystems
This is module three, Testing and Benchmarking Storage Subsystems. So what will we cover in this module? We'll start off talking about various methods you can use to test and benchmark your storage subsystem. This includes things like Microsoft DiskSpd, CrystalDiskMark, and then tools you can get from the various Storage vendors to actually test your storage subsystem. Finally we can use SQL Server to test the storage subsystem performance, and that will help you get around sometimes you have storage administrators who don't set up some of these other tools. So Microsoft DiskSpd is a new tool that replaces SQLIO, SQLIO is actually deprecated and Microsoft has removed it from the downloads that are available on the internet. So DiskSpd is actually a much better tool. It doesn't require or use SQL Server for its testing, and it gives you much more detailed test data when you run the test to see what's been going on with your storage subsystem. Now you can use an old fashioned command prompt, or you can use PowerShell to run your tests.

Understanding Storage Types Suitable for SQL Server Workloads
This is module 2, understanding storage types that are suitable for SQL Server workloads. So in this module, we're going to talk about the different types of storage that are commonly used for SQL Server. Then we're going to talk about some of the strengths and the weaknesses of each type of storage as they relate to different types of SQL Server workloads, and then finally, we're going to talk about some of the considerations for getting the best performance from each type of storage, specifically for SQL Server. So we've got several different types of storage that are commonly used by SQL Server, and these include internal SAS or SATA drives. Modern servers typically have up to 24 internal drive bays, so you can have quite a few internal drives there. You also have PCIe flash-based, add-in storage cards, such as Fusion-io cards or cards from Intel. Next, we've got direct-attached storage, where you've got a number of drives and an external enclosure that's directly attached to one or more RAID controllers in your host database server. Then we have storage area networks. Next, we've got SMB 3. 0 file shares, which you can use with SQL Server 2012 or newer. Then we've got Scale-Out File Servers with Windows Server 2012 R2, and then with Windows Server 2016, we've got Storage Spaces Direct, which is another way of using Scale-Out File Servers using commodity hardware and getting much better performance than you might have seen in the past.

Understanding the Effects of RAID Levels on SQL Server Workloads
This is module five, Understanding the Effects of RAID Levels on SQL Server Workloads. So what we're going to cover in this module, we'll start off talking about how the RAID level you decide to use is affected by the different kinds of SQL Server workloads that you're going to run into. Then we'll talk about how your availability requirements are going to affect your desired RAID level. And then finally, we'll talk about how performance and redundancy are affected by the RAID level that you choose for each one of your logical drives.

Improving Storage Subsystem Performance
This is Module Six, Improving Storage Subsystem Performance. So what are we going to talk about in this module? We'll start off discussing things that you can do to improve your storage subsystem performance. This includes things such as index and query tuning, going through and making sure that your SQL Server instance configuration is set up properly, and making sure your operating system is set up properly, and then finally, making sure your hardware and storage configuration is set up for the best performance and scalability. So when you go and do this, there's actually multiple layers of optimization that you need to think about. Multiple things in each layer that you can make sure that are set up correctly that quite often are not set up correctly by default. So, you can go through and investigate and address each one of these in each layer depending on your system. So, for example, in some cases you may not be able to do index and query tuning because you have a third party application and you're not allowed to touch that. But you certainly should investigate and see if there's any opportunities there, and even if you can't do it, perhaps you can bring it to the attention of the vendor of that application. Now more often you can make sure that your SQL Server Instance Configuration settings are all set up properly and you certainly should be able to go in and make sure your operating system is set up properly and that your hardware and storage is set up properly for the best performance of scalability, because nobody's ever come to me, ever in my career, and complained that a database server is too fast. I've never heard that. They'll always complain that it's too slow, so everything you can do to attack each one of these layers and make things better is a good thing to do.