Index

„„ A
        database backup, 137
generating BACKUPBUFFER
ASYNC_IO_COMPLETION Wait Type waits, 138
backup-related waits, 99–100 lowering, 138–139
Books Online (BOL), 98 reader and writer
definition, 98 backup buffers, 136–137
instant file initialization, 101 BACKUPIO Wait Type
lowering, 99–102 backup/restore operation, 140, 142
perform volume internals, 140–141
maintenance tasks, 100 lowering, 142
storage-related Wait Types, 102–103 modified backup query, 141
storage subsystem, 97 sys.dm_os_wait_stats
sys.dm_os_wait_stats DMV, 98–99 DMV, 141–142
visual representation, 98 Backups-related Wait Types
ASYNC_NETWORK_IO Wait Type BACKUPBUFFER, 136–140
application requests, 106 BACKUPIO, 140–142
graphical representation, 103 BACKUPTHREAD, 142–144
lowering, 104–106 RTO and RPO, 135
modification, 105 SQL Server
sys.dm_os_ wait_stats DMV, 104 backup strategy, 135
task manager network utilization, 105 BACKUPTHREAD Wait Type
Atomicity characteristics, 145–146 AdventureWorks
database, 143–144
backup/restore operation, 142
„„ B
        lowering, 144
Background processes relation, 143
benign, 247 summary, 144
CHECKPOINT_QUEUE, 248–251 Baseline analysis
DIRTY_PAGE_POLL, 251–253 comparison graph, 64
LAZYWRITER_SLEEP, 253–255 disk-read latency, 64–65
miscellaneous, 247 flowchart, 61
MSQL_XP, 255–257 measurements, 66–67
OLEDB Client Provider, 257–259 metrics, 60
TRACEWRITE, 259–266 PAGEIOLATCH_SH, 62–63
WAITFOR, 266–267 performance-analysis
BACKUPBUFFER Wait Type flowchart, 65–66
backup/restore operation, 136, 140 sys.dm_os_waiting_tasks DMV, 62
BufferCount parameter, 138 Buffer latches, 174

© Enrico van de Laar 2015 301

E. van de Laar, Pro SQL Server Wait Statistics, DOI 10.1007/978-1-4842-1139-7
■ index

„„ C
        communications layer, 211
database mirroring
Checkpoint file pairs (CFPs) monitor, 208, 213–214
abstract view, 271 lowering, 213–214
data file and row data, 271 Mirror_Test table, 211–212
memory-optimized tables, 270–271 modification transaction, 209–210
merge operation, 272–273 results, 212
multiple data files, 271–272 synchronous mirror mode, 208–209
offline checkpoint thread, 272 Dedicated Administrator
transaction-commit Connection (DAC), 96
timestamps, 271–272 Detect Waits
WAIT_XTP_HOST_WAIT, 278 blocking information, 30
CHECKPOINT_QUEUE Wait Type query results, 30
operations, 251 scenario, 29
SQL Server instances, 248 sys.dm_exec_sessions, 30–31
sys.dm_os_wait_stats and sys.dm_os_ sys.dm_os_waiting_tasks, 29
waiting_tasks DMV, 249–250 Wait Statistics flowchart, 32
sys.dm_os_wait_stats DMV, 250 DIRTY_PAGE_POLL Wait Type
CMEMTHREAD Wait Type checkpoint feature, 251
lowering, 107–108 configuration option, 252
memory objects, 106, 108 dirty pages, 253
Mutex object, 106–107 indirect checkpoints, 251–252
mutual exclusions, critical sections SQL Server 2012, 251
and thread safety, 106 summary, 253
query procedure cache, 108 Disk-based tables
SQL Server mini-dump, 107 isolation, 273–274
Common Language Runtime (CLR), 243 SQL Server instance, 270
Consistency characteristics, 145–146 Durable characteristics, 145–146
CPU-related Wait Types Dynamic Management Views (DMVs), 4
CXPACKET, 71–78 detect Waits, 29–32
processors, 71 extended events, 34–45
SOS_SCHEDULER_YIELD, 78–85 perfmon command, 33–34
THREADPOOL, 85–96 sys.dm_exec_requests, 26–29
CXPACKET Wait Type sys.dm_os_waiting_tasks, 23–26
configuration of parallelism, 72–73 sys.dm_os_wait_stats, 21–23
CPU-related, 71
execution plan, 75
lowering parallelism „„ E, F, G
       
configuration, 74–76 Extended events
parallel queries, 71–72 ALTER EVENT SESSION
query execution, 78 command, 43
SELECT operation, 75–76 capture wait statistics
skewed workload, 77–78 information, 35–45
threads, 72 configuration, 36–37
execution, 42–43
filter, 39–40
„„ D
        live Wait Statistics information, 41–42
Database administrator (DBA), 29 monitor-event selection, 37–38
Database Management Systems (DBMSs), 5 Person.Person table, 41
DBMIRROR_SEND Wait Type query results, 44–45
AdventureWorks database, 211 returns files as rows, 43–44
asynchronous mode, 210 sessions folder, 35–36

302
 ■ Index

SQL Server Profiler, 34–35 isolation level, 273–274

sql_text global field, 38–39 memory-optimized tables, 270
start extended event session, 40–41 traditional tables, 270
sys.dm_xe_map_values, 35 transaction log changes, 274
types, 35 WAIT_XTP_CKPT_CLOSE, 279–281
WAIT_XTP_HOST_WAIT, 274–278
WAIT_XTP_OFFLINE_CKPT_
„„ H
        NEW_LOG, 281–283
HADR_LOGCAPTURE_WAIT and HADR_ Internet Information Services (IIS), 229
WORK_QUEUE Wait Types IO_COMPLETION Wait Type
AlwaysOn Availability Groups, 214 database-related actions, 109
asynchronous-commit mode, 215 lowering, 111
availability modes, 215 storage-related actions, 109, 111
database mirroring transaction log backup, 109–110
configuration, 214–215 IO latches, 174
primary and secondary IO-related Wait Types
replicas, 215–216 ASYNC_IO_COMPLETION, 97–103
summary, 216 ASYNC_NETWORK_IO, 103–106
synchronous-commit mode, 215 CMEMTHREAD, 106–109
HADR_SYNC_COMMIT Wait Type IO_COMPLETION, 109–111
AdventureWorks database, 218 LOGBUFFER and
AlwaysOn operation mode, 220 WRITELOG, 111–116
AO_Test table, 219 RESOURCE_SEMAPHORE, 116–122
column bar, 220–221 RESOURCE_SEMAPHORE_
dashboard option, 220 QUERY_COMPILE, 122–128
generation inside, 217 SLEEP_BPOOL_FLUSH, 128–133
lowering, 220–222 WRITE_COMPLETION, 133–134
perfmon counters, 221–222 Isolation levels
primary and secondary replicas, 219 characteristics, 145–146
secondary replica, 222 definition, 149
synchronous replication dirty reads, 150
mode, 217, 223 inconsistent analysis, 150
sys.dm_os_wait_stats DMV, 218 locking behavior, 150–151
High-availability and phantom reads, 150
disaster-recovery (HA/DR) read uncommitted values, 149–150
configurations, 207 serializable, 150
DBMIRROR_SEND, 208–214 transaction, 151–152
HADR_LOGCAPTURE_WAIT and
HADR_WORK_QUEUE, 214–216
HADR_SYNC_COMMIT, 217–223 „„ L
       
REDO_THREAD_ Latches
PENDING_WORK, 223–225 classes, 293–300
virtual machines, 207 spinlocks, 289–290
Latch-related Wait Types
compatibility matrix, 173
„„ I, J, K
        description, 171
INDEXPROPERTY function, 195 latch definition, 172
In-Memory OLTP-related Wait Types LATCH_[xx], 189–196
checkpoint file pairs, 270–273 locking and blocking, 171
disk-based tables, 270 modes, 172–173
feature analysis, 269 PAGEIOLATCH_SH occur, 173–174

303
■ index

Latch-related Wait Types (cont.) LCK_M_S Wait Type

PAGEIOLATCH_[xx], 197–205 data modification query, 156
page-latch contention, 176–179 lock-related Wait Type, 155
PAGELATCH_[xx], 180–189 lowering, 157–158
sys.dm_os_wait_stats, 174–176 SELECT command, 157
transactions, 171–172 Shared lock, 156
wait occurs, 173–175 transactions, 158
LATCH_[xx] Wait Type LCK_M_U Wait Types
B-tree index structure, 192–193 concurrent transactions, 159–160
buffer cache, 189 graphical representation, 159
INDEXPROPERTY function, 195 lowering LCK_M_U waits, 161
lowering, 196 Ostress utility, 160–161
memory area, 190 Person.Address table, 160
navigation of B-tree, 193–194 session ID 74, 161
non-clustered index, 191 Update lock mode, 158, 161
resource_description LCK_M_X Wait Type
column, 191–192 COMMIT statement, 163
results, 192 exclusive lock, 162–163
sys.dm_db_index_physical_stats HumanResources.Employee table, 163
results, 195 lock-related Wait Types, 162
sys.dm_os_wait_stats and sys.dm_os_ lower LCK_M_X Wait Times, 163
latch_stats, 190 SELECT statement, 162
test contention table, 191 shared locks, 162
TRUNCATE command, 194–195 Lock-related Wait Type
LAZYWRITER_SLEEP Wait Type ACID properties, 145
checkpoint and characteristics, 145
lazywriter processes, 254 concurrent lock situation, 147–148
flowchart, 254 hierarchy, 148–149
lazywriter process shares, 253–254 isolation characteristics, 145–146
SQL Server process, 255 LCK_M_I[xx], 163–167
LCK_M_I[xx] Wait Type LCK_M_S, 155–158
COMMIT command, 165 LCK_M_SCH_S &
Intent lock, 163–164, 167 LCK_M_SCH_M, 167–170
locking hierarchy, 164–165 LCK_M_U, 158–161
lowering, 166 LCK_M_X, 162–163
SELECT statement, 165 level of isolation, 149–152
Shared Intent Update, 164 lock modes and
Shared with Intent Exclusive, 164 compatibility, 146–148
sys.dm_os_waiting_tasks DMV, 165 querying lock information, 152–155
Update Intent Exclusive, 164 relational database, 145
LCK_M_SCH_S & LOGBUFFER and WRITELOG Wait Types
LCK_M_SCH_M Wait Types approaches, 115
COMMIT/ROLLBACK flowchart, 111–112
command, 168 insert row, 114–115
lowering, 170 log buffer and storage
Schema locks, 167, 170 subsystem, 111–112
Sch-S and Sch-M, 167–168 Ostress utility, 115
SELECT query, 169 processes transactions, 116
sys.dm_os_waiting_tasks DMV, 168 transaction movement, 112–113
table modification query, 169 trans_demo database, 113–114

304
 ■ Index

Lowering TRACEWRITE Waits IO performance script, 202–203

export SQL server profiler trace, 264 latch-related Wait Type, 197, 205
reader_spid column, 263–264 lowering, 200–205
server-side tracing, 266 modification, 203–204
sp_ trace_setstatus, 265–266 monitoring storage
sys.traces, 265 performance, 201–202
sys.traces catalog view, 263 results, 200
trace definition, 264–265 storage subsystem, 197–198
sys.dm_os_wait_stats DMV, 199–200
usage of buffer cache, 205
„„ M
        Page-latch contention, 176–179
Memory-optimized tables PAGELATCH_[xx] Wait Type
Checkpoint file pairs, 270–273 AdventureWorks database, 182
SQL Server instance, 270 clustered index, 181–182
WAIT_XTP_HOST_WAIT, 274–276 DBCC IND command, 183–184
Merge operation, 272–273 graphical representation, 180
MSQL_XP Wait Type ID_Hash column, 187
lowering MSQL_XP Waits, 257 last-page insert
MARS, 255 contention, 181, 185–186
PREEMPTIVE_ leaf level, 181–182
GETPROCADDRESS, 255 lowering, 184–189
results of, 256 non-partitioned index, 187–188
stored procedures, 256–258 Ostress command, 182–183
sys.dm_os_wait_stats DMV, 256 page-latch contention, 180
Multiple Active Result partitions, 188–189
Sets (MARS), 255–258 query results, 183
Perfmon command, 33–34
PREEMPTIVE_OS_
„„ N
        AUTHENTICATIONOPS
Non-buffer latches, 174 Wait Type
connection, 240
lowering, 241–242
„„ O
        mixed-mode authentication, 239
Object Linking and Embedding Database query, 240–241
(OLEDB). See OLEDB Wait Type results, 241
Offline checkpoint thread, 272 SQL Server connects, 239
OLEDB Wait Type summary, 242
CHECKDB command, 258 Windows authentication
Client Provider, 257 functions, 239
DBCC command, 257 PREEMPTIVE_OS_ENCRYPTMESSAGE
internal processes, 259 and PREEMPTIVE_OS_
lowering OLEDB Waits, 258–259 DECRYPTMESSAGE Wait Types
query result, 258 account selection, 231
sys.dm_os_wait_ stats DMV, 258 certificate selection, 232–233
connection properties, 233–234
encrypted connection, 234–235
„„ P
        encrypting and
PAGEIOLATCH_[xx] Wait Types decrypting messages, 228
buffer cache latches, 198 features view, 229
data-page movement, 198–199 Internet Information Services, 229
disk operations, 197 lowering, 235

305
■ index

PREEMPTIVE_OS_ENCRYPTMESSAGE
and PREEMPTIVE_OS_
„„ Q
       
DECRYPTMESSAGE Wait Querying lock information
Types (cont.) resource_description column, 153
permissions dialog opens, 231–232 sp_WhoIsActive, 155
queries, 235 sp_WhoIsActive default results, 154
self-signed certificate, 231 sql_text output, 154
Server Certificates view, 230 stored procedure, 154
SQL Server instance, 228–229 sys.dm_tran_locks DMV, 152
summary, 235 Querying SQL Server Wait statistics.
Transparent Data Encryption, 229 See Dynamic Management
PREEMPTIVE_OS_ Views (DMVs)
GETPROCADDRESS Wait Type
Common Language Runtime, 243
extended stored procedures, 243,
„„ R
       
245–246 REDO_THREAD_PENDING_
master database, 244 WORK Wait Type
overview, 243 characteristic HADR_ prefix, 223
query Wait Statistics, 245 data modifications, 223–224
summary, 246 Redo Thread, 225
xp_cmdshell, 244 replication modes, 225
xp_getnetname, 245 secondary replica inside, 223–224
PREEMPTIVE_OS_ summary, 225
WRITEFILEGATHER Wait Type synchronous AlwaysOn group, 224
AdventureWorks database Relational Database Management
data file, 237 System (RDBMS), 145
default database file RESOURCE_SEMAPHORE_QUERY_
configuration, 236 COMPILE Wait Type
description, 236 actual execution plan properties, 125
instant file initialization, 238 compilation memory, 123
lowering PREEMPTIVE_OS_ compilation-memory
WRITEFILEGATHER resource semaphore, 122–123
Waits, 237–238 concurrent connections, 124–125
storage subsystem, 238 DBCC MEMORYSTATUS
Windows operating system, 236 command, 124, 126
Preemptive Wait Type gateways, 123
graphical representation, 227 lowering, 127–128
PREEMPTIVE_OS_ memory shortage, 122
AUTHENTICATIONOPS, 239–242 resource semaphores, 122, 128
PREEMPTIVE_OS_ sys.dm_os_waiting_tasks DMV, 126
ENCRYPTMESSAGE and threshold value, 124
DECRYPTMESSAGE, 228–235 RESOURCE_SEMAPHORE Wait Type
PREEMPTIVE_OS_ additional memory, 116
GETPROCADDRESS, 243–246 AdventureWorks database, 117
PREEMPTIVE_OS_ lowering, 121
WRITEFILEGATHER, 236–238 MemoryGrantInfo
SQL Server, 227–228 properties, 117–118
Windows functions, 227–228 parallel query, 118
WRITEFILEGATHER function, 228 perfmon counter, 121

306
 ■ Index

query-memory request, 116, 122 SOS_SCHEDULER_YIELD Wait Times

required memory, 116 AdventureWorks database, 80
resource semaphore, 116–117 CPU queries, 84–85
SELECT command, 119–120 lowering, 81–85
sys.dm_exec_query_resource_ Ostress, 82–83
semaphores, 119–120 phases and queues, 79
sys.dm_os_waiting_tasks DMV, 119 RUNNING state, 79
situations, 81
SQL Server instance, 85
„„ S
        SQL Server scheduling, 78
