Previously published as SQL Server query performance tuning.
Includes bibliographical references and index.
Intro; Table of Contents; About the Author; About the Technical Reviewer; Acknowledgments; Introduction; Chapter 1: SQL Query Performance Tuning; The Performance Tuning Process; The Core Process; Iterating the Process; Performance vs. Price; Performance Targets; "Good Enough" Tuning; Performance Baseline; Where to Focus Efforts; SQL Server Performance Killers; Insufficient or Inaccurate Indexing; Inaccurate Statistics; Improper Query Design; Poorly Generated Execution Plans; Excessive Blocking and Deadlocks; Non-Set-Based Operations; Inappropriate Database Design; Recompiling Execution Plans.
Additional Memory Monitoring ToolsDBCC MEMORYSTATUS; Dynamic Management Views; Sys.dm_os_memory_brokers; Sys.dm_os_memory_clerks; Sys.dm_os_ring_buffers; Sys.dm_db_xtp_table_memory_stats; Sys.dm_xtp_system_memory_consumers; Monitoring Memory in Linux; Memory Bottleneck Resolutions; Optimizing Application Workload; Allocating More Memory to SQL Server; Moving In-Memory Tables Back to Standard Storage; Increasing System Memory; Changing from a 32-Bit to a 64-Bit Processor; Compressing Data; Enabling 3GB of Process Address Space; Addressing Fragmentation; Summary.
Chapter 3: Disk Performance AnalysisDisk Bottleneck Analysis; Disk Counters; Disk Transfers/Sec; Disk Bytes/Sec; Avg. Disk Sec/Read and Avg. Disk Sec/Write; Buffer Manager Page Reads/Writes; Additional I/O Monitoring Tools; Sys.dm_io_virtual_file_stats; Sys.dm_os_wait_stats; Monitoring Linux I/0; Disk Bottleneck Resolutions; Optimizing Application Workload; Using a Faster I/O Path; Using a RAID Array; RAID 0; RAID 1; RAID 5; RAID 6; RAID 1+0 (RAID 10); Using a SAN System; Using Solid-State Drives; Aligning Disks Properly; Adding System Memory; Creating Multiple Files and Filegroups.
Frequent Recompilation of QueriesImproper Use of Cursors; Excessive Index Fragmentation; Summary; Chapter 2: Memory Performance Analysis; Performance Monitor Tool; Dynamic Management Views; Hardware Resource Bottlenecks; Identifying Bottlenecks; Bottleneck Resolution; Memory Bottleneck Analysis; SQL Server Memory Management; Available Bytes; Pages/Sec and Page Faults/Sec; Paging File %Usage and Page File %Usage; Buffer Cache Hit Ratio; Page Life Expectancy; Checkpoint Pages/Sec; Lazy Writes/Sec; Memory Grants Pending; Target Server Memory (KB) and Total Server Memory (KB).
Moving the Log Files to a Separate Physical DiskUsing Partitioned Tables; Summary; Chapter 4: CPU Performance Analysis; Processor Bottleneck Analysis; % Processor Time; % Privileged Time; Processor Queue Length; Context Switches/Sec; Batch Requests/Sec; SQL Compilations/Sec; SQL Recompilations/Sec; Other Tools for Measuring CPU Performance; Sys.dm_os_wait_stats; Sys.dm_os_workers and Sys.dm_os_schedulers; Query Store; Measure CPU Behavior in Linux; Processor Bottleneck Resolutions; Optimizing Application Workload; Eliminating Excessive Compiles/Recompiles; Using More or Faster Processors.
0
8
8
8
8
Identify and fix causes of poor performance. You will learn Query Store, adaptive execution plans, and automated tuning on the Microsoft Azure SQL Database platform. Anyone responsible for writing or creating T-SQL queries will find valuable the insight into bottlenecks, including how to recognize them and eliminate them. This book covers the latest in performance optimization features and techniques and is current with SQL Server 2017. If your queries are not running fast enough and you're tired of phone calls from frustrated users, then this book is the answer to your performance problems. SQL Server 2017 Query Performance Tuning is about more than quick tips and fixes. You'll learn to be proactive in establishing performance baselines using tools such as Performance Monitor and Extended Events. You'll recognize bottlenecks and defuse them before the phone rings. You'll learn some quick solutions too, but emphasis is on designing for performance and getting it right. The goal is to head off trouble before it occurs. What You'll Learn: Use Query Store to understand and easily change query performance Recognize and eliminate bottlenecks leading to slow performance Deploy quick fixes when needed, following up with long-term solutions Implement best practices in T-SQL to minimize performance risk Design in the performance that you need through careful query and index design Utilize the latest performance optimization features in SQL Server 2017 Protect query performance during upgrades to the newer versions of SQL Server.