Skip to main content

🗓

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



     
 


       
 

Comments

Popular posts from this blog

TRAINING MYSQL DATABASE ADMINISTRATOR (DBA)

MYSQL DATABASE ADMINISTRATOR (DBA) www.purnamaacademy.com , Hotline (Call/SMS/WA) :  0838-0838-0001 Syllabus Overview Melalui training ini peserta mampu memahami tugas tugas seorang Database Administrator dalam mengelola Database Server MySQL terkait storage management, user management, keamanan , performance dan juga cara pemantauan kinerja server database Class Type : Training Duration : 3 Days (09.00 – 17.00) Venue / Price : Click Here , Registration : Click Here   (Save up to 20% for Early Bird Registration !) Description : MySQL adalah sebuah perangkat lunak sistem manajemen basis data SQL (bahasa Inggris: database management system) atau DBMS yang multithread, multi-user, dengan sekitar 6 juta instalasi di seluruh dunia. MySQL AB membuat MySQL tersedia sebagai perangkat lunak gratis dibawah lisensi GNU General Public License (GPL), tetapi mereka juga menjual dibawah lisensi komersial untuk kasus-kasus di mana penggunaannya tidak cocok denga...

TRAINING ISO 45005 : 2020 K3 COVID - Pedoman umum untuk bekerja dengan aman selama pandemi COVID-19

SILABUS ISO 45005 , Training ISO 45005 di Purnama Academy Bandung, Jakarta, (Online, Offline, Inhouse Training) hubungi tim sales call/Whatsapp 0838-0838-0001 ISO/PAS 45005:2020 Manajemen kesehatan dan keselamatan kerja Pedoman umum untuk bekerja dengan aman selama pandemi COVID-19 Durasi : 09.00 – 16.00 WIB ( 3  Hari ) Deskripsi : ISO (the International Organization for Standardization) adalah sebuah organisasi yang tersusun atas federasi dari berbagai komite yang memiliki kepentingan untuk mencipatakan  berbagai standarisasi internasional di setiap bidang yang di wakili teknisnya oleh komite, setiap komite bisa terdiri atas organsasi , pemerintahan ataupun non organisasi yang berkompetensi Dokumen ISO 45005 dipublikasikan sebagai respon  terhadap pandemi COVID-19 dan peningkatan risiko penyakit ini terhadap kesehatan, keselamatan, dan kesejahteraan orang . Didalam dokumen mencakup  semua pengaturan terhadap orang, termasuk mereka yang bekerja di rumah atau di lingkungan bergerak (...

CISA Exam Preparation Training

  CISA Exam Preparation Training | Purnama Academy Prepare for the CISA Certification with Confidence The demand for qualified IT Auditors and IT Governance professionals continues to increase as organizations rely more on technology to run critical business processes. One of the most recognized credentials in this field is the CISA (Certified Information Systems Auditor) certification. Purnama Academy offers a comprehensive CISA Exam Preparation Training designed to help professionals understand the exam structure, master key domains, and improve their chances of passing the CISA examination on the first attempt. What Is CISA (Certified Information Systems Auditor)? CISA is a globally recognized professional certification issued by ISACA . It validates an individual’s knowledge and skills in auditing, controlling, monitoring, and assessing enterprise information systems. CISA certification focuses on ensuring that information systems are: Properly ...