1.SQL Server Performance Tuning

2.Introduction to SQL Server Performance Tuning:

3.Introduction: Houston, do you have a problem?

• Resource bottlenecks
• Identifying issues
• Prioritizing issues
• Continuous process
• SQL Server Query Basics
• How does a query process?
• Query Optimizer
• Plan Cache
• Execution plans
• Baselines and Statistics
• Value in knowing whats good
• Performance metrics
• SQL Server Statistics (DMOs)
• SQL Server Load Testing
• Scripts & third party tools
• Hands-on lab: Perform SQL Server load testing using PowerShell
• Configuring a SQL Server for Optimal Performance:
• Hardware Environment and Configuration
• Memory
• Storage Platform
• Software Environment and Configuration
• Windows Operating System
• SQL Server Best Practices

4.SQL Server Performance Tuning Methodology:

• Waits and Queues Methodology
• Basics of methodology
• Cashier example
• Value of methodology
• How to monitor Wait and Queues
• Hands-on lab: Querying DMVs to monitor waits and queues
• Index Tuning

5.Index Types:

• Clustered Indexes
• Heaps
• Non-Clustered Indexes
• ColumnStore Indexes
• Index Maintenance
• Missing indexes
• Database Engine Tuning Advisor
• Duplicate Indexes
• Hands-on lab: Perform various index tuning exercises (2 – 3)
• Query Tuning
• Watching selectivity
• Order of operations
• Blocking
• Deadlocks
• Locking
• Consistency
• Complex solutions
• Best Practices
• Hands-on lab: Perform various query tuning exercises (3 – 4)

6.SQL Server Monitoring and Tools:

• Monitoring, Alerting and Tools
• Perfmon
• SQL Trace
• Profiler
• Extended Events
• sp_ServerDiagnostic
• sp_WhoIsActive
• sp_Blitz
• sp_BlitzIndex
• SQLIO
• Alerting
• Hands-on lab: Create an XEvent session to track wait resources
• BI performance monitoring
• Making it real
• Phase I: Taking over a SQL Server environment
• hase II: Identifying your problem children
• Phase III: Achieving an acceptable level of stability & reliability