SQL Server: Improving Storage Subsystem Performance

Pluralsight

Course Summary

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 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.

Course 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.

+
Course Syllabus

Course Overview
- 1m 40s

—Course Overview 1m 40s

Introduction
- 10m 1s

—Introduction and Importance of Storage Subsystem Performance 1m 52s
—Latency and IOPS Metrics 2m 50s
—Sequential Throughput 2m 2s
—Course Objectives 2m 9s
—Course Focus and Summary 1m 5s

Measuring and Analyzing Storage Subsystem Performance
- 14m 38s

—Introduction 0m 32s
—Windows Performance Monitor and Windows Resource Monitor 1m 40s
—Demo: Windows Performance Monitor 1m 22s
—Demo: Windows Resource Monitor 1m 7s
—SQL Server Diagnostic Queries 0m 40s
—Demo: SQL Server I/O-Related Diagnostic Queries 5m 47s
—Demo: SQL Server I/O-Related Diagnostic Queries - Part 2 3m 5s
—Summary 0m 21s

Testing and Benchmarking Storage Subsystems
- 17m 11s

—Introduction and Microsoft DiskSpd 1m 6s
—Demo: Using Microsoft DiskSpd to Test Storage 3m 39s
—CrystalDiskMark 3m 16s
—Demo: Using CrystalDiskMark to Test Storage 1m 54s
—Storage Vendor Tools 1m 18s
—Demo: Using Vendor Tools to Test Storage 1m 5s
—Storage Admins May Not Accept Benchmarks 0m 39s
—Demo: Using SQL Server to Test Storage 3m 42s
—Summary 0m 27s

Understanding Storage Types Suitable for SQL Server Workloads
- 16m 32s

—Introduction and SQL Server Storage Types 1m 32s
—Internal SAS/SATA Drives and Direct-attached Storage (DAS) 4m 21s
—Storage Area Network (SAN) 3m 33s
—PCIe Flash Storage and SMB 3.0 File Shares 3m 39s
—Storage Spaces Direct and S2D 3m 1s
—Summary 0m 24s

Understanding the Effects of RAID Levels on SQL Server Workloads
- 15m 4s

—Introduction 0m 33s
—Common RAID Levels 4m 59s
—RAID Does Not Replace Backup/Restore Plans 1m 50s
—Primary SQL Server Workload Types 4m 34s
—SQL Server File Types and Workloads and RAID Level Choice 2m 33s
—Summary 0m 33s

Improving Storage Subsystem Performance
- 58m 17s

—Introduction 1m 44s
—Workload Tuning and Index Tuning 2m 0s
—Demo: 'Bad Man List' Queries 4m 46s
—Demo: Queries to Find Index Tuning Opportunities 6m 2s
—Demo: Using Data Compression and Columnstore (1) 3m 16s
—Demo: Using Data Compression and Columnstore (2) 3m 27s
—Instance-level and tempdb Configuration Settings 2m 49s
—Demo: Changing Instance-level Configuration Settings 4m 26s
—SQL Server Database Property Settings 2m 14s
—Demo: Changing Database Properties 2m 36s
—ALTER DATABASE SCOPED CONFIGURATION 1m 51s
—Demo: ALTER DATABASE SCOPED CONFIGURATION 3m 12s
—Using CPU-Z to Check Power Management 0m 39s
—Demo: Using CPU-Z to Check Power Management 2m 33s
—Demo: Windows Power Plan 1m 17s
—Demo: Using Resource Governor to Limit I/O 6m 25s
—Demo: Granting OS Rights to SQL Server 2m 22s
—Server Hardware Configuration Settings 3m 52s
—Module and Course Summary 2m 38s

Course Fee:

USD 29

Course Type:	Self-Study
Course Status:	Active
Workload:	1 - 4 hours / week

This course is listed under Development & Implementations , Data & Information Management , Networks & IT Infrastructure , Operating Systems , Quality Assurance & Testing , Hardware and Server & Storage Management Community

Attended this course? Write a Review

Course Fee:

USD 29

Course Type:	Self-Study
Course Status:	Active
Workload:	1 - 4 hours / week

IT Career Development Platform

SQL Server: Improving Storage Subsystem Performance

Pluralsight

Course Summary

Course Description

Course Description

Course Syllabus

Course Type:

Course Status:

Workload:

SQL (Structured Query Language)

Redundant Array of Independent Disks (RAID)

Testing

Storage Area Network (SAN)

Server Message Block (SMB) Protocol

SAS

Peripheral Component Interconnect Express (PCIe)

Flash storage

Distributed Antenna System (DAS)

Attended this course? Write a Review

Course Type:

Course Status:

Workload: