SQL Server با عملکرد بالا: کتابی برای سریع‌تر شدن

Contents at a Glance......Page 5 Contents......Page 6 About the Author......Page 13 About the Technical Reviewers......Page 14 Acknowledgments......Page 15 Introduction......Page 16 TDS/Network Protocols......Page 18 Schedulers......Page 20 Workers......Page 22 Parsing and Binding......Page 25 Query Optimization......Page 26 Plan Caching......Page 29 Operators......Page 30 Data Access Operators......Page 31 Nested Loops Join......Page 32 Updates......Page 33 Memory Grants......Page 34 Locks and Latches......Page 35 Summary......Page 36 Chapter 2: Analyzing Wait Statistics......Page 37 Introduction......Page 38 sys.dm_os_wait_stats......Page 42 Extended Events......Page 46 system_health Extended Event Session......Page 48 Example: Analyzing CXPACKET Waits......Page 50 Latches and Spinlocks......Page 53 THREADPOOL......Page 55 Timer Wait Types......Page 56 Blocking......Page 58 Summary......Page 59 Plan Changes......Page 60 Identify Expensive Queries......Page 62 Architecture......Page 63 Enabling, Purging, and Disabling the Query Store......Page 65 Using the Query Store......Page 70 Performance Troubleshooting......Page 74 Incomplete Queries......Page 76 Force Failure......Page 79 Catalog Views......Page 80 Live Query Statistics......Page 82 Summary......Page 84 Statistics Update......Page 85 tempdb Configuration......Page 87 Query Optimizer Hotfixes and Trace Flag 4199......Page 88 max degree of parallelism......Page 89 Instant File Initialization......Page 91 Cardinality Estimator......Page 92 optimize for ad hoc workloads......Page 93 Memory Configuration......Page 94 Lock Pages in Memory......Page 95 blocked process threshold......Page 96 Advanced Trace Flags......Page 97 Summary......Page 98 DML Contention......Page 99 Describing tempdb Latch Contention......Page 102 Trace Flags 1117 and 1118......Page 103 What Is New in SQL Server 2016......Page 104 tempdb Events......Page 106 Sort Warning......Page 111 Monitoring Disk Space......Page 112 Summary......Page 113 Chapter 6: SQL Server In-Memory Technologies......Page 114 In-Memory OLTP......Page 115 Memory-Optimized Tables......Page 116 Indexes......Page 119 Natively Compiled Modules......Page 121 Changing Tables and Natively Compiled Modules......Page 123 Native Compilation......Page 125 Memory-Optimized Table Variables......Page 126 Columnstore Indexes......Page 127 Examples......Page 129 Operational Analytics......Page 134 Using Disk-Based Tables......Page 136 Using Memory-Optimized Tables......Page 139 Summary......Page 142 Chapter 7: Performance Troubleshooting......Page 143 Performance Counters......Page 144 Comparing Batches and Transactions......Page 145 Log Growths......Page 147 Page Life Expectancy......Page 149 % Processor Time......Page 150 Processor Queue Length......Page 151 SQL Compilations/Sec and Re-compilations/Sec......Page 152 Checkpoint Pages/Sec......Page 153 sys.dm_os_performance_counters......Page 154 sys.dm_io_virtual_file_stats......Page 155 sys.dm_db_index_usage_stats......Page 157 sys.dm_db_index_physical_stats......Page 158 sys.dm_os_sys_info......Page 160 SQL Trace/Extended Events......Page 161 SQL Server Data Collector......Page 163 Operator Level Performance Statistics......Page 164 Trace Flags on Plans......Page 165 Summary......Page 166 Chapter 8: Indexing......Page 167 How SQL Server Uses Indexes......Page 168 Where to Use Indexes......Page 169 Index Usage Validation......Page 171 Heaps......Page 172 Clustered Indexes......Page 173 Filtered Indexes......Page 174 Working with Indexes......Page 175 The Missing Indexes Feature......Page 183 The Database Engine Tuning Advisor......Page 185 Summary......Page 192 Chapter 9: SQL Server Storage......Page 193 Storage Types......Page 194 Database Configuration......Page 195 Fragmentation......Page 196 Compression......Page 197 Metrics and Performance......Page 198 Resource Monitor......Page 199 Diskspd......Page 200 SQLIOSim......Page 202 RAID Levels......Page 203 RAID 1......Page 204 RAID 5......Page 205 RAID 10......Page 206 Query Processing......Page 207 Summary......Page 208 Index......Page 209 Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. You’ll learn advanced configuration options, in-memory technologies, storage and disk configuration, and more, all toward enabling your desired application performance and throughput.Configuration doesn’t stop with implementation. Workloads change over time, and other impediments can arise to thwart desired performance. __**High Performance SQL Server**__ covers monitoring and troubleshooting to aid in detecting and fixing production performance problems and minimizing application outages. You'll learn a variety of tools, ranging from the traditional wait analysis methodology to the new query store, and you'll learn how improving performance is really an iterative process.__**High Performance SQL Server**__ is based on SQL Server 2016, although most of its content can be applied to prior versions of the product. This book is an excellent complement to performance tuning books focusing on SQL queries, and provides the other half of what you need to know by focusing on configuring the instances on which mission-critical queries are executed. * Covers SQL Server instance-configuration for optimal performance * Helps in implementing SQL Server in-memory technologies * Provides guidance toward monitoring and ongoing diagnostics **What You Will Learn*** Understand SQL Server's database engine and how it processes queries * Configure instances in support of high-throughput applications * Provide consistent response times to varying user numbers and query volumes Design databases for high-throughput applications with focus on performance* Record performance baselines and monitor SQL Server instances against them * Troubleshot and fix performance problems ****Who This Book Is For****SQL Server database administrators, developers, and data architects. The book is also of use to system administrators who are managing and are responsible for the physical servers on which SQL Server instances are run. Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. You'll learn advanced configuration options, in-memory technologies, storage and disk configuration, and more, all toward enabling your desired application performance and throughput. Configuration doesn't stop with implementation. Workloads change over time, and other impediments can arise to thwart desired performance. High Performance SQL Server covers monitoring and troubleshooting to aid in detecting and fixing production performance problems and minimizing application outages. You'll learn a variety of tools, ranging from the traditional wait analysis methodology to the new query store, and you'll learn how improving performance is really an iterative process. High Performance SQL Server is based on SQL Server 2016, although most of its content can be applied to prior versions of the product. This book is an excellent complement to performance tuning books focusing on SQL queries, and provides the other half of what you need to know by focusing on configuring the instances on which mission-critical queries are executed. Covers SQL Server instance-configuration for optimal performance Helps in implementing SQL Server in-memory technologies Provides guidance toward monitoring and ongoing diagnostics What You Will Learn Understand SQL Server's database engine and how it processes queries Configure instances in support of high-throughput applications Provide consistent response times to varying user numbers and query volumes Design databases for high-throughput applications with focus on performance Record performance baselines and monitor SQL Server instances against them Troubleshot and fix performance problems Who This Book Is For SQL Server database administrators, developers, and data architects. The book is also of use to system administrators who are managing and are responsible for the physical servers on which SQL Server instances are run. Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. You'll learn advanced configuration options, in memory technologies, storage and disk configuration, and more, all toward enabling your desired application performance and throughput