Schedulers, tasks and worker threads sys.dm_os_wait_stats DMV, 81
DMVs, 15 worker thread, 79–80
requests, 10 Spinlocks
results, 15–16 advantage, 290
schedulers, 14–15 class contention, 292
sessions, 9 difference, 289–290
SQL Server scheduling, 8 internal algorithm, 290
tasks, 10–11 latches, 289
worker threads, 11–13 lightweight synchronization
Schema Stability (Sch-S) and Schema primitives, 289
Modification (Sch-M), 167–168 sys.dm_os_spinlock_stats, 290–291
Serializable isolation level, 150 troubleshoot, 291
Shared Intent Update (SIU), 164 SQL Operating System (SQLOS)
Shared with Intent Exclusive (SIX), 164 architecture, 6
SLEEP_BPOOL_FLUSH Wait Type DBMS, 5
CHECKPOINT command, 132 preemptive scheduling approach, 7
checkpoint process, 128, 130–133 quantum, 7
database data file, 128–129 simplified architecture, 6–7
data modification process, 129 UMS, 7–8
results, 132 SQL server machine configurations
sys.dm_os_wait_stats, 132 HA/DR, 286–287
trans_demo database, 131 In-Memory OLTP, 287–288
types, 129–130 Ostress, 285
Solid baseline test machine, 285–286
analysis, 60–67 Sys.dm_exec_requests DMV, 26–29
(see Wait Statistics analysis) Sys.dm_os_waiting_tasks DMV
benefits, 49 description, 24–25
comparing measurements, 52 query, 25–26
definition, 48 results, 23–24
graph, 49–50 Sys.dm_os_wait_stats, 174–176
impact baseline, 48 Sys.dm_os_wait_stats DMV, 21–23
intervals, 52
iterative process, 48–49
key information, 52 „„ T
       
metrics, 52 TempDB files, 178–179
performance problem, 47–48 Test machine
pitfalls, 51 HA/DR, 286–287
types and statistics, 50–51 In-Memory OLTP, 287–288
visualization, 49–50 SQL server machine, 285–286

307
■ index

THREADPOOL Wait Type schedule measurements, 58–60

CPU usage, 95 screenshot, 58–59
CXPACKET waits, 93–94, 96 sys.dm_os_wait_stats DMV, 54
dedicated administrator T-SQL script, 56
connection, 92 Wait Statistics internals
error execution, 90–91 abstract view, 16
formulas, 86 configuration option, 3
gaining access, 91–92 database administrators/developers, 3
Ostress user sessions, 95 DBCC command, 4
parallelism lowering, 92–94 execution time calculation, 17–18
processors configuration, 86–87 history, 3–5
quertes, 87 milliseconds forward
SQL Server scheduling, 85, 88–91 with scheduler, 19
sys.dm_os_waiting_tasks, 90 phases and queues, 19
sys.dm_os_waiting_tasks DMV, 95 Runnable Queue, 17
tasks and worker threads, 89 RUNNING phase, 17
test machine, 87–88 running requests with scheduler, 18
thread pool starvation, 86 schedulers, tasks and
user connections, 94–96 worker threads, 8–16
TRACEWRITE Wait Type. See also SQLOS, 5–8
Lowering TRACEWRITE Waits SUSPENDED, 16
default event selection, 261 Wait Times and worker
management tools, 260 thread flow, 17
query execution, 262–263 WAIT_XTP_CKPT_CLOSE Wait Types
SQL:BatchCompleted event, 262 automatic/manual checkpoint, 279
SQL Profiler trace, 259–260 call stack, 280
trace button, 261 checkpoint operations, 281
Traditional tables results, 280
commit timestamps, 271 sys.dm_os_wait_stats DMV, 279–280
log changes, 274 WAIT_XTP_HOST_WAIT
SQL Server instance, 270 CFPs creation, 278
Transparent Data Encryption (TDE), 229 checkpoint command, 276–277
database data and log files, 275
definition, 274–275
„„ U, V
        extended events session, 277
Update Intent Exclusive (UIX), 164 hkengine.dll file, 277
User Mode Scheduling (UMS), 7 In-Memory filegroup files, 277–278
SQL Server instance, 276
sys.dm_os_wait_stats and sys.dm_os_
„„ W, X, Y, Z
        waiting_tasks DMV, 276
WAITFOR Wait Type, 266–267 WAIT_XTP_OFFLINE_CKPT_NEW_LOG
Wait Statistics analysis Wait Type, 281–283
baseline creation, 52 Worker threads
capture method, 56–58 formulas, 11
database, 53 processors page, 12
datetime data type, 54 state column, 13
delta method, 55 sys.dm_os_threads, 11
disadvantage, 55 sys.dm_os_workers, 13
job schedule, 59–60 WRITE_COMPLETION
reset capture method, 56 Wait Type, 133–134

308