Tutorialspoint

Mid-Year Savings Flat 10% OFF, Use Code: MID10

An Introduction to Wait Statistics in SQL Server

person icon Mike West

3.9

An Introduction to Wait Statistics in SQL Server

A Hands-On Guide to Using Wait Statistics in the Applied Space

updated on icon Updated on Jul, 2024

language icon Language - English

person icon Mike West

English [CC]

category icon Development,SQL Server

Lectures -23

Resources -3

Duration -1 hours

3.9

price-loader

30-days Money-Back Guarantee

Training 5 or more people ?

Get your team access to 10000+ top Tutorials Point courses anytime, anywhere.

Course Description

Welcome to An Introduction to Wait Statistics in SQL Server 2019

This course is a brief, hands-on approach to using wait statistics in SQL Server. The course will use SQL Server 2019.

The execution of SQL Server’s internal code is done using a mechanism called threads. Each thread can be executing SQL Server code, and multiple threads coordinate together when a query runs in parallel. These threads are created when SQL Server starts, depending on the number of processor cores available for SQL Server to use.

Threads are placed on a scheduler when a query starts, with one scheduler per processor core, and don’t move off that scheduler until the query has finished. A scheduler has three basic ‘parts’:

  1. The processor, which has exactly one thread currently executing code.

  2. The waiter list, which has all the threads that are basically stuck, waiting for a particular resource to become available.

  3. The runnable queue, which has all the threads that are able to execute but are waiting to get on the processor.

Threads transition from state 1 to 2 to 3 to 1, around and around until the query has finished.

From our perspective, the most interesting part of scheduling is when a thread has to wait for a resource before it can continue. Some examples of this are: A thread needs to read a page, and the page isn’t in memory, so the thread issues an asynchronous physical I/O and then has to wait, off the processor, until the I/O completes. A thread needs to acquire a share lock on a row to read it, but another thread already holds a conflicting exclusive lock while it is updating the row.

When a thread encounters the need for a resource that it cannot get, it has no choice but to stop and wait for the resource to become available (the mechanism for how the thread is notified about resource availability is beyond the scope of this article). When that happens, SQL Server makes a note of why the thread had to wait and this is called the wait type. Some examples of this are: When a thread is waiting for a page to be read into memory so it can be read, the wait type is PAGEIOLATCH_SH (if the thread is waiting for a page that it will change, the wait type is PAGEIOLATCH_EX). When a thread is wait for a share lock on a row, the wait type is LCK_M_S (lock-mode-share)

SQL Server also keeps track of how long the thread has to wait. This is called the resource wait time, and is usually just known as the wait time.

Thanks for your interest in An Introduction to Wait Statistics in SQL Server 2019.

Goals

  • How to Install SQL Server 2019 and SQL Server Management Studio
  • How to use wait statistics to troubleshoot real-world problems.
  • How to incorporate wait statistics into sp_whoisactive to see what each transaction is waiting on.
  • How to fix issues you find using wait statistics.

Prerequisites

  • A foundation in SQL Server is highly recommended.
  • This is not an entry level SQL Server Course.
An Introduction to Wait Statistics in SQL Server

Curriculum

Check out the detailed breakdown of what’s inside the course

Introduction
11 Lectures
  • play icon Course Introduction 01:06 01:06
  • play icon Is this Course Right for You? 01:27 01:27
  • play icon What is a Wait Statistic? 01:13 01:13
  • play icon Install SQL Server 2019 and SSMS 05:08 05:08
  • play icon The Wait Statistic DMVs 03:05 03:05
  • play icon Download COURSE CONTENT HERE
  • play icon Add and Upgrade the AdvertureWorks Database 06:05 06:05
  • play icon Download and Run sp_whoisactive 01:34 01:34
  • play icon Crash Course on whoisactive 02:38 02:38
  • play icon Install SQLQueryStress 05:36 05:36
  • play icon The Top Wait Statistics 03:26 03:26
Hands-On Wait Statistics
11 Lectures
Tutorialspoint

Instructor Details

Mike West

Mike West

I'm the founder of LogikBot. I've worked at Microsoft and Uber. I helped design courses for Microsoft's Data Science Certifications. If you're interested in machine learning, I can help.

I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman. 

Over the last five years I've transitioned to the exciting world of applied machine learning.  I'm excited to show you what I've learned and help you move into one of the single most important fields in this space.

Experience, education and passion 

I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car. 

Certifications  

Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT). 

Personal  

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.

Course Certificate

Use your certificate to make a career change or to advance in your current career.

sample Tutorialspoint certificate

Our students work
with the Best

Related Video Courses

View More

Annual Membership

Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses

Subscribe now
Annual Membership

Online Certifications

Master prominent technologies at full length and become a valued certified professional.

Explore Now
Online Certifications

Talk to us

1800-202-0515