SQL Server Performance Tuning and Optimization Online Training in Karachi, Lahore, Islamabad, Pakistan, Dubai

SQL Server Performance Tuning and Optimization

In this course, you will learn practical tuning and optimization techniques for SQL Server that include indexing and statistics strategies, transaction log operations,and data file configuration.

Course Key Learnings

ThisCourse will provide comprehensive material of SQL Server performance tuning along with handy tips for issue resolution.
Provide details about SQL waits and resolution.
Learn about database statistics, optimizer and maintenance plan for tables and indexes.
SQL Tuning techniques
General guidelines for avoiding performance issues in SQL database.

Course Outline:

Module 1: Database StructuresThe fundamental building block of knowledge for all SQL Server performance tuning and optimization is how SQL Server stores data on disk and this module covers the structures in a database. Topics covered include:
- Records, record structure, and optimizations
- Pages and page structure
- Allocation bitmaps
- IAM chains and allocation units

Module 2: Data File Internals and Maintenance

There are many things to consider around data file configuration and management, especially for tempdb. Topics covered include:

Physical layout considerations
Allocation algorithms and optimizations
Instant initialization
Growth, shrink, and their problems
Data compression
Tempdb configuration and performance

Module 3: Locking and Blocking

All operations performed by SQL Server are done in the context of transactions and all transactions involve locking to some degree. This module will explain how transactions and locking work, plus explore blocking and deadlocks and the performance and application problems that can result. Topics covered include:

The anatomy of a data modification
Locking and blocking
- Granularity
- Escalation
- Duration
Troubleshooting locking behavior
- Blocking situations
- Deadlock situations

Module 4: Isolation Levels and Versioning

In many systems today, real-time analysis is required – often at the expense of OLTP activity. And, when significant OLTP activity is present, real-time analysis is prone to inconsistencies. After discussing locking and blocking, we move to discussing statement-level and transaction-level inconsistencies, when they can occur, how to minimize/eliminate them using locking (at the expense of concurrency) and then how to use versioning to get the best of both worlds (concurrency and accuracy). But, at what expense? The primary focus of this module is to discuss when using these isolation levels is appropriate, what the possible trade-offs are, and what are the best practices for using versioning. Topics covered include:

Understanding isolation levels
Isolation in SQL Server
Controlling isolation levels
Statement-level read consistency
Transaction-level read consistency
Overhead/monitoring

Module 5: Logging, Recovery, and the Transaction Log

Logging and recovery underpins SQL Server’s ability to recover after a crash and all high-availability and disaster recovery technologies. It can also have a profound effect on the performance of a workload. This module will explain these topics in great depth, including:

Transaction log architecture
Log records
Checkpoints and recovery
Transaction log operations
Recovery models
Log file provisioning and maintenance

Module 6: Index Internals

Taking your design further, we next consider our base table strategies in indexes. This is one of the most misunderstood and improperly handled areas of SQL Server. This module explains the internals of both clustered and nonclustered indexes and is the foundation for the remainder of the class. We’ll also start reviewing data access methods and the limited uses to nonclustered indexes. Topics covered include:

Index concepts
Table structures
Heaps vs. clustered indexes
Clustering key choice
Clustering key columns in nonclustered indexes
Nonclustered index structure

Module 7: Index Fragmentation

Even with the most carefully-designed indexing strategy, fragmentation is unavoidable. This module explains the causes and effects of fragmentation, as well as the trade-offs between the various methods of removing it. Topics covered include:

Data access methods
Fragmentation and its effect on performance
How does fragmentation happen?
Optimizing indexes to remove and prevent fragmentation

Module 8: Internals and Data Access

Understanding the options that SQL Server has for accessing data from within indexes is critical to creating the RIGHT indexes. In this module we continue our internals discussion with primary focus on how some of the special features (INCLUDE and filters) enhance your indexing options. We’ll also dive into the concept of covering and show the benefits of wider indexes. Topics covered include:

Data access patterns
Covering
- Understanding selectivity
- Understanding the “tipping point”
What methods exist for covering?
- Nonclustered indexes (all releases)
- Using indexed views
- Using INCLUDE
- Using filtered indexes
- Using filtered statistics

Module 9: Statistics: Internals and Updates

Now that we know how SQL Server uses indexes and the different options available, how does SQL Server know which index to choose? Topics covered include:

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: Indexing Strategies

What kinds of indexes are best to create? In what order should the columns be and should they be in the key or only in the leaf-level? This module focuses on a variety of real queries and then debates all of the options that should be considered and above all emphasizes consolidation. Topics covered include:

Indexing for performance
- Design strategies
- Overall strategies
Using the tools for tuning
- SET STATISTICS IO ON
- Showplan
- Missing indexes
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)
Indexed views v. columnstore indexes
Rowstore indexes v. columnstore indexes

Module 11: Cardinality Estimation Issues

SQL Server needs to “know” how much data to process in order to process it efficiently. There are many places where this is difficult to do. Knowing the limitations of SQL Server as well as many straightforward and even relatively easy workarounds, can make a huge difference in your queries performance. Topics covered include:

Selectivity and estimates
Query complexity
Estimates from statistics
- Sampling
- The histogram
- Filtered statistics
- Uneven distribution
Overview: Changes to cardinality estimation in SQL Server
Migrations / upgrades / regressions

Requirements

Basic understanding of SQL Server

Who this course is for:

This course is meant for all levels of SQL professionals (developers and DBA’s). It provides roadmap of how to diagnose and resolve performance issues related to SQL Server database

International Student Fee:700 USD