|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/an-introduction-to-wait-statistics-in-sql-server-2019/
课程评论：没有评论
课程名称：SQL Server 2019 等待统计入门 课程概述：欢迎参加《SQL Server 2019 等待统计入门》课程。本课程旨在通过实践的方式介绍如何在 SQL Server 中使用等待统计信息，具体使用 SQL Server 2019 版本。SQL Server 的内部代码执行机制采用线程，每个线程可以执行 SQL Server 代码，并且在查询并行运行时，多个线程会协同工作。线程的创建与 SQL Server 启动时可用的处理器核心数量有关。查询开始时，线程被分配到调度器上，每个处理器核心对应一个调度器，线程在查询完成之前不会离开该调度器。 调度器的基本组成包括三个部分：处理器（当前有一个线程正在执行代码）、等待列表（所有等待特定资源的线程）和可运行队列（所有可以执行但等待获取处理器的线程）。线程会在这三种状态之间转换，直到查询完成。 课程重点是调度过程中，线程因等待资源而停滞的情况。例如，线程在尝试读取不在内存中的页面时，会发出异步物理 I/O 请求并等候，直至 I/O 完成；当线程需要获取某行的共享锁以读取该行时，如果另一个线程已经持有冲突的排他锁，则也会导致等待。 当线程因无法获得所需资源而停止时，SQL Server 会记录下线程等待的原因，这被称为等待类型。例如，当线程等待读取页面以加载到内存中，等待类型为 PAGEIOLATCH_SH；如果等待的是要修改的页面，等待类型为 PAGEIOLATCH_EX；在等待某行的共享锁时，等待类型为 LCK_M_S。此外，SQL Server 还会追踪线程等待的时间，这称为资源等待时间，通常称为等待时间。 感谢您对《SQL Server 2019 等待统计入门》课程的关注，期待在课程中见到您。
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':The processor, which has exactly one thread currently executing code.The waiter list, which has all the threads that are basically stuck, waiting for a particular resource to become available.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. See you in the course.
