SQL Server: Scaling SQL Server 2012 and 2014: Part 2
Learn how to avoid configuration issues from the hardware to the database that can limit the scalability of a SQL Server 2012/2014 workload, along with features and design methodologies for scaling up and scaling out.
What you'll learn
When considering how to improve the scalability of a SQL Server workload, many people jump to the conclusion that scaling up or scaling out are the only answers. This course, SQL Server: Scaling SQL Server 2012 and 2014: Part 2, builds on the earlier "Scaling SQL Server 2012 - Part 1" course and will show you a huge number of things you can do to improve performance and scalability. First, you'll learn about configuration issues in hardware, storage, Windows, and SQL Server, and you'll also see demonstrations of some of the issues and tools to help you find the issues in the first place. Finally, you'll get an explanation of some of the techniques and features you can use to scale up or scale out, once you've configured the system correctly. By the end of this course, you'll be able to improve the performance and scalability of a SQL Server workload.
Table of contents
- Introduction 1m
- Statistics-related Database Properties 3m
- Recovery Model and Compatibility Level 4m
- Undesirable Database Properties 5m
- Miscellaneous Settings to Consider 5m
- Demo: Checking Database Properties 2m
- Database Filegroup Considerations 2m
- Database File Layout Considerations 2m
- Database File Size and Growth Settings 2m
- Demo: Finding Files That Have Percent Growth 1m
- Demo: Changing Database Properties with T-SQL 1m
- Module Summary 1m
- Introduction 1m
- Operating System Issues 2m
- Negotiated Versions of SMB 1m
- Lock Pages in Memory (LPIM) 1m
- Perform Volume Maintenance Tasks 2m
- Demo: Granting OS Rights to SQL Server 2m
- Windows Page File Size 1m
- Global Trace Flags 4m
- Tempdb Configuration Settings 2m
- Instance-level Configuration Settings 4m
- Demo: Changing Instance-level Configuration Settings with T-SQL 3m
- Best Practices for Performance and Scalability 2m
- Module Summary 1m
- Introduction 0m
- Common Storage Subsystem Issues 2m
- Common SQL Server Workload Types and I/O Access Patterns 2m
- Demo: I/O Related DMV Queries 3m
- Common DMV I/O Query Result Patterns 2m
- Sizing Your Storage Subsystem 3m
- Choosing Storage Types and Subsystem Design Tips 3m
- CrystalDiskMark 5.1 and Microsoft DiskSpd 1m
- Demo: CrystalDiskMark 5.1 3m
- Demo: Microsoft DiskSpd 3m
- Module Summary 0m
- Introduction 1m
- Common Hardware Issues 1m
- Key BIOS Settings 2m
- PCIe Throughput Limits 2m
- Power Management 2m
- BIOS Power Management and Using CPU-Z 2m
- Demo: Using CPU-Z 3m
- Demo: Windows Power Plan Setting 2m
- Processor Selection for SQL Server 1m
- Intel Tick-tock Release Strategy 2m
- Intel Processor Family Tree 2m
- Preferred Haswell-EP and Haswell-EX Processors 3m
- Module Summary 1m
- Introduction 0m
- Buffer Pool Extensions 2m
- Demo: Buffer Pool Extensions 4m
- Resource Governor Enhancements 1m
- Demo: Resource Governor Enhancements 5m
- Clustered Columnstore Indexes 1m
- Demo: Clustered Columnstore Indexes 3m
- Delayed Durability 1m
- Demo: Delayed Durability 7m
- New Cardinality Estimator 1m
- Demo: New Cardinality Estimator 4m
- Module Summary 1m
- Where to Go Next and Course Summary 1m
About the author
Glenn works as a Principal Consultant at SQLskills.com. He has been a SQL Server MVP since 2007, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, and performance tuning. He is also an Adjunct Faculty member at University College - University of Denver, where has been teaching since 2000. He has completed the Master Teacher P... more