Linked e-resources
Details
Table of Contents
At a Glance; Contents; About the Author; About the Technical Reviewers; Acknowledgments; Introduction; Chapter 1: How SQL Server Works; TDS/Network Protocols; How Work Is Performed; SQLOS; Schedulers; Workers; Query Optimization; Parsing and Binding; Query Optimization; Plan Caching; Query Execution; Operators; Data Access Operators; Aggregations; Joins; Nested Loops Join; Merge Join; Hash Join; Parallelism; Updates; Memory Grants; Locks and Latches; Summary; Chapter 2: Analyzing Wait Statistics; Introduction; Waits Information; sys.dm_os_wait_stats; sys.dm_exec_session_wait_stats.
Extended Eventssystem_health Extended Event Session; Example: Analyzing CXPACKET Waits; Latches and Spinlocks; Waits; CX_PACKET; PAGELATCH_ and PAGEIOLATCH_; ASYNC_NETWORK_IO; SOS_SCHEDULER_YIELD; THREADPOOL; PREEMPTIVE_*; OLEDB; IO_COMPLETION; WRITELOG; Timer Wait Types; Blocking; In-Memory OLTP; Summary; Chapter 3: The Query Store; Why Is a Query Slow?; Plan Changes; How the Query Store Can Help; Plan Regressions; SQL Server Upgrades; Application/Hardware Changes; Identify Expensive Queries; Identify Ad Hoc Workloads; Architecture; Enabling, Purging, and Disabling the Query Store.
Using the Query StorePerformance Troubleshooting; Incomplete Queries; Force Failure; Catalog Views; Live Query Statistics; Summary; Chapter 4: SQL Server Configuration; Statistics Update; Standard Automatic Statistics Update; Trace Flag 2371 and SQL Server 2016; tempdb Configuration; Query Optimizer Hotfixes and Trace Flag 4199; max degree of parallelism; cost threshold for parallelism; Instant File Initialization; Cardinality Estimator; optimize for ad hoc workloads; SQL Server Editions; Memory Configuration; Lock Pages in Memory; backup compression default; query governor cost limit.
Blocked process thresholdAdvanced Trace Flags; Summary; Chapter 5: tempdb Troubleshooting and Configuration; DML Contention; Describing tempdb Latch Contention; Fixing tempdb Latch Contention; Using Multiple Data Files; Trace Flags 1117 and 1118; What Is New in SQL Server 2016; tempdb Events; DDL Contention; tempdb Spill Warnings; Sort Warning; Hash Warning; Exchange Warning; Monitoring Disk Space; Summary; Chapter 6: SQL Server In-Memory Technologies; In-Memory OLTP; What Is New in SQL Server 2016; Memory-Optimized Tables; Indexes; Natively Compiled Modules.
Changing Tables and Natively Compiled ModulesNative Compilation; Memory-Optimized Table Variables; Current Limitations; Columnstore Indexes; Examples; Operational Analytics; Using Disk-Based Tables; Using Memory-Optimized Tables; Summary; Chapter 7: Performance Troubleshooting; Performance Counters; Comparing Batches and Transactions; Log Growths; Data File(s) Size (KB); Page Reads/Sec; Page Writes/Sec; Page Life Expectancy; Buffer Cache Hit Ratio; % Processor Time; Processor Queue Length; Latches; Locks; LogicalDisk and PhysicalDisk; SQL Compilations/Sec and Re-compilations/Sec
Memory Grants.
Extended Eventssystem_health Extended Event Session; Example: Analyzing CXPACKET Waits; Latches and Spinlocks; Waits; CX_PACKET; PAGELATCH_ and PAGEIOLATCH_; ASYNC_NETWORK_IO; SOS_SCHEDULER_YIELD; THREADPOOL; PREEMPTIVE_*; OLEDB; IO_COMPLETION; WRITELOG; Timer Wait Types; Blocking; In-Memory OLTP; Summary; Chapter 3: The Query Store; Why Is a Query Slow?; Plan Changes; How the Query Store Can Help; Plan Regressions; SQL Server Upgrades; Application/Hardware Changes; Identify Expensive Queries; Identify Ad Hoc Workloads; Architecture; Enabling, Purging, and Disabling the Query Store.
Using the Query StorePerformance Troubleshooting; Incomplete Queries; Force Failure; Catalog Views; Live Query Statistics; Summary; Chapter 4: SQL Server Configuration; Statistics Update; Standard Automatic Statistics Update; Trace Flag 2371 and SQL Server 2016; tempdb Configuration; Query Optimizer Hotfixes and Trace Flag 4199; max degree of parallelism; cost threshold for parallelism; Instant File Initialization; Cardinality Estimator; optimize for ad hoc workloads; SQL Server Editions; Memory Configuration; Lock Pages in Memory; backup compression default; query governor cost limit.
Blocked process thresholdAdvanced Trace Flags; Summary; Chapter 5: tempdb Troubleshooting and Configuration; DML Contention; Describing tempdb Latch Contention; Fixing tempdb Latch Contention; Using Multiple Data Files; Trace Flags 1117 and 1118; What Is New in SQL Server 2016; tempdb Events; DDL Contention; tempdb Spill Warnings; Sort Warning; Hash Warning; Exchange Warning; Monitoring Disk Space; Summary; Chapter 6: SQL Server In-Memory Technologies; In-Memory OLTP; What Is New in SQL Server 2016; Memory-Optimized Tables; Indexes; Natively Compiled Modules.
Changing Tables and Natively Compiled ModulesNative Compilation; Memory-Optimized Table Variables; Current Limitations; Columnstore Indexes; Examples; Operational Analytics; Using Disk-Based Tables; Using Memory-Optimized Tables; Summary; Chapter 7: Performance Troubleshooting; Performance Counters; Comparing Batches and Transactions; Log Growths; Data File(s) Size (KB); Page Reads/Sec; Page Writes/Sec; Page Life Expectancy; Buffer Cache Hit Ratio; % Processor Time; Processor Queue Length; Latches; Locks; LogicalDisk and PhysicalDisk; SQL Compilations/Sec and Re-compilations/Sec
Memory Grants.