IT & Software
Other
SQL Server
SQL Server Performance Tuning
3.0 (101 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
446 students enrolled

SQL Server Performance Tuning

Index maintenance, Query Tuning, dealing with TempDb issues and many more.
3.0 (101 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
446 students enrolled
Created by Amit Arora
Last updated 1/2016
English
English [Auto-generated]
Preview this course
SQL Server Performance Tuning
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 3.5 hours on-demand video
  • 5 articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • This tutorial 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.
Requirements
  • Basic understanding of SQL Server and some experience in writing T-SQL.
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 course 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.

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. Pre-requisite for this course is basic understanding of SQL Server and T-SQL.
Course content
Expand all 34 lectures 03:33:57
+ – SQL Server Waits
5 lectures 37:58

This lecture provide details about following topics:

1. How SQL executes query.

2. Query life cycle.

How SQL Server executes query
Preview 07:48

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 - 1
12:26

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 - 2
09:18

This demo 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.
Waits-Demo
04:25

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

SQL Server performance counters
04:01
+ – Indexes
8 lectures 52:17

This lecture covers following topics:

  1. Index structure.
  2. Index types
  3. difference between clustered vs non-clustered indexes.
Index Types
11:40

This demo 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.

Index Demo - 1
04:54

This lecture covers following topic:

  1. Column order
  2. missing index information in sql
  3. un-used index information
Un-used and missing indexes
05:26

This demo will provide information on affect of multiple indexes on query. It also demonstrate 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 Demo - 2(a)
03:47
Index Demo - 2(b)
02:51
Index Demo - 2(c)
03:07

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 Maintenance
17:51

This demo list the scripts for the index maintenance we usually perform on sql server databases.

Index Demo - 3
02:41
+ – Tempdb
5 lectures 36:43

This lecture provide information on:

  1. Tempdb content.
  2. Tempdb user objects
  3. Tempdb size.
Tempdb Contents
13:02

This demo list the scripts which help us see content of tempdb.

Tempdb Content Demo
04:17

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

Tempdb Best practices
04:58

This lecture provides information on following topics:

  1. Space Allocation in SQL
  2. Latch contention
  3. How to avoid latch contention
Tempdb Contention
11:24

Demo to check tempdb speed and temdb file usuage.

Tempdb Demo
03:02
+ – Diagnostic Tools
4 lectures 20:40

This lecture covers following topics:

  1. Statistics IO
  2. Statistics Time
Statistics IO and Statistics Time
07:40

This demo provide details on how to check statistics and time details on any query in sql database.

Diagnostic Tools - Demo
03:01

This lecture provides information on SQL profiler and activity monitor.

SQL Server Profiler
07:18

Hands on with SQL activity monitor.

Activity Monitor Demo
02:41
+ – SQL Query Tuning
5 lectures 30:06

This lecture provides basics of query tuning and which queries to tune.

Introduction
02:40

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

Optimizer Hints
10:04

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

Parameter Sniffing
04:38

Scripts in this demo demonstrates parameter sniffing scenarios and resolution techniques.

Parameter Sniffing Demo
04:34

This lecture explains what happens when we change some server level parameters like :

Cost threshold for parallelism

Max degree of parallelism

Server Configuration
08:10
+ – Partition in SQL Server
1 lecture 16:53

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

Partition
16:53
+ – Demo Scripts
5 lectures 15:43
Waits Demo
04:07
Index Demo Scripts
04:07
Tempdb Demo Scripts
03:34
SQL Tuning Demo Scripts
01:49
Diagnostic Tools Demo scripts
02:05