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: Performance
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.
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.
Building a Career in SQL Server: Tips, Strategies, and Pathways for Aspiring DBAs
Starting a career as a SQL Server Database Administrator (DBA) offers numerous opportunities in today’s data-driven landscape. As organizations increasingly rely on databases to manage and analyze their data, skilled DBAs are in high demand. Whether you’re just beginning or looking to advance in this field, here are some key strategies to help you build a successful career in SQL Server.
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.
Monitoring SQL Server on Linux with Native Linux Tools
As organizations deploy SQL Server on Linux, leveraging native Linux tools for monitoring becomes essential to ensure optimal performance and reliability. Tools like htop, vmstat, and iostat provide valuable insights into system resources and help identify potential bottlenecks. This blog explores how to use these native Linux tools to monitor SQL Server effectively.
Archiving Old Transactions in SQL Server Using PowerShell
Archiving old or obsolete data is an effective strategy to maintain optimal performance. In this post, we’ll explore how to archive old transactions from a SQL Server database using PowerShell. We’ll walk through creating the necessary database and tables, populating them with sample data, executing a PowerShell script to automate the archiving process, and verifying the results.
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.
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.