SQL Server Performance Tuning

Index maintenance, Query Tuning, dealing with TempDb issues and many more.
4.2 (14 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.
95 students enrolled
Instructed by Amit Arora IT & Software / Other
$19
$40
52% off
Take This Course
  • Lectures 34
  • Length 3.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 1/2016 English

Course 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.

What are the requirements?

  • Basic understanding of SQL Server and some experience in writing T-SQL.

What am I going to get from this course?

  • 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.

What 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Course Layout
Introduction
Preview
03:38
Section 2: SQL Server Waits
07:48

This lecture provide details about following topics:

1. How SQL executes query.

2. Query life cycle.

12:26

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

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
04:25

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.
04:01

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

Section 3: Indexes
11:40

This lecture covers following topics:

  1. Index structure.
  2. Index types
  3. difference between clustered vs non-clustered indexes.
04:54

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.

05:26

This lecture covers following topic:

  1. Column order
  2. missing index information in sql
  3. un-used index information
03:47

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(b)
02:51
Index Demo - 2(c)
03:07
17:51

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.
02:41

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

Section 4: Tempdb
13:02

This lecture provide information on:

  1. Tempdb content.
  2. Tempdb user objects
  3. Tempdb size.
04:17

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

04:58

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

11:24

This lecture provides information on following topics:

  1. Space Allocation in SQL
  2. Latch contention
  3. How to avoid latch contention
03:02

Demo to check tempdb speed and temdb file usuage.

Section 5: Diagnostic Tools
07:40

This lecture covers following topics:

  1. Statistics IO
  2. Statistics Time
03:01

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

07:18

This lecture provides information on SQL profiler and activity monitor.

02:41

Hands on with SQL activity monitor.

Section 6: SQL Query Tuning
02:40

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

10:04

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

04:38

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

04:34

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

08:10

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

Cost threshold for parallelism

Max degree of parallelism

Section 7: Partition in SQL Server
16:53

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

Section 8: Demo Scripts
Waits Demo
Article
Index Demo Scripts
Article
Tempdb Demo Scripts
Article
SQL Tuning Demo Scripts
Article
Diagnostic Tools Demo scripts
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Amit Arora, 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.  

Ready to start learning?
Take This Course