TRAINING SQL SERVER PERFORMANCE TUNING AND OPTIMIZING SQL DATABASES

 

Description    :    You will get a conceptual understanding of various SQL Server database performance issues and learn about different places to check for the performance information along with tuning approach.

Broadly this Purnama Academy training  topic will cover following:
Understanding SQL Server waits and how to resolve it.
Tempdb contention issues.
Learn about database statistics, optimizer and maintenance plan for tables and indexes.
SQL Tuning techniques.
Partition in SQL Server.
General guidelines for avoiding performance issues in SQL database.
       
       
Goals & Values        This training  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.
 

Participants    :    •    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. Pre-requisite for this course is basic understanding of SQL Server and T-SQL.
•   
       
Prerequisites    :    Basic understanding of SQL Server and some experience in writing T-SQL
       
Details    :    SQL Server Performance Tuning Overview
SQL Server Waits Lesson
•    How SQL Server executes query
This lecture provide details about following topics
. How SQL executes query.
. Query life cycle.
Pratinjau
•    Waits -
This lecture provide details about following topics
1.    Understanding waits in SQL.
2.    Details on wait types.
3.    CX packet waits.
4.    async_network_io_wait

•    Waits -
This lecture provide details about following topics
1.    Pageiolatch_ex wait details and tips to reduce this wait.
2.    sos_scheduler_yield wait details.
3.    Theadpool

•    Waits-Lesson Exercise
This Lesson Exercise section will provide details on how to get wait details on SQL server.
1.    Check blocking session and wait details.
2.    Query to check if server is under CPU pressure.
3.    Query to check waits details on server.
4.    IO wait details query.

•    SQL Server performance counters
This lecture provides information about SQL server performance counters and description of some important counters related to performance tuning.

Indexes
•    Index Types
This lecture covers following topics
1.    Index structure.
2.    Index types
3.    difference between clustered vs non-clustered indexes.

•    Index Lesson Exercise -
This Lesson Exercise provides information on how to create clustered and non-clustered indexes in sql server and which system tables to refer to get information about indexes in database.

•    Un-used and missing indexes
This lecture covers following topic
1.    Column order
2.    missing index information in sql
3.    un-used index information

•    Index Lesson Exercise -
This Lesson Exercise will provide information on affect of multiple indexes on query. It also Lesson Exercisenstrate the affect of column order in index and in SQL query. Finally we will look, how we can retrieve missing index and un-used index information in SQL server.


•    Index Maintenance
This lecture provides information about following topics
1.    Fragmentation in SQL server
2.    Caused of fragmentation and page split
3.    Index maintenance plans
4.    Index rebuild vs Index reorganize options.

•    Index Lesson Exercise -
This Lesson Exercise list the scripts for the index maintenance we usually perform on sql server databases.

Tempdb
•    Tempdb Contents
This lecture provide information on
1.    Tempdb content.
2.    Tempdb user objects
3.    Tempdb size.

•    Tempdb Content Lesson Exercise
This Lesson Exercise list the scripts which help us see content of tempdb.

•    Tempdb Best practices
This lecture provide details on best practices for the tempdb files.

•    Tempdb Contention
This lecture provides information on following topics
1.    Space Allocation in SQL
2.    Latch contention
3.    How to avoid latch contention

•    Tempdb Lesson Exercise
Lesson Exercise to check tempdb speed and temdb file usuage.

Diagnostic Tools
•    Statistics IO and Statistics Time

•    Diagnostic Tools - Lesson Exercise

•    SQL Server Profiler

•    Activity Monitor Lesson Exercise

SQL Databse Query Tuning
•    Introduction
This lecture provides basics of query tuning and which queries to tune.

•    Optimizer Hints
This lecture provides information on Optimizer hints and general sql optimization techniques.

•    Parameter Sniffing
This lecture provides basics of parameter sniffing and details on how to resolve it.

•    Parameter Sniffing Lesson Exercise
Scripts in this Lesson Exercise Lesson Exercisenstrates parameter sniffing scenarios and resolution techniques.

•    Server Configuration
This lecture explains what happens when we change some server level parameters like  Cost threshold for parallelism, Max degree of parallelism

Partition in SQL Server
•    Partition
This lecture provides information on what is partition and how it is useful for easy maintenance of tables and in performance tuning.

Lesson Exercise Scripts
•    Waits Lesson Exercise

•    Index Lesson Exercise Scripts

•    Tempdb Lesson Exercise Scripts

•    SQL Tuning Lesson Exercise Scripts

•    Diagnostic Tools Lesson Exercise scripts

     
 


       
 

Categories: SQL Server performance tuning, training