Scaling SQL Server 2012 - Part 1
Learn how to identify and avoid practical architecture, design, and implementation issues that can limit the scalability of a SQL Server 2012 workload. This course is applicable to anyone who is responsible for improving the performance and scalability of a SQL Server workload, with most topics applicable to SQL Server 2005 onward.
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 will show you that there are a huge number of things you can do to improve performance and scalability before you should even think of scale-up or scale-out. The bulk of the course describes architecture and design issues in both the application and the database that can affect scalability, along with demonstrations of some of the issues and detailed scripts to help you find the issues in the first place. The course ends with a section on common indexing problems that can drastically limit a workload's scalability, along with how to identify and fix them. Save your company a bunch of money by postponing the scaling decision through fixing your existing scalability bottlenecks, not ignoring them! This course is perfect for anyone who is responsible for improving the performance and scalability of a SQL Server workload, with most topics applicable to SQL Server 2005 onward.
Table of contents
- Introduction 2m
- Postponing the Scaling Decision 1m
- Preventing Scalability Problems 2m
- Finding Your Bottlenecks and Improving Them 2m
- Demo: Sample 911 Emergency Script Part 1 11m
- Demo: Sample 911 Emergency Script Part 2 15m
- Using Diagnostic Tools to Find Bottlenecks 4m
- Common Performance Bottlenecks 2m
- Working with Consultants 1m
- Working with Developers 2m
- Demo: The Bad Man Queries 6m
- Working with Stakeholders 2m
- Summary and Return on Investment 3m
- What is Next? 1m
- Introduction 2m
- Application Architecture and Design Issues 2m
- Application Refactoring 2m
- Overuse of ORM Code Generators 1m
- Using Ad Hoc T-SQL from the Application 2m
- Overuse of XML Data 2m
- SQL Server Scalar User-Defined Functions 2m
- Demo: T-SQL Scalar UDFs 6m
- Too Much Business Logic in the Database 1m
- Using DML Triggers for Application Logic 1m
- Using CLR Assemblies for Application Logic 2m
- Requesting Too Much Data from the Database 2m
- User-Controlled, Long-Running Transactions 1m
- Overuse of New SQL Server Features 2m
- Doing Row-by-Agonizing-Row Processing 2m
- Demo: Set-Based vs. RBAR Processing 3m
- Not Having a Data Archiving Strategy 2m
- Not Having a Reporting Strategy 2m
- Not Using Middle-Tier Caching 2m
- Common Middle-Tier Caching Tools 1m
- Summary 1m
- What is Next? 1m
- Introduction 2m
- Database Architecture and Design Issues 1m
- Using Default Database Properties 4m
- Demo: Creating a New Database 7m
- Data File Physical Layout 3m
- Transaction Log File Physical Layout 3m
- Transaction Log File Configuration 3m
- Poor Database Normalization 2m
- Lack of Check Constraints 2m
- Lack of Foreign Keys 1m
- Heap Tables 1m
- Inappropriate Data Types 2m
- Wide Data Types 2m
- Poor Query Design (1) 2m
- Demo: Implicit Conversions 5m
- Poor Query Design (2) 2m
- SQL Server is a Database Server! 3m
- Not Using T-SQL Stored Procedures 3m
- Overuse of ORM Query Generators 2m
- Additional ORM Query Considerations 2m
- Summary 2m
- What is Next? 1m
- Introduction 1m
- Common Indexing Issues 1m
- Too Many Indexes 2m
- Demo: Finding Unused Indexes 2m
- Having High Impact Missing Indexes 3m
- Demo: Finding Missing Indexes 6m
- Finding Missing Index Warnings 1m
- Missing Index Warning Example 1m
- Demo: Finding Missing Index Warnings 2m
- Database Tuning Advisor Issues 1m
- Demo: Using the DTA Command-Line Utility 4m
- Having Poorly-Maintained Indexes 1m
- Improper Index Maintenance 2m
- Using the SQL Maintenance Plan Wizard 2m
- Common XML Index Issues 2m
- Common Spatial Index Issues 2m
- Not Using Data Compression 2m
- Data Compression Considerations 2m
- Demo: Data Compression 10m
- Not Using Columnstore Indexes 1m
- 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