SQL Server Performance Tuning
4.1 (23 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
171 students enrolled
Wishlisted Wishlist

Please confirm that you want to add SQL Server Performance Tuning to your Wishlist.

Add to Wishlist

SQL Server Performance Tuning

Index maintenance, Query Tuning, dealing with TempDb issues and many more.
4.1 (23 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
171 students enrolled
Created by Amit Arora
Last updated 1/2016
English
Current price: $10 Original price: $40 Discount: 75% off
4 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3.5 hours on-demand video
  • 5 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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.
View Curriculum
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 is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
34 Lectures
03:33:59
+
Course Layout
1 Lecture 03:38
+
SQL Server Waits
5 Lectures 37:58

This lecture provide details about following topics:

1. How SQL executes query.

2. Query life cycle.

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 12:37
Waits Demo
02:35

Index Demo Scripts
03:44

Tempdb Demo Scripts
02:34

SQL Tuning Demo Scripts
01:56

Diagnostic Tools Demo scripts
01:47
About the Instructor
Amit Arora
4.0 Average rating
105 Reviews
930 Students
4 Courses
Data Integration Specialist

I have over 16 years of experience with databases (primarily with Oracle and SQL Server and NoSQL databases). In this period, I worked in every phase of Software development life cycle including database developer and database administrator, data modelling, etc.

I am an Oracle certified DBA and handing Oracle / SQL Server projects in my jobs. I am regular blogger for various sites and written Oracle/ SQL related articles along with certification tests for Oracle, PMP and more.

I completed MongoDB courses with full score from MongoDB university and practically implimented the solution for various applications.