Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database. 3 Ed

Table of Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

 About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

 About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16

 Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17

 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18

 Part I: Foundations of Wait Statistics Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20

    Chapter 1: Wait Statistics Internals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

        A Brief History of Wait Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

        The SQLOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

        Schedulers, Tasks, and Worker Threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27

            Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28

            Requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30

            Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30

            Worker Threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

            Schedulers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35

            Putting It All Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37

            Wait Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38

            Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43

    Chapter 2: Querying SQL Server Wait Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . .44

        sys.dm_os_wait_stats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

        sys.dm_os_waiting_tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48

            Understanding sys.dm_os_waiting_tasks . . . . . . . . . . . . . . . . . . . . . . . .48

            Querying sys.dm_os_waiting_tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50

        sys.dm_exec_requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52

            Understanding sys.dm_exec_requests . . . . . . . . . . . . . . . . . . . . . . . . . . .52

            Querying sys.dm_exec_requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53

        sys.dm_exec_session_wait_stats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55

        Combining DMVs to Detect Waits Happening Now . . . . . . . . . . . . . . . . . . . . . . .57

        Viewing Wait Statistics Using Perfmon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62

        Capturing Wait Statistics Using Extended Events . . . . . . . . . . . . . . . . . . . .64

            Capture Wait Statistics Information for a Specific Query . . . . .66

        Analyzing Wait Statistics on a Per-Query Basis Using Execution Plans .76

        Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .80

    Chapter 3: The Query Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81

        What Is the Query Store? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81

        Enabling the Query Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .82

            Enable the Query Store Using SSMS . . . . . . . . . . . . . . . . . . . . . . . . . . . .82

            Enable the Query Store Using T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . .84

        Query Store Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84

        How Wait Statistics Are Processed in the Query Store . . . . . . . . . . . . . . .85

        Accessing Wait Statistics Through Query Store Reports . . . . . . . . . . . . . .88

        Accessing Wait Statistics Through Query Store DMVs . . . . . . . . . . . . . . . . .91

        Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94

    Chapter 4: Building a Solid Baseline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95

        What Are Baselines? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96

        Visualizing Your Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98

        Baseline Types and Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99

        Baseline Pitfalls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101

            Too Much Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101

            Know Your Metrics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101

            Find the Big Measurement Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . .101

            Use Fixed Intervals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102

        Building a Baseline for Wait Statistics Analysis . . . . . . . . . . . . . . . . . .102

            Reset Capture Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .106

            Delta Capture Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107

        Using SQL Server Agent to Schedule Measurements . . . . . . . . . . . . . . . . . . .109

        Wait Statistics Baseline Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112

        Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .120

 Part II: Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121

    Chapter 5: CPU-Related Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122

        CXPACKET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122

            What Is the CXPACKET Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123

            Lowering CXPACKET Wait Time by Tuning the Parallelism Configuration Options .127

            Lowering CXPACKET Wait Time by Resolving Skewed Workloads . . .131

            Introduction of the CXCONSUMER Wait Type . . . . . . . . . . . . . . . . . . . .132

            CXPACKET Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133

        SOS_SCHEDULER_YIELD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133

            What Is the SOS_SCHEDULER_YIELD Wait Type? . . . . . . . . . . . . . . . . . .134

            Lowering SOS_SCHEDULER_YIELD Waits . . . . . . . . . . . . . . . . . . . . . . . . . .137

            SOS_SCHEDULER_YIELD Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142

        THREADPOOL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142

            What Is the THREADPOOL Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . .143

            THREADPOOL Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145

            Gaining Access to Our SQL Server During THREADPOOL Waits . . . .149

            Lowering THREADPOOL Waits Caused by Parallelism . . . . . . . . . . . . .150

            Lowering THREADPOOL Waits Caused by User Connections . . . . . . . .152

            THREADPOOL Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .155

    Chapter 6: IO-Related Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .156

        ASYNC_IO_COMPLETION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .156

            What Is the ASYNC_IO_COMPLETION Wait Type? . . . . . . . . . . . . . . . . . .157

            ASYNC_IO_COMPLETION Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158

            Lowering ASYNC_IO_COMPLETION Waits . . . . . . . . . . . . . . . . . . . . . . . . . .159

            ASYNC_IO_COMPLETION Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164

        ASYNC_NETWORK_IO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .165

            What Is the ASYNC_NETWORK_IO Wait Type? . . . . . . . . . . . . . . . . . . . . .165

            ASYNC_NETWORK_IO Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .166

            Lowering ASYNC_NETWORK_IO Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . .167

            ASYNC_NETWORK_IO Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .168

        CMEMTHREAD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .169

            What Is the CMEMTHREAD Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . .169

            Lowering CMEMTHREAD Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .171

            CMEMTHREAD Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .172

        IO_COMPLETION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .172

            What Is the IO_COMPLETION Wait Type? . . . . . . . . . . . . . . . . . . . . . . . .172

            IO_COMPLETION Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173

            Lowering IO_COMPLETION Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174

            IO_COMPLETION Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174

        LOGBUFFER and WRITELOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175

            What Are the LOGBUFFER and WRITELOG Wait Types? . . . . . . . . . . . . .175

            LOGBUFFER and WRITELOG Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .177

            Lowering LOGBUFFER and WRITELOG Waits . . . . . . . . . . . . . . . . . . . . . . .179

            LOGBUFFER and WRITELOG Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180

        RESOURCE_SEMAPHORE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180

            What Is the RESOURCE_SEMAPHORE Wait Type? . . . . . . . . . . . . . . . . . . .180

            RESOURCE_SEMAPHORE Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .182

            Lowering RESOURCE_SEMAPHORE Waits . . . . . . . . . . . . . . . . . . . . . . . . . . .187

            RESOURCE_SEMAPHORE Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187

        RESOURCE_SEMAPHORE_QUERY_COMPILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188

            What Is the RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Type? . . . . .188

            RESOURCE_SEMAPHORE_QUERY_COMPILE Example . . . . . . . . . . . . . . . . . . . .192

            Lowering RESOURCE_SEMAPHORE_QUERY_COMPILE Waits . . . . . . . . . . . . .195

            RESOURCE_SEMAPHORE_QUERY_COMPILE Summary . . . . . . . . . . . . . . . . . . . .196

        SLEEP_BPOOL_FLUSH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .196

            What Is the SLEEP_BPOOL_FLUSH Wait Type? . . . . . . . . . . . . . . . . . . . .196

            SLEEP_BPOOL_FLUSH Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199

            Lowering SLEEP_BPOOL_FLUSH Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . .201

            SLEEP_BPOOL_FLUSH Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .202

        WRITE_COMPLETION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .202

            What Is the WRITE_COMPLETION Wait Type? . . . . . . . . . . . . . . . . . . . . .202

            WRITE_COMPLETION Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203

            Lowering WRITE_COMPLETION Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203

            WRITE_COMPLETION Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204

    Chapter 7: Backup-Related Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205

        BACKUPBUFFER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .206

            What Is the BACKUPBUFFER Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . .206

            BACKUPBUFFER Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209

            Lowering BACKUPBUFFER Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209

            BACKUPBUFFER Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .211

        BACKUPIO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .211

            What Is the BACKUPIO Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . .211

            BACKUPIO Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .212

            Lowering BACKUPIO Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .213

            BACKUPIO Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .214

        BACKUPTHREAD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .214

            What Is the BACKUPTHREAD Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . .214

            BACKUPTHREAD Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215

            Lowering BACKUPTHREAD Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .216

            BACKUPTHREAD Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217

    Chapter 8: Lock-Related Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218

        Introduction to Locking and Blocking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .220

            Lock Modes and Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .220

            Locking Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222

            Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224

            Querying Lock Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .227

        LCK_M_S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230

            What Is the LCK_M_S Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231

            LCK_M_S Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231

            Lowering LCK_M_S Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233

            LCK_M_S Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233

        LCK_M_U . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .234

            What Is the LCK_M_U Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .234

            LCK_M_U Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236

            Lowering LCK_M_U Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237

            LCK_M_U Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237

        LCK_M_X . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238

            What Is the LCK_M_X Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238

            LCK_M_X Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238

            Lowering LCK_M_X Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240

            LCK_M_X Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240

        LCK_M_I[xx] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240

            What Is the LCK_M_I[xx] Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . .240

            LCK_M_I[xx] Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241

            Lowering LCK_M_I[xx] Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243

            LCK_M_I[xx] Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243

        LCK_M_SCH_S and LCK_M_SCH_M . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244

            What Are the LCK_M_SCH_S and LCK_M_SCH_M Wait Types? . . . . . . . .244

            LCK_M_SCH_S and LCK_M_SCH_M Example . . . . . . . . . . . . . . . . . . . . . . . . .245

            Lowering LCK_M_SCH_S and LCK_M_SCH_M Waits . . . . . . . . . . . . . . . . . .247

            LCK_M_SCH_S and LCK_M_SCH_M Summary . . . . . . . . . . . . . . . . . . . . . . . . .247

    Chapter 9: Latch-Related Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248

        Introduction to Latches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248

            Latch Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .250

            Latch Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251

            sys.dm_os_latch_stats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .253

            Page-Latch Contention . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .254

        PAGELATCH_[xx] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .257

            What Is the PAGELATCH_[xx] Wait Type? . . . . . . . . . . . . . . . . . . . . . . .257

            PAGELATCH_[xx] Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .258

            Lowering PAGELATCH_[xx] Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .263

            PAGELATCH_[xx] Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .268

        LATCH_[xx] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .269

            What Is the LATCH_[xx] Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . .269

            LATCH_[xx] Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .270

            Lowering LATCH_[xx] Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .275

            LATCH_[xx] Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276

        PAGEIOLATCH_[xx] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276

            What Is the PAGEIOLATCH_[xx] Wait Type? . . . . . . . . . . . . . . . . . . . . .277

            PAGEIOLATCH_[xx] Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279

            Lowering PAGEIOLATCH_[xx] Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281

            PAGEIOLATCH_[xx] Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285

    Chapter 10: High-Availability and Disaster-Recovery Wait Types . . . . . . . . . .286

        DBMIRROR_SEND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .287

            What Is the DBMIRROR_SEND Wait Type? . . . . . . . . . . . . . . . . . . . . . . . .290

            DBMIRROR_SEND Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .290

            Lowering DBMIRROR_SEND Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .292

            DBMIRROR_SEND Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .293

        HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE . . . . . . . . . . . . . . . . . . . . . . . . . .294

            What Are the HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE Wait Types? .294

            HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE Summary . . . . . . . . . . . .297

        HADR_SYNC_COMMIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297

            What Is the HADR_SYNC_COMMIT Wait Type? . . . . . . . . . . . . . . . . . . . . .297

            HADR_SYNC_COMMIT Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .298

            Lowering HADR_SYNC_COMMIT Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . .301

            HADR_SYNC_COMMIT Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .304

        REDO_THREAD_PENDING_WORK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .305

            What Is the REDO_THREAD_PENDING_WORK Wait Type? . . . . . . . . . . . . .305

            REDO_THREAD_PENDING_WORK Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . .307

    Chapter 11: Preemptive Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .308

        SQL Server on Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .309

        PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE . . . . .312

            What Are the PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Wait Types? .312

            PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Example .312

            Lowering PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Waits .320

            PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Summary .320

        PREEMPTIVE_OS_WRITEFILEGATHER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320

            What Is the PREEMPTIVE_OS_WRITEFILEGATHER Wait Type? . . . . . . . .320

            PREEMPTIVE_OS_WRITEFILEGATHER Example . . . . . . . . . . . . . . . . . . . . . . .321

            Lowering PREEMPTIVE_OS_WRITEFILEGATHER Waits . . . . . . . . . . . . . . . .322

            PREEMPTIVE_OS_WRITEFILEGATHER Summary . . . . . . . . . . . . . . . . . . . . . . .323

        PREEMPTIVE_OS_AUTHENTICATIONOPS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .324

            What Is the PREEMPTIVE_OS_AUTHENTICATIONOPS Wait Type? . . . . . .324

            PREEMPTIVE_OS_AUTHENTICATIONOPS Example . . . . . . . . . . . . . . . . . . . . .325

            Lowering PREEMPTIVE_OS_AUTHENTICATIONOPS Waits . . . . . . . . . . . . . .327

            PREEMPTIVE_OS_AUTHENTICATIONOPS Summary . . . . . . . . . . . . . . . . . . . . .329

        PREEMPTIVE_OS_GETPROCADDRESS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .329

            What Is the PREEMPTIVE_OS_GETPROCADDRESS Wait Type? . . . . . . . . .329

            PREEMPTIVE_OS_GETPROCADDRESS Example . . . . . . . . . . . . . . . . . . . . . . . .332

            Lowering PREEMPTIVE_OS_GETPROCADDRESS Waits . . . . . . . . . . . . . . . . .333

            PREEMPTIVE_OS_GETPROCADDRESS Summary . . . . . . . . . . . . . . . . . . . . . . . .333

    Chapter 12: Background and Miscellaneous Wait Types . . . . . . . . . . . . . . . . . . . . .334

        CHECKPOINT_QUEUE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .335

            What Is the CHECKPOINT_QUEUE Wait Type? . . . . . . . . . . . . . . . . . . . . .335

            CHECKPOINT_QUEUE Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .338

        DIRTY_PAGE_POLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .339

            What Is the DIRTY_PAGE_POLL Wait Type? . . . . . . . . . . . . . . . . . . . . . .339

            DIRTY_PAGE_POLL Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .342

        LAZYWRITER_SLEEP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .342

            What Is the LAZYWRITER_SLEEP Wait Type? . . . . . . . . . . . . . . . . . . . . .342

            LAZYWRITER_SLEEP Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .344

        MSQL_XP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .344

            What Is the MSQL_XP Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .344

            MSQL_XP Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .345

            Lowering MSQL_XP Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .346

            MSQL_XP Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347

        OLEDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347

            What Is the OLEDB Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347

            OLEDB Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347

            Lowering OLEDB Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .348

            OLEDB Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349

        TRACEWRITE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349

            What Is the TRACEWRITE Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . .350

            TRACEWRITE Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .350

            Lowering TRACEWRITE Waits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .354

            TRACEWRITE Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .357

        WAITFOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .357

            What Is the WAITFOR Wait Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .358

            WAITFOR Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .358

            WAITFOR Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359

    Chapter 13: In-Memory OLTP–Related Wait Types . . . . . . . . . . . . . . . . . . . . . . . . . . .360

        Introduction to In-Memory OLTP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .360

            Checkpoint File Pairs (CFPs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .361

            Isolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .365

            Transaction Log Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .365

        WAIT_XTP_HOST_WAIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .366

            What Is the WAIT_XTP_HOST_WAIT Wait Type? . . . . . . . . . . . . . . . . . . .366

            WAIT_XTP_HOST_WAIT Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .369

        WAIT_XTP_CKPT_CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .370

            What Is the WAIT_XTP_CKPT_CLOSE Wait Type? . . . . . . . . . . . . . . . . . .370

            WAIT_XTP_CKPT_CLOSE Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .371

        WAIT_XTP_OFFLINE_CKPT_NEW_LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .372

            What Is the WAIT_XTP_OFFLINE_CKPT_NEW_LOG Wait Type? . . . . . . . .372

            WAIT_XTP_OFFLINE_CKPT_NEW_LOG Summary . . . . . . . . . . . . . . . . . . . . . . .373

 Appendix I: Example SQL Server Machine Configurations . . . . . . . . . . . . . . . . . . . . . . . . .374

    Default Test Machine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .374

    HA/DR Test Machines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .375

 Appendix II: Spinlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .378

 Appendix III: Latch Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .381

 Appendix IV: Waits and DMVs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .392

 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .401

Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You'll learn to identify precisely why your queries are running slowly. And you'll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.

The book goes beyond the most common wait types into the more complex and performance-threatening wait types. You’ll learn about per-query wait statistics and session-based wait statistics, and the types of problems they can help you solve. The different wait types are categorized by their area of impact, including CPU, IO, Latching, Locking, and many more.

Clear examples are included to help you gain practical knowledge of why and how specific wait times increase or decrease, how they impact your SQL Server’s performance, and what you can do to improve performance. After reading this book, you won’t want to be without the valuable information that wait statistics provide regarding where you should be spending your limited tuning time to maximize performance and value to your business.

What You'll Learn

Understand how the SQL Server engine processes requests Identify resource bottlenecks in a running SQL Server instance Locate wait statistics information inside DMVs and Query Store Analyze the root cause of sub-optimal performance Diagnose I/O contention and locking contention Benchmark SQL Server performance Improve database performance by lowering overall wait time

Who This Book Is For

Database administrators who want to identify and resolve performance bottlenecks, those who want to learn more about how the SQL Server engine accesses and uses resources inside SQL Server, and administrators concerned with achieving―and knowing they have achieved―optimal performance