Compatibility levels determine how certain features of SQL Server behave, allowing databases to maintain functionality even as the server itself evolves. However, changing compatibility levels requires careful consideration to balance the benefits of new features against the potential risks of disrupting existing applications. This blog explores the importance of handling compatibility level changes thoughtfully during SQL Server upgrades, highlighting the risks of making changes too hastily and the advantages of adopting new compatibility levels within a reasonable timeframe.
Category: Internals
Leveraging SQL Server 2019’s Last Known Actual Query Plan for Troubleshooting
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.
Using sp_getapplock to Prevent Concurrent Execution of Stored Procedures in SQL Server
In multi-user database environments, ensuring the integrity and consistency of data is essential. One common challenge is preventing multiple instances of the same stored procedure from executing at the same time, which can lead to data conflicts, race conditions, and unpredictable results. To address this, SQL Server provides a powerful tool called sp_getapplock. This post explains what sp_getapplock is, what you are actually locking when you use it, how to implement it to prevent concurrent executions of a stored procedure, and the difference between transaction-level and session-level locks.
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.
Determining When to Optimize SQL Server Workloads Versus Upgrading Hardware
When your SQL Server isn’t performing as expected, it’s tempting to think that upgrading hardware is the quick fix. More CPUs, extra memory, faster disks—they all sound like solutions that should solve the problem. But before you start investing in new equipment, it’s worth taking a step back to see if optimization can address the issues.
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.
Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server
Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It’s especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I’ll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.
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.