SQL Server 2019 Effective Performance Tuning: SQL Server Simplified

Most organizations are generating a huge amount of data and they are storing them in the databases. SQL Server is one of the most preferred relational database solutions on the market and plays a role as a data server for mission-critical applications. When considered from this point of view, any delay or performance decrease occurs on the database server might cause huge problems. To overcome SQL Server’s performance issues, we need to have enough experience and knowledge, but in this book the following tips will help you understand the common performance problems in a simple manner and also explains where they need to look for these issues.

SQL Server Performance Tuning is both an art and a science. There is no right answer when it comes to tuning queries in an SQL Server. The issues you have solved on one system may not be a problem for another, and vice versa.

Therefore, performance tuning in SQL Server can become an incredibly daunting task. Sometimes, even the minutest change can have a dramatic impact on the database performance. However, with a few tweaks in your approach, you can make slow SQL Servers go faster.

Table of contents:

Overview of SQL Server 2019 Performance Tuning
Top Reasons for Slow Performance
Intelligent query processing in SQL databases
Configuring Storage Spaces with a NVDIMM-N write-back cache
Database Engine Tuning Advisor
Reduce the Production Server Tuning Load
Performance Improvements using DTA
Columnstore index recommendations in DTA
Tuning Database Using Workload from Query Store
Execution Plans
Query Profiling Infrastructure
Showplan Logical and Physical Operators Reference
Compare and Analyze Execution Plans
Partitioned Tables and Indexes
Using Partition Wizard
Upgrading Databases by using the Query Tuning Assistant
Resource Governor
Configure Resource Governor Using a Template
Change Resource Pool Settings
Resource Governor Workload Group
Change Workload Group Settings
Statistics
View Statistics Properties
Automatic tuning
Cardinality Estimation
Introduction to Memory-Optimized Tables
Memory Optimization Advisor
Native Compilation Advisor
Estimate Memory Requirements for Memory-Optimized Tables
Configuring Storage for Memory-Optimized Tables
Hardware considerations for In-Memory OLTP in SQL Server
Indexes on Memory-Optimized Tables
Sample Database for In-Memory OLTP
Application Pattern for Partitioning Memory-Optimized Tables
Implementing MERGE Functionality in a Natively Compiled Stored Procedure
SQL Server Management Objects Support for In-Memory OLTP
Troubleshooting Hash Indexes for Memory-Optimized Tables