Trace flags
Issue: Trace flags are switches that change SQL Server's default behavior, used either to address specific performance issues or to enable behaviors that have proven beneficial for most workloads. Some trace flags address behaviors so commonly problematic that they are widely recommended as default startup flags on the affected SQL Server versions.
This finding flags instances that are missing one or more of the following recommended trace flags based on the SQL Server version: 1117, 1118, and 2371 for SQL Server 2014 (version 12) and earlier; 7752 for SQL Server 2016 and 2017 (versions 13 and 14) when Query Store is in use; 7745 for SQL Server 2016 and later when Query Store is in use; and 3226 for all versions.
Each of these flags addresses a specific behavior that, if left at default on the affected versions, can produce measurable performance or operational issues.
Problem: Trace flag 1117 forces all data files in a filegroup to grow at the same time when one file hits the autogrowth threshold, keeping files evenly sized so SQL Server's proportional fill algorithm distributes activity evenly. Without it on SQL Server 2014 and earlier, tempdb data files often become unevenly sized and contention concentrates on the largest file. Starting in SQL Server 2016 this behavior is controlled by the AUTOGROW_ALL_FILES database option, and the trace flag has no effect.
Trace flag 1118 forces uniform extent allocation instead of mixed extents, reducing contention on SGAM allocation pages in tempdb. Without it on SQL Server 2014 and earlier, heavy tempdb workloads experience PAGELATCH contention on SGAM pages. Starting in SQL Server 2016 this behavior is controlled by the MIXED_PAGE_ALLOCATION database option (off by default for tempdb), and the trace flag has no effect.
Trace flag 2371 changes the threshold for automatic statistics updates from a fixed 20 percent of rows changed to a dynamic, smaller threshold for large tables. Without it on SQL Server 2014 and earlier, statistics on large tables become stale long before SQL Server updates them, leading to poor query plans. Starting in SQL Server 2016 with database compatibility level 130 or higher, this behavior is the default and the trace flag has no effect.
Trace flag 7752 enables asynchronous load of Query Store data at database startup, so user queries are not blocked while Query Store loads. Without it on SQL Server 2016 and 2017, Query Store databases can experience slow startup behavior. Starting in SQL Server 2019, this behavior is controlled by the engine and the trace flag has no effect.
Trace flag 7745 prevents SQL Server from waiting to flush Query Store data to disk during a database shutdown or failover. Without it on SQL Server 2016 and later, shutdowns and Always On failovers can be delayed waiting for Query Store data to be written, extending downtime. This trace flag remains relevant in current versions.
Trace flag 3226 suppresses successful backup messages from the SQL Server error log. Without it, frequent log backups (often every few minutes) clutter the error log with success messages, making it difficult to find actual errors when troubleshooting. This trace flag is widely recommended on all versions and has no negative side effects, since backup history is still recorded in msdb regardless.
Learn More...Unusual database state
Issue: Every SQL Server database has a state that reflects its current operational condition. Several other states exist that indicate the database is in an abnormal condition: SUSPECT, EMERGENCY, RECOVERY_PENDING, RESTORING, and COPYING.
This finding flags databases that are in a state other than ONLINE or OFFLINE. Each of these unusual states points to a specific situation, ranging from in progress operations that should resolve on their own to serious failures that require immediate attention.
Problem: Each unusual state has different implications and warrants investigation, since the database is not available for normal use while in any of these states.
SUSPECT means SQL Server attempted recovery during startup but failed, typically due to corruption, missing files, or storage failures. The database is inaccessible, and the underlying cause must be identified before recovery actions are taken to avoid making the situation worse.
RECOVERY_PENDING means SQL Server cannot start recovery, often because of missing files, permission problems, or storage that was unavailable at startup. The database is also inaccessible until the underlying issue is resolved.
EMERGENCY is an administrator initiated state used during recovery from corruption. It allows a sysadmin to access an otherwise unrecoverable database in read only mode for diagnosis or to perform repair operations such as DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. A database left in EMERGENCY mode unintentionally is not protected and is vulnerable to further damage.
RESTORING is normal during a restore operation but indicates a problem if the database remains in this state for an extended period without an active restore in progress. This commonly happens when a restore was initiated WITH NORECOVERY for log shipping or staging purposes and the recovery step was never completed.
COPYING applies to Azure SQL Database and Managed Instance and indicates a copy operation is in progress, but its presence on a traditional SQL Server instance is unusual and warrants investigation.
In all of these cases, the database is unavailable to applications, and the unusual state often signals an underlying problem with storage, files, permissions, or process execution that should be addressed before more damage occurs.
Learn More...Instance configuration
Issue: Many SQL Server configuration options can be changed at runtime using sp_configure followed by RECONFIGURE, but some settings only take full effect after the SQL Server service is restarted. When a change has been made but the service has not yet been restarted, the option shows different values for value (the configured value) and value_in_use (the value currently in effect) in the sys.configurations system view.
This finding identifies instances where one or more configuration options have a pending change waiting on a service restart. Common examples include max worker threads, min server memory, max server memory, network packet size, and certain advanced settings.
The condition can persist for weeks or months when changes are made but a restart is not scheduled promptly, leaving the instance in a state where the configured intent does not match the actual running behavior.
Problem: The most immediate issue is that the intended configuration change is not actually in effect. Whoever made the change believes the new value is active, while the instance continues to behave according to the previous setting. This creates a gap between documented configuration and actual behavior that can mislead troubleshooting and tuning efforts.
The condition also creates risk during the next service restart, whether scheduled or unplanned. A restart that occurs unexpectedly (due to patching, host failover, or service crash) will activate the pending change at an unpredictable time, potentially during peak business hours and without the team being prepared for any side effects. If the change turns out to be incorrect or causes problems, the issue surfaces in the worst possible context.
Pending changes also accumulate. When multiple changes are made over time without a restart, the instance carries an increasing list of differences between configured and active values, making it harder to know which change is responsible if behavior shifts after the eventual restart.
Learn More...SQL Agent job schedule
Issue: SQL Server Agent jobs run scheduled administrative and business tasks such as backups, integrity checks, index maintenance, and ETL processes. A job has its own enabled or disabled state, and each schedule attached to the job also has its own enabled or disabled state. Both must be enabled for the job to run automatically at the intended time.
This finding flags two related conditions: a job that is enabled but has no schedule attached at all, and a job that is enabled but whose attached schedule is disabled. In either case, the job will not run on a recurring basis even though it appears active in the job list.
These conditions typically arise from manual changes made during troubleshooting, deployments that did not complete cleanly, or jobs that were intentionally created for ad hoc execution but never properly retired or documented.
Problem: A job that does not run on the expected schedule may go unnoticed for a long time, particularly if no one is actively watching for missed executions. When the job exists for a critical purpose (backups, integrity checks, replication agents, ETL processes), the missed runs can have serious consequences, including missing recovery points, undetected corruption, broken downstream data flows, and silent data divergence.
The risk is amplified by the appearance that all is well. The job is visible in SQL Server Agent, its status shows as enabled, and casual review of the job list does not reveal anything wrong. Only by drilling into the schedule details does the missing or disabled schedule become apparent, which is a step that ad hoc inspections often skip.
Jobs that are deliberately run only on demand are a legitimate use case, but they should be clearly marked or named so they are not confused with jobs that should be running on a schedule. Without that distinction, the team cannot easily tell which jobs are intentional manual jobs and which are misconfigured scheduled jobs.
Learn More...Database files best practice
Issue: User database files (data and log) should be placed on storage that supports the performance, availability, and capacity needs of the workload. SQL Server best practices recommend placing data and log files on dedicated drives separate from the operating system drive, allowing files to grow as needed, and separating log files from data files onto different physical volumes when possible.
This finding flags one or more of three related conditions: user database files placed on the C drive when other drives are present on the server, user database files configured with MAXSIZE set to a value other than UNLIMITED (preventing growth beyond the cap), and user database log and data files located on the same drive when separate volumes exist.
Each condition represents a deviation from accepted storage best practices and is typically the result of default settings being accepted during database creation rather than a deliberate design choice.
Problem: Files on the C drive consume space on the operating system volume, which is sized for Windows and SQL Server binaries rather than user data. If the C drive fills up, Windows itself can become unstable, paging fails, and the SQL Server service may stop or refuse to start, causing an outage that affects every database on the instance. Backups and other administrative operations also compete for the same drive, increasing contention and recovery risk.
Files configured with a maximum size limit will stop growing when they hit the cap, even when free space remains on the underlying drive. The result is a query failure or transaction abort that surfaces to the application, often during routine activity that should have completed normally. The cap is sometimes set as a safety measure, but proper drive sizing and monitoring achieve the goal more reliably without creating an artificial failure point.
Log and data files on the same drive eliminate the I/O performance benefits of separating sequential log writes from random data file activity. They also create a single point of failure: if the drive is lost, both the database and its transaction log are lost simultaneously, which can prevent recovery to the most recent committed transaction even when backups exist.
Together, these conditions reduce performance, increase the risk of outages, and complicate disaster recovery in ways that are easy to avoid with proper file placement during database creation.
Learn More...Lock pages in memory
Issue: Lock Pages in Memory (LPIM) is a Windows privilege that, when granted to the SQL Server service account, allows SQL Server to lock its memory allocations so that Windows cannot page them out to disk. It is enabled by granting the "Lock pages in memory" user right to the SQL Server service account through Local Security Policy or Group Policy. Once granted and after a service restart, SQL Server uses the AWE memory allocator and locks buffer pool pages in physical RAM.
This finding identifies instances where LPIM is currently in use. It is not necessarily a problem, but it is worth noting in case the team is unaware that LPIM is enabled, or unaware of the operational considerations that come with it.
Problem: LPIM was historically recommended to prevent Windows from paging out SQL Server's working set during memory pressure, which can cause severe performance degradation when buffer pool pages are written to and read from the page file. On servers experiencing this specific problem, LPIM provides a meaningful protection against working set trimming and is still considered a valid configuration choice on dedicated SQL Server hosts.
However, LPIM also changes how SQL Server interacts with the operating system in important ways. Locked memory cannot be reclaimed by Windows under any circumstances, so if SQL Server's max server memory is set too high or other applications on the host need memory, the entire server can become unresponsive rather than gracefully redistributing memory. This makes proper max server memory configuration critical, since the safety net of Windows being able to reclaim memory no longer applies.
Diagnostics and monitoring also behave differently with LPIM enabled. Memory usage reported by Task Manager and Performance Monitor may not reflect SQL Server's actual consumption, since locked pages are accounted for differently, and troubleshooting memory issues requires looking at SQL Server's own memory dynamic management views rather than operating system tools.
Learn More...Resource Governor
Issue: Resource Governor is a SQL Server feature that allows administrators to manage the consumption of CPU, memory, and physical I/O resources by classifying incoming requests into workload groups and applying resource limits to those groups. It is primarily used to prevent runaway queries from monopolizing the instance, to provide predictable performance for critical workloads, and to enforce service level differentiation between tenants on a shared instance.
This finding identifies instances where Resource Governor is currently enabled. It is not necessarily a problem, but it is worth noting in case the team is unaware that Resource Governor is active, or unaware of the operational considerations that come with it.
Problem: Resource Governor introduces an additional layer of configuration that affects how queries execute, and its presence can produce performance behavior that is difficult to diagnose without knowing it is in play. Queries that appear to run slowly or hit memory grant timeouts may be subject to limits imposed by their workload group, not by overall server pressure, and troubleshooting from wait statistics alone may not reveal the cause.
The classifier function, which assigns each new connection to a workload group, runs on every login. A poorly written or slow classifier function can add measurable latency to connection establishment and, in extreme cases, prevent logins entirely if it errors out, including blocking administrators. Enabling the dedicated administrator connection (DAC) is particularly important on instances using Resource Governor for this reason.
Additionally, Resource Governor configuration is stored in the master database and depends on the classifier function continuing to exist and behave correctly. Restores, migrations, and version upgrades all require careful handling so that workload groups, resource pools, and the classifier function are preserved or recreated correctly in the new environment.
Learn More...Offline CPU schedulers
Issue: SQL Server creates one scheduler for each logical processor it can see at startup. Each scheduler is responsible for managing worker threads on its assigned CPU. When a scheduler is in the VISIBLE OFFLINE state, it has been recognized by SQL Server but is not being used to run user queries.
The most common cause is licensing limits in SQL Server editions. Standard Edition is capped at 24 cores (32 cores in SQL Server 2025) or 4 sockets, whichever is lower. Web Edition (no longer available in SQL Serer 2025) is capped at 16 cores, and Express Edition is capped at 4 cores. When a SQL Server instance is installed on a host with more cores than the edition allows, the extra cores show up as VISIBLE OFFLINE schedulers.
Other causes include the affinity mask setting being configured to exclude specific CPUs, or the use of soft NUMA configurations that intentionally take certain schedulers offline.
Problem: The most immediate issue is wasted hardware capacity. A server purchased and provisioned with 32 cores running SQL Server Standard Edition will only use 24 of them, leaving the remaining 8 cores idle from SQL Server's perspective while still being licensed at the operating system level and consuming hardware budget.
This can go unnoticed for years because the server appears to have plenty of CPU available in Windows Task Manager and other operating system level monitoring, while SQL Server itself is constrained to a smaller subset. Performance investigations may focus on tuning queries or adding memory when the real bottleneck is artificial CPU restriction.
There is also a risk of confusion during troubleshooting. Wait statistics, scheduler related queries, and CPU pressure indicators can be misleading when only some schedulers are active, and administrators unfamiliar with the licensing limits may misdiagnose the symptoms.
Just as importantly, paying for hardware that SQL Server cannot use is a direct cost issue. The licensing decision and the hardware decision should be aligned, but in practice they often are not, particularly when virtual machines are sized generously without considering edition limits.
Learn More...Auto create statistics
Issue: SQL Server's query optimizer relies on statistics about the distribution of data in tables and indexes to generate efficient execution plans. The AUTO_CREATE_STATISTICS database option, enabled by default, allows SQL Server to automatically create single column statistics on columns referenced in query predicates when no usable statistics already exist.
This finding indicates that one or more databases have AUTO_CREATE_STATISTICS set to OFF. The setting is sometimes disabled in response to a specific vendor recommendation or as part of an attempt to control statistics management manually, but in most environments leaving it on is the correct choice.
Problem: Without auto created statistics, the optimizer has less information to work with when building query plans, which can lead to poor cardinality estimates, inappropriate join types, missing or oversized memory grants, and inefficient execution plans overall. The performance impact is often subtle and inconsistent, since the optimizer falls back to defaults or guesses when statistics are missing, producing plans that may work acceptably for some data distributions and very poorly for others.
The problem is particularly difficult to diagnose because the symptoms appear as general query slowness rather than a specific error. Developers and DBAs may spend significant time tuning queries that would simply run well with the appropriate statistics in place.
Disabling auto create statistics also places the entire burden of statistics management on the team, requiring explicit creation of statistics on every column the optimizer might benefit from. In practice this is rarely done comprehensively, so most queries end up worse off than they would be with the automatic behavior.
Learn More...Replication
Issue: SQL Server replication is a set of technologies for copying and distributing data and database objects between SQL Server instances, with several types available including snapshot, transactional, merge, and peer to peer replication. Replication is configured around publishers (the source), distributors (the intermediary), and subscribers (the destinations), each of which holds metadata, agents, and jobs that support the replication topology.
This finding identifies instances where replication is currently configured and active. It is not necessarily a problem, but it is worth noting in case the team is unaware that replication is in use, or unaware of the operational considerations that come with it.
Problem: Replication adds operational complexity that affects multiple aspects of database administration. The transaction log of a published database cannot be truncated past records that have not yet been delivered to subscribers, so a stalled or disconnected replication agent can cause unexpected log growth and potentially fill the log volume.
Backup and restore procedures become more involved because replication metadata is stored in distribution and msdb databases, and restoring a published database without proper handling can break the replication topology. Schema changes on published tables require coordination, since not all changes propagate automatically and some require explicit replication aware procedures.
Replication also depends on SQL Server Agent jobs and dedicated agent processes (Log Reader, Distribution, Snapshot, and Merge agents) that must be monitored. When these agents fail or fall behind, data divergence can occur silently between publisher and subscribers, and the longer the issue persists, the harder it becomes to resolve without reinitializing subscriptions.
Also, replication interacts with other features in non-obvious ways. Always On Availability Groups, database mirroring, log shipping, Change Data Capture, and Change Tracking all have specific compatibility considerations with replication that affect failover, upgrades, and disaster recovery procedures.
Learn More...About sp_checks
This page contains a list of SQL Server configuration checks performed by Straight Path's suite of sp_check tools. For more details about our free tools, select one from the following list: