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.
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.
Exploring Programming Constructs in T-SQL – Part 2: Loops and CASE Expressions
Welcome back to our series on programming constructs in T-SQL! In Part 1 of this series, we explored the fundamentals of variables and conditional IF statements, laying the groundwork for dynamic and efficient SQL scripting. In this second installment, we’ll delve deeper into T-SQL by examining two more powerful constructs: Loops (specifically the WHILE loop) and CASE Expressions. Understanding these constructs will enable you to handle repetitive tasks and implement conditional logic directly within your SQL queries, further enhancing the flexibility and capability of your scripts.
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.
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.
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.
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.
Five More SQL Server Error Log Messages You Should Care About
In a previous post, we explored five significant SQL Server error log messages that demand immediate attention. However, the SQL Server error log contains a wealth of additional information essential for maintaining the health and performance of your databases. In this sequel, we will highlight five more error log messages that should be on your radar. We’ll look into what each error means and offer suggestions for when you encounter them.