0 - Information Only

Server level triggers

Issue: Server level triggers, also known as DDL triggers or logon triggers, fire in response to events at the SQL Server instance level rather than within a specific database.

DDL triggers can respond to events such as database creation, login creation, or permission changes, while logon triggers fire each time a user establishes a session. They are often created to enforce security policies, audit administrative actions, or restrict connections based on criteria such as source application or time of day.

This finding flags instances where one or more server level triggers are currently enabled.

Problem: Server level triggers are not inherently bad, but they execute on every qualifying event and can have outsized impact if poorly designed.

A logon trigger with inefficient logic runs for every single connection attempt, adding latency to every login and potentially locking out all users, including administrators, if it contains an error. A DDL trigger that raises an exception can block legitimate administrative work, and a trigger that writes to a table can cause cascading failures if that table becomes unavailable.

Forgotten triggers from past audits or security initiatives are particularly risky because they continue executing long after the original requirement has ended, and their presence may not be obvious to DBAs troubleshooting unrelated issues.

Change Tracking

Issue: Change Tracking is a lightweight SQL Server feature that records which rows have changed in tracked tables, but not the actual changed values. It is commonly used by applications that need to sync data to mobile clients, caches, or external systems, letting them query only the rows modified since the last synchronization. This finding flags databases where change tracking is currently enabled, which warrants review to confirm it is still needed and properly maintained.

Problem: Change tracking is far less expensive than Change Data Capture, but it is not free. Each tracked table carries extra metadata maintenance on every insert, update, and delete, and internal change tracking tables grow over time based on retention settings. If the auto cleanup process is disabled or falls behind, these internal tables can consume substantial storage and degrade performance.

Change tracking is also often left enabled long after the original consuming application has been retired, continuing to add overhead for no benefit. Additionally, certain operations such as database restores, failovers, and version upgrades require careful handling of change tracking metadata, and a stale configuration can complicate these activities.

Forced parameterization

Issue: Parameterization controls how SQL Server treats literal values in queries when generating execution plans. Under the default SIMPLE setting, SQL Server parameterizes only a narrow set of straightforward queries, leaving most queries with their literal values embedded in the plan cache. When FORCED parameterization is enabled at the database level, SQL Server aggressively replaces nearly all literal values in queries with parameters, producing a single cached plan that can be reused across many similar queries regardless of the specific values passed in.

Problem: Forced parameterization is a significant change in query behavior and is not appropriate for every workload. While it can reduce plan cache bloat and CPU spent on compilation for applications that submit many near identical ad hoc queries, it can also cause serious performance problems when data distributions are skewed. A single plan generated for one set of parameter values may perform poorly for other values, a situation commonly called parameter sniffing.

Forced parameterization also interferes with certain query patterns including those that rely on literal value based filtering for indexed views, filtered indexes, or specific plan choices. When enabled without thorough testing, it can cause widespread query regressions that are difficult to diagnose because the root cause is a database level setting rather than any individual query.

Change Data Capture

Issue: Change Data Capture (CDC) is a SQL Server feature that records insert, update, and delete activity on tracked tables into change tables, allowing downstream systems to consume a stream of data changes. CDC is typically enabled to support data warehousing, ETL processes, or replication to external systems such as Kafka or other analytics platforms.

This finding flags databases where CDC is currently enabled, which warrants review to confirm it is still needed and properly managed.

Problem: CDC is not inherently bad, but it introduces overhead and operational complexity that is often forgotten after the original use case has ended. The CDC capture process reads the transaction log, which means the log cannot be truncated past the oldest unprocessed change, potentially causing the log file to grow unexpectedly if the capture job falls behind or is disabled.

Change tables consume storage that grows continuously unless the cleanup job runs properly, and the capture and cleanup jobs themselves add background workload. CDC also complicates certain operations including schema changes, database restores, and Always On failovers, since CDC metadata and jobs must be handled carefully. When CDC is left enabled after the consuming process has been decommissioned, it silently accumulates cost with no benefit.

Delayed durability

Issue: Delayed durability is a database option introduced in SQL Server 2014 that allows transactions to commit before their log records are actually written to disk. Normally, SQL Server follows strict write ahead logging, meaning a transaction is not considered committed until its log records are hardened to the transaction log file.

With delayed durability enabled, log records are buffered in memory and written asynchronously, allowing the committing transaction to return control to the application immediately. The setting can be configured at the database level as DISABLED, ALLOWED, or FORCED.

Problem: Delayed durability trades data safety for performance. If SQL Server crashes, the host loses power, or the service stops unexpectedly before buffered log records are flushed, any transactions that committed with delayed durability but had not yet been written to disk will be lost, even though the application was told the commit succeeded. This breaks the durability guarantee that most applications assume, and can result in silent data loss that is not detectable until someone notices missing records. When set to FORCED, every transaction in the database is affected regardless of how it was written, meaning even well designed application code cannot opt out.

Extended events

Issue: Extended Events (XE) is SQL Server's built-in diagnostic and tracing framework, used to capture detailed information about server activity. SQL Server ships with several system sessions such as system_health and AlwaysOn_health that are designed to run continuously with minimal overhead. This issue refers to custom, user created XE sessions that are actively running on the instance, often left over from past troubleshooting efforts, performance investigations, or temporary audits.

Problem: Custom XE sessions vary widely in cost depending on which events they capture, how they filter data, and where they write output. Sessions that capture high frequency events (such as sql_statement_completed or lock acquisition events) without proper filtering can generate enormous volumes of data, consume significant CPU and memory, and fill disk volumes if they write to files.

Forgotten sessions are particularly dangerous because they continue accumulating overhead and storage long after the original investigation ended. In some cases, poorly configured sessions can degrade performance enough to affect production workloads, which is exactly the opposite of their intended purpose.

Recursive triggers

Issue: The RECURSIVE_TRIGGERS database option controls whether triggers are allowed to fire themselves recursively. When enabled, a trigger that performs an action causing the same trigger to fire again (direct recursion) will continue to execute until the call stack limit is reached. The setting is off by default, but it can be enabled intentionally for specific scenarios or accidentally during database migrations and template based deployments.

Problem: Recursive triggers are notoriously difficult to debug and can produce unexpected behavior that is hard to trace. A single row update can cascade through multiple trigger executions, resulting in severe performance degradation, unintended data modifications, or stack overflow errors when the 32 level nesting limit is hit.

Most applications do not need recursive trigger behavior, and when it is enabled without a clear design reason, it typically reflects a misconfiguration rather than an intentional choice. The risk is compounded because developers writing triggers often do not realize the option is enabled, leading to bugs that only surface under specific data conditions.

Auto close

Issue: AUTO_CLOSE is a database option that, when enabled, causes SQL Server to shut down a database and release its resources after the last user disconnects. The next time a connection request arrives, SQL Server must reopen the database and restart it before the request can be serviced. The setting is off by default for most editions but is on by default for SQL Server Express, which is a common reason it appears in production environments.

Problem: Every time the database closes and reopens, SQL Server must allocate memory, perform recovery, and rebuild cached execution plans and metadata, which introduces noticeable latency for the first connection after an idle period. For applications that connect intermittently, this produces unpredictable response times and can cause connection timeouts. Auto close also clears the plan cache for that database on every close, eliminating the performance benefits of plan reuse.

In environments with many databases configured this way, the constant open and close cycles generate unnecessary overhead and can flood the error log with database startup messages.

Auto shrink

Issue: AUTO_SHRINK is a database option that, when enabled, causes SQL Server to periodically check for free space in data and log files and automatically shrink them when more than 25 percent of the file is unused. The setting is off by default for new databases but is often found enabled on older databases, databases created from templates, or those restored from legacy systems.

Problem: Auto shrink is widely regarded as one of the most harmful settings in SQL Server. The shrink process generates significant I/O and causes severe index fragmentation, which degrades query performance and increases the workload on your storage. Worse, shrink and growth operations often occur in a damaging cycle: the file shrinks, the database grows again to accommodate normal activity, and the cycle repeats. This wastes resources continuously and can happen at unpredictable times, including during peak business hours. Auto shrink also runs with no awareness of workload, so it can kick off in the middle of critical operations.