If you’ve run a security hardening script against SQL Server and seen sp_ssis_startup flagged as a risk, you’re not alone. Automated scanners treat any startup procedure as a potential threat, and on the surface that logic makes sense. In practice, disabling this one causes real problems that are easy to miss until something breaks. This post covers what sp_ssis_startup actually does, why the threat model behind the scanner recommendation doesn’t hold up, and what happens to your SSIS Catalog execution state when the procedure isn’t there to clean things up after a restart. Includes verification queries and a recommended approach for satisfying security reviewers without breaking your catalog.
Category: Troubleshooting
Five Helpful Queries for SQL Server DBAs
As a SQL Server DBA, having the right set of queries can significantly streamline your workflow, enhance monitoring, and aid in troubleshooting. This post shares five essential queries that address common administrative tasks, each accompanied by a detailed explanation to help you understand their purpose, interpret the results, and take appropriate action.
Reading SQL Server’s XML Deadlock Report Captured by the system_health Event
SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.
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.
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.
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.
Manually Synchronizing a Lagging Secondary Replica in SQL Server Always On Availability Groups
In high-availability environments, SQL Server Always On Availability Groups (AG) are essential for minimizing downtime and preventing data loss. However, situations can arise where a secondary replica lags behind the primary, disrupting synchronization and risking potential data inconsistencies. This blog provides instructions on how to manually catch up a lagging secondary replica using transaction log backups.