SQL Server Performance Tuning Training (Online)
By Neeraj, Microsoft Certified DBA!!! (*****)
Course Description
Our SQL Server Performance Tuning and Optimization course will give you a practical optimization technique to put into production. We ensure you understand WHY and HOW a technique works rather than just what the technique is all about.
As well as optimization techniques, this course will also help with design and architecture so you can prevent performance and scalability problems from happening. All the modules will be addressed on multiple fronts: how SQL Server works, implementation considerations and techniques, common problems, troubleshooting, and resolutions.
This course is essential for all SQL Server Developers, DBAs, Data Engineers, and Architects.
For course details and registration, please get in touch with Daniel at +1 267 718 1533 (Mobile & Whatsapp). We are based in Philadelphia, USA, and host affordable and comprehensive SQL Server/Azure/AWS/DevOps training programs for students around the globe.
Student Demographic
Course Introduction Video
Course Information
Neeraj's Profile
Mr. Neeraj has 10+ years of Experience in SQL Server, with experience in Administration, Development, Data Modeling, Performance Tuning, Trouble Shooting, Auditing, Security, Reporting, Azure SQL, SSIS, SSAS, SSRS, PowerShell, etc.
He is a Microsoft Certified DBA /Developer and worked in the United States and India in high transactional Production Environments covering SQL Server 2022/2019/17/16/14/12/08 R2/08/05.
Neeraj has conducted corporate SQL Server training for clients like - IBM, Infosys, HCL, Cognizant, Wipro, Cap Gemini, Accenture, KRONOS, Bank of America, Black Rock, Standard Charted Bank, NOKIA, Siemens, Samsung, RAMCO, All Scripts, L&T, ATOS, LOWES, Global Education, Munich RE, NTT Data, Zimiga, Genpact, Airport Authority Muscat, Zenith Bank Nigeria, Fujitsu, United Health Group, Oracle, Variant, Pelotas, Digital Harbor, First America etc.
Talk to Neeraj
Course Content
Module 1: Database Structures
- Records, record structure, and optimizations
- Pages and page structure
- Allocation bitmaps
- IAM chains and allocation units
Module 2: Data File Internals and Maintenance
- Physical layout considerations
- Allocation algorithms and optimizations
- Instant initialization
- Growth, shrink, and their problems
- Data compression
- Tempdb configuration and performance
Module 3: Transactions and Locking
- The anatomy of a data modification
- Locking and blocking
- Granularity
- Escalation
- Duration
- Troubleshooting locking behavior
- Blocking situations
- Deadlock situations
Module 4: Row Versioning and Isolation
- Understanding isolation levels
- Isolation in SQL Server
- Controlling isolation levels
- Statement-level read consistency
- Transaction-level read consistency
- Overhead/monitoring
Module 5: Optimizing Logging and Recovery
- Transaction log architecture
- Log records
- Checkpoints and recovery
- Transaction log operations
- Recovery models
- Log file provisioning and maintenance
Module 6: Index Internals and Data Access
- Index concepts
- Table structure
- Index internals
- Heaps vs. clustered indexes
- Table usage
- Clustering key choice and performance implications
- Data access patterns
- Understanding selectivity
- Understanding the ‘tipping point’
Module 7: Index Fragmentation
- Data access methods
- Fragmentation and its effect on performance
- How does fragmentation happen?
- Optimizing indexes to remove and prevent fragmentation
Module 8: Internals and Covering
- What methods exist for covering?
- Nonclustered indexes
- Using indexed views
- Using INCLUDE
- Using filtered indexes
- Using filtered statistics
- Practical index consolidation
Module 9: Statistics: Internals and Updates
- How the Optimizer uses statistics
- Statistics from A-to-Z
- What they look like
- What they are telling us
- How to see them
- When/how they get created
- When/how they get updated
Module 10: Cardinality Estimation Issues
- Problems/solutions with statistics
- Steps in histogram
- Filtered statistics
- Uneven distribution
- SQL Server 2014 cardinality estimation changes
Module 11: Indexing Strategies
- Indexing for performance
- Indexing for AND (highly-selective queries, index intersection, covering
- Indexing for OR (tuning, re-writing)
- Indexing for joins (join types, which strategies use what types of indexes, 3 phases of tuning)
- Indexing for aggregates (hash aggregates, stream aggregates, indexed views)
Module 12: SQL Server I/O
- Data and log reading and writing
- Tempdb contention and space usage
- Basic monitoring
Module 13: I/O Concepts for DBAs
- Defining storage terminology
- IOPS, MB/sec, Latency
- Magnetic vs. Solid-State Drives (SSDs)
- RAID configurations
- Mount points
- Partition alignment problems
- Testing with SQLIO and IOMeter
- Direct-attached storage
- Traditional vs. PCI-Express drives
Module 14: I/O – Storage Area Networks for DBAs
- SAN components
- Multipathing
- iSCSI vs. fiber channel (FC)
- Shared vs. dedicated arrays
- Cache (on the drives, SAN controller, RAID controller, and Windows)
- Clustering
- Appendix: snapshot backups (SAN, not SQL)
Module 15: SQLOS Scheduling and CPU Performance Tuning
- Understanding Windows scheduling
- Server hardware and NUMA
- CPU scheduling under SQLOS
- DMV monitoring
- Troubleshooting CPU performance issues
- Using Resource Governor to limit CPU usage
Module 16: Extended Events
- Profiler vs. Extended Events
- Extended Events core concepts
- Event session basics
- Event execution lifecycle
- Event targets
- Extended Events UI
Module 17: Wait and Latch Statistics
- Thread lifecycle
- Waits, latches, spinlocks
- DMVs
- Common wait types
Module 18: Query Plan Analysis
- Why look at query plans?
- Capturing and analyzing plans
- Essential information in a query plan
- Understanding common operators
- Identifying typical plan patterns and steps for further investigation
Module 19: Changes in Query Performance
- Using Distributed Replay to examine hardware, software, and workload changes
- Using the Query Store to examine workload and query plan changes
- Using plan guides and Query Store to force specific query plans
- Using In-Memory OLTP to change query performance
Module 20: Statement Execution, Stored Procedures, and the Plan Cache
- Understanding/analyzing plan cache
- What’s in the plan cache?
- Understanding/optimizing stored procedures
- A cautionary tale about scalar functions
Module 21: Index Analysis
- Index cleanup
- Index health
- Missing indexes
Module 22: SQLOS Memory Management and Memory Performance Tuning
- SQLOS Memory Manager
- SQLOS memory components
- Configuration options for SQL Server
- How to identify memory issues
- Internal memory pressure
- External memory pressure
- Virtual address space issues
- Resource semaphores
- DBCC commands
Module 23: Deadlock Analysis
- Review of locking in SQL Server
- What is a deadlock
- Collecting deadlock graphs
- Anatomy of a deadlock
- Reading deadlock graphs
- Resolving deadlocks
Module 24: Performance Issue Patterns
- Benchmark vs. baseline
- Data collection methods and tools
- Performance Monitor and Collector Sets
- PAL tool (Performance Analysis of Logs)
- SQL Trace
- Analyzing Trace data
- SQLDiag
- SQLNexus
Course Statistics
16
Years of Experience
3671
Gratified Students
112
Training Batches
9634
Training Hours