Troubleshooting production performance issues is often challenging because detailed query execution data can vanish before you realize there’s a problem. Many times, you end up piecing together incomplete clues from runtime statistics or the plan cache. Fortunately, SQL Server 2019 introduced a powerful feature to address this pain point: the Last Known Actual Query Plan.
Author: Yvonne Vanslageren
Understanding Worker Threads vs. CPU Utilization in SQL Server
When managing SQL Server performance, it’s important to understand the relationship—and the distinction—between worker threads and CPU utilization. These are two separate metrics that can behave independently. For instance, you might experience high CPU utilization while still having available worker threads, or you could have all worker threads occupied even when CPU utilization is low. This post takes a look at SQL Server worker threads and CPU utilization.
SQL Server’s Query Store: Understanding Plan Forcing and Performance Optimization
The Query Store in SQL Server is a powerful feature that enables database administrators and developers to track and analyze query performance over time. By storing runtime statistics and execution plans, it provides invaluable insights into how queries behave and how they can be optimized. This blog looks into the mechanics of the Query Store, the necessity of plan forcing, and how to effectively use it to stabilize and enhance query performance.
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 SQL Server’s TRUSTWORTHY Database Setting
In SQL Server, the TRUSTWORTHY database setting can grant elevated permissions across a database boundary, influencing how certain modules execute, particularly those using WITH EXECUTE AS or unsafe CLR assemblies. Enabling TRUSTWORTHY allows SQL Server to “trust” that the database owner and associated users won’t misuse elevated permissions to perform unauthorized actions. However, this setting comes with significant security risks if used improperly.
SSISDB Catalog and Delegation in Double-Hop Scenarios
Managing SQL Server Integration Services (SSIS) in complex, multi-server environments can present challenges, especially when it comes to credential delegation across multiple servers—a situation often referred to as the “double-hop” problem. In this post, we’ll explore how the SSISDB catalog handles delegation, the default behavior, and how to configure delegation to ensure your SSIS packages run smoothly across different servers.
Diagnosing and Resolving Last Page Insert Contention in SQL Server
In high-concurrency environments, SQL Server databases may experience performance bottlenecks due to contention issues. One common issue is Last Page Insert Contention, which occurs when multiple sessions attempt to insert records into the last page of an index simultaneously. This contention can lead to significant wait times and reduced performance, particularly in tables with ever-increasing key columns. We will look at page insert contention and give recommendations for improvement.
Testing Always Encrypted with Parameterized Queries in SQL Server
It’s more important than ever to be vigilant in protecting and securing our data. Always Encrypted is a feature in SQL Server designed to protect sensitive data, such as Social Security numbers or credit card information. In this guide, we’ll focus on testing Always Encrypted using parameterized queries to ensure data remains secure during common operations like searching, inserting, and updating.
Using TRUNCATE TABLE with Table Partitions in SQL Server
In SQL Server, the TRUNCATE TABLE command is often favored over DELETE for its efficiency when removing data from a table. However, when working with partitioned tables, the locking behavior changes slightly. Additionally, the LOCK_ESCALATION = AUTO setting can further optimize performance by managing lock escalation at the partition level. We explore how these features interact, providing an in-depth look at locking behavior when truncating partitioned tables and the role of LOCK_ESCALATION = AUTO.
Automating Database Restoration Between SQL Server Availability Groups Using PowerShell
In this post, I’ll walk you through a PowerShell script that automates the restoration of a database from one AG to another. The script handles everything—from performing a COPY-ONLY backup on the source AG to restoring the database on the target AG’s primary and secondary replicas, applying transaction logs, and rejoining the database to the new AG seamlessly.