Cardinality estimation (CE) is how the optimizer predicts the number of rows that will flow through each operator in a plan. Those estimates drive cost, join choices, memory grants, and ultimately latency and resource usage. SQL Server has shipped multiple CE models over time. The pre-2014 model—commonly called the legacy CE—dates back to SQL Server 7.0. Starting in SQL Server 2014, Microsoft introduced a new CE and has continued refining it in later releases, including SQL Server 2022. Keeping the legacy CE turned on in SQL Server 2022 is usually the wrong long-term choice.
Category: SQL Server 2022
Resumable Maintenance in SQL Server: Index Rebuilds, Index Creation, and ALTER TABLE ADD CONSTRAINT
Large tables make routine maintenance risky. Traditional online index builds and ALTER TABLE ADD CONSTRAINT operations run as a single long‑lived transaction. If an outage or fail‑over occurs near the end, SQL Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL Server 2017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017–2022) and resumable online ADD CONSTRAINT(2022).
Saving Time with SQL Server Migrations Using Always On Availability Groups and Log Shipping
Migrating databases to a newer version of SQL Server is a critical task that often comes with challenges, especially when aiming to minimize downtime. Leveraging SQL Server’s Always On Availability Groups and log shipping features allows you to perform most of the migration work in advance, ensuring a seamless transition with minimal impact on your operations. This blog provides a step-by-step approach to migrating from SQL Server 2017 to SQL Server 2022, focusing on efficiency and continuity.
Understanding the Timeline of Query Optimization Improvements in SQL Server 2022
Intelligent Query Processing (IQP) is a suite of advanced features introduced in SQL Server 2017 and enhanced in subsequent versions, including SQL Server 2022. By upgrading to SQL Server 2022 and setting your database compatibility level to 160, you enable capabilities that dynamically optimize query performance. Understanding when these performance improvements take effect can help database administrators and developers plan and manage their optimization strategies effectively. The journey toward maximum performance improvement is gradual and influenced by factors such as workload characteristics and system configurations.
Securing NorthPine Bank’s Data: How SQL Server 2022 Can Help
NorthPine Bank, a fictitious yet representative financial institution, recognized the necessity to modernize its data infrastructure to safeguard sensitive customer information against emerging threats. The bank decided to migrate its operations to SQL Server 2022, leveraging its advanced security features to enhance data protection, ensure regulatory compliance, and maintain operational efficiency. This blog explores the specific security challenges faced by NorthPine Bank and details how SQL Server 2022 addresses these issues through its robust, built-in features.
The Hidden Layers of Dynamic Data Masking in SQL Server
Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.
Creating SQL Server AGs on Linux: Part 2 – Enabling HADR on SQL Server
This is part of a series on setting up SQL Server availability groups on Linux. It details how to enable High Availability Disaster Recovery (HADR) on SQL Server, including creating master keys, certificates, and availability groups. Clear instructions are provided for configuring SQL Server and Pacemaker to ensure data redundancy and high availability. Follow these steps to enhance the reliability of your SQL Server instances on Ubuntu 20.04.
Creating SQL Server AGs on Linux: Part 1 – Installing and Configuring Pacemaker
This post is the first part of a series on setting up SQL Server availability groups on Linux. It covers the installation and configuration of Pacemaker on Ubuntu 20.04, creating the Pacemaker cluster, and configuring essential cluster properties. Designed for SQL Server DBAs with limited Linux experience, this guide lays the foundation for a robust high availability environment.
SQL Server on Linux: Getting Started with SQL Server 2022 on Red Hat 9
Learn how to install and configure SQL Server 2022 on Red Hat Enterprise Linux (RHEL) 9. This guide provides step-by-step instructions for adding the Microsoft repository, installing SQL Server and its tools, and performing basic configurations to optimize SQL Server for enterprise environments on RHEL 9.
Navigating the Changes in SQL Server 2022: A Guide to Server Alias Configuration
If you’re embarking on a fresh installation of SQL Server 2022, you might be in for a surprise. Gone are the familiar days of the SQL Server Native Client being a core component of SQL Server installations. This blog post will discuss the recent changes in SQL Server 2022 and how to navigate server alias configuration in this new landscape.