Practical Guide to Diagnosing, Measuring and Tuning SQL Server: Tips for Testing and Optimizing Database Performance

What you'll learn

Use SQL Server monitoring tools in testing to track performance metrics proactively and identify bottlenecks before they impact application performance.

Design and implement targeted performance testing plans to ensure SQL-based applications are scalable and responsive under varying loads.

Assess and interpret query performance and execution plans to gauge their impact on SQL Server performance during testing, refining them as needed.

Test indexing strategies to measure data retrieval speed and query efficiency, ensuring optimized access patterns for high-demand scenarios.

Refactor and fine-tune SQL queries to ensure they meet stringent performance benchmarks and respond effectively to real-world loads.

Develop and execute comprehensive performance tests to measure query responsiveness and validate SQL-driven applications' scalability and robustness.

Analyze and test code performance within a structured testing strategy to pinpoint inefficiencies and optimize SQL interactions.

Leverage effective data structures in .NET applications to streamline data handling and achieve performance objectives during high-demand testing.

Requirements

Basic SQL Knowledge: Familiarity with SQL syntax and commands (e.g., SELECT, JOIN) is helpful.

Some experience with SQL Server is ideal, but you don't need to be an expert.

Knowing the basics of performance testing will enhance your understanding.

Access to SQL Server (any edition) on a personal or work computer to practice the course exercises.

Description

Are you ready to unlock the full potential of SQL Server performance? This hands-on course, designed for testers and developers, delves into diagnosing, measuring, and optimizing SQL Server performance to ensure efficient, robust applications. You'll learn practical techniques to detect bottlenecks, optimize queries, improve indexing, and streamline code to handle larger datasets and higher loads.Throughout this course, you'll work with real-world case studies that highlight common performance issues, such as inefficient data structures, excessive server round-trips, and poor indexing strategies. Core topics include indexing, execution plans, data access optimization, and efficient query structures. With practical examples, you'll see how to apply best practices in different scenarios and avoid performance traps. You'll gain hands-on experience with tools for measuring performance, identifying bottlenecks, optimizing resources, and improving the user experience, along with skills to analyze execution plans and diagnose costly queries.By the end of this course, you'll be equipped to identify and resolve performance issues with effective troubleshooting and tuning tools. You'll not only enhance database speed but also minimize resource consumption, reducing costs and improving application responsiveness.Whether you're a tester aiming to spot performance problems or a developer eager to fine-tune database interactions, this course will provide you with actionable insights and hands-on skills you can immediately apply to real projects.

Overview

Section 1: Introduction

Lecture 1 The Problem

Lecture 2 Introduction to the Presenter

Lecture 3 Who Should Take This Course

Lecture 4 Course Overview

Section 2: Performance Metrics

Lecture 5 Introduction to Performance Metrics

Lecture 6 Why Measure Performance?

Lecture 7 Examples of Performance Issues

Section 3: Identifying Areas for Improvement

Lecture 8 Identifying Areas for Improvement

Lecture 9 Stored Procedures

Lecture 10 Indexes

Lecture 11 Execution Plans

Lecture 12 Application Code

Lecture 13 ORM

Lecture 14 Optimizing for Concurrency

Lecture 15 Data Access Patterns

Section 4: Measuring Performance and Isolating Bottlenecks

Lecture 16 Clearing Up Misunderstandings

Lecture 17 The Actual Command Timeout

Lecture 18 Differentiate Between DB Engine and Client

Lecture 19 Render Time vs. Execution Time

Section 5: Tools and Techniques for Measuring Performance and Isolating Bottlenecks

Lecture 20 Tools and Techniques

Lecture 21 Client Statistics

Lecture 22 SQL Profiler

Lecture 23 Stop Watch

Lecture 24 Set Statistics

Lecture 25 Execution Plan Analysis

Lecture 26 Monitoring Tools

Lecture 27 WhoIsActive and SQL Server Trace

Lecture 28 Important Notes

Section 6: Advanced Tuning Techniques

Lecture 29 Advanced Tuning Techniques

Lecture 30 Unveiled Mystery

Section 7: Addressing Common Performance Issues

Lecture 31 Addressing Common Performance Issues

Lecture 32 Improving Indexing Strategies

Lecture 33 Analyzing Execution Plans

Lecture 34 Parameter Sniffing

Lecture 35 Temporary Tables

Lecture 36 Common Table Expression (CTE)

Lecture 37 Hidden Gem

Section 8: Avoiding Common Query Design Pitfalls

Lecture 38 Avoiding Common Query Design Pitfalls

Lecture 39 UNION and UNION ALL: Choosing Wisely

Lecture 40 User-Defined Functions: When to Use and Avoid

Lecture 41 Efficient Wildcard Searches

Lecture 42 SELECT * Queries: Why They're Costly

Lecture 43 Managing MAX Data Types Effectively

Lecture 44 Using OR in INNER JOIN Conditions

Lecture 45 Avoiding Cursors for Row-Based Operations

Lecture 46 Understanding Locking and Isolation Levels

Section 9: Optimization of Application Code (Case Study)

Lecture 47 Case Overview

Lecture 48 Before Start

Lecture 49 Initial Approach

Lecture 50 Find the Culprit

Lecture 51 The Magic

Lecture 52 Key Takeaways from this Case Study

Section 10: Performance Tuning is an Art

Lecture 53 Performance Tuning is an Art

Lecture 54 Remember

Lecture 55 Thank You

Testers, Test Analysts, Test Engineers, Test Consultants, QA Analysts, QA Leads, QA Managers, Automation Test Engineers, Performance Testers, Load Testers, Manual Testers, Functional Testers, Non-functional Testers, Regression Testers, Integration Testers, System Testers, Software Quality Engineers, Software Quality Assurance (SQA) Analysts, and Validation Engineers.,Software Developers, Web Developers, Software Engineers, Senior Software Developers, Backend Developers, Full Stack Developers, Systems Software Engineers, Software Architects, Solutions Architects, Cloud Developers, Data Scientists, Data Engineers, Application Developers, API Developers, and Desktop Application Developers.,Database Developers, SQL Developers, ETL (Extract, Transform, Load) Developers, Data Warehouse Developers, BI (Business Intelligence) Developers, Data Integration Developers, Data Migration Specialists, Stored Procedure Developers, T-SQL Developers (SQL Server), Database Programmers, OLAP (Online Analytical Processing) Developers, Report Developers, Data Solutions Architects, Database Designers, Data Architects, Data Warehouse Architects, Database Solutions Architects, Enterprise Data Architects, Schema Designers, Database Schema Analysts, Physical Database Designers, Database Administrators (DBAs), Senior Database Administrators, Database Performance Analysts, Database Optimization Specialists, SQL Server Administrators, Cloud Database Administrators, Database Maintenance Engineers, Data Integrity Analysts, and Database Monitoring Specialists.,DevOps Engineers, CI/CD Engineers, System Administrators, Support Engineers, Technical Support Engineers, Application Support Analysts, and Customer Success Engineers.,This course is ideal for testers, QA professionals, and developers who work with SQL Server and want to enhance their skills in performance testing and optimization.,This course designed for those who need to identify and troubleshoot SQL performance issues, validate database performance under various conditions, and optimize queries for faster data retrieval.,Testers looking to assess SQL-based applications' scalability.,Developers aiming to improve SQL query efficiency.,QA professional responsible for validating application responsiveness.