Hide instance
Issue: The Hide Instance setting is a SQL Server network configuration option that prevents the SQL Server Browser service from exposing the instance to client “browse/enumeration” requests (for example, when users click Browse to discover SQL Servers).
When enabled, the instance typically does not appear in enumerated lists returned by discovery tools, and clients must connect using the normal endpoint details rather than relying on Browser-based discovery; for hidden named instances this generally means specifying the port number (and using a static port is recommended).
The setting is configured in SQL Server Configuration Manager under the protocols for the affected instance, on the Flags tab. It is sometimes enabled deliberately as a hardening step to reduce casual discovery of SQL Server instances on the network, but it does not prevent access if an attacker already knows the endpoint details.
This finding identifies instances where Hide Instance is enabled. This is not necessarily a problem, but it’s worth noting in case the team is unaware the setting is active or unaware of the potential client connectivity implications (especially for named instances).
Problem: Hide Instance provides modest security benefit by removing the instance from automated enumeration responses. An attacker scanning for SQL Server instances using browser-based discovery will not see the hidden instance in the response, which raises the bar slightly for opportunistic discovery. The protection is limited because port scanning, DNS enumeration, and other techniques can still find the instance, but the feature does eliminate one of the simpler discovery paths.
The main client compatibility impact is for named instances, especially those using dynamic ports. Clients that rely on SQL Server Browser to resolve Server\Instance to the current TCP port will no longer be able to do so for a hidden instance. In these cases, clients must specify an explicit port (for example Server,Port) or use an alias, and configuring the instance to a static port is the common remediation.
Named instances are particularly affected. By default, named instances listen on dynamic ports that change at startup, and the SQL Server Browser service is what tells clients which port to use for a given instance name. With Hide Instance enabled, clients cannot resolve the dynamic port through the browser and must connect using a server name plus an explicit port number. The standard remediation is to configure the named instance to listen on a static port and document the port for client teams.
Hide Instance is also sometimes enabled in environments where the team is not aware of the implications, often as a side effect of applying a security hardening template. The condition is worth reviewing to confirm the feature is being used deliberately and that the supporting client connectivity model has been adjusted to work with hidden instances.
Learn More...Common criteria compliance
Issue: Common Criteria Compliance is a SQL Server feature that, when enabled, configures the engine to meet the requirements of the international Common Criteria security certification at the Evaluation Assurance Level (EAL) defined for SQL Server. Enabling it changes specific runtime behaviors to comply with the certification requirements, including residual information protection, login statistics tracking, and column level GRANT permission handling.
The feature is controlled by the common criteria compliance enabled server configuration option, which requires show advanced options to be on. Enabling the option requires a SQL Server service restart for the changes to take full effect.
This finding identifies instances where Common Criteria Compliance is currently enabled. The condition is most often the result of a configuration applied to meet a specific government, defense, or regulated industry requirement.
Problem: Common Criteria Compliance changes several behaviors that can affect performance and produce unexpected query results compared to a standard SQL Server installation. Residual information protection requires that memory be overwritten before being reallocated, which adds CPU overhead to memory management operations and can affect throughput on busy instances.
The column level GRANT behavior also changes in a way that interacts with table level DENY permissions differently than the SQL Server default. This can produce permission outcomes that surprise developers and administrators who are not aware that the option is enabled, particularly when troubleshooting access issues that appear inconsistent with the documented permission model.
The login statistics tracking adds a small amount of overhead to every login but provides useful audit information about successful and failed login attempts and the timing of those events. This is generally a positive side effect of the option, but the increased volume of statistics data should be accounted for in monitoring and audit storage planning.
The feature is also tied to specific SQL Server versions and editions that have been certified. Upgrading to a newer version may invalidate the certification status until the new version is itself certified, which can affect compliance posture even if the configuration option remains enabled. Organizations relying on the certification for compliance should track the certification status of their SQL Server version against current requirements.
Learn More...C2 audit mode
Issue: C2 audit mode is a legacy SQL Server feature that enables auditing of system level activities to support compliance with the U.S. Department of Defense "C2" security standard. When enabled, SQL Server writes a comprehensive audit trail of successful and failed login attempts, statement permissions checks, and many other security relevant events to trace files in the default data directory.
The feature was introduced in earlier SQL Server versions and is enabled through the c2 audit mode server configuration option, which requires show advanced options to be on. C2 audit mode has been deprecated for many years and is replaced by SQL Server Audit, which provides equivalent or better functionality with significantly more flexibility and lower operational impact.
This finding identifies instances where c2 audit mode is currently enabled. The condition is most often the result of a configuration applied many years ago to meet a specific compliance requirement that has since evolved or been replaced.
Problem: C2 audit mode produces a very high volume of audit data, since it captures every statement permission check and many other low level events. The resulting trace files can grow rapidly and consume significant storage, particularly on busy instances where each query generates multiple audit records.
The feature also has a critical operational characteristic that catches teams by surprise: if SQL Server cannot write audit records (because the disk is full, the audit directory is unreachable, or the trace fails for any reason), the SQL Server service shuts down. This behavior is by design under the C2 standard, which requires that auditing not silently fail, but it means that any disruption to the audit destination causes a complete outage of the database engine.
C2 audit mode is also deprecated and will eventually be removed from SQL Server. Microsoft has documented this deprecation across many versions and recommends migrating to SQL Server Audit, which offers granular control over which events are captured, multiple destination types (file, Windows event log, security log), and better performance characteristics.
Learn More...Trace flag 1118
Issue: Trace flag 1118 is not enabled globally. This trace flag is recommended for SQL Server 2014 and earlier.
Problem: Enabling trace flag 1118 tells SQL Server to avoid mixed extents by allocating each 64 KB extent to a single object. Doing this results in slightly more data pages, but reduces the possibility of contention.
Learn More...Trace flag 1117
Issue: Trace flag 1117 is not enabled globally. This trace flag is recommended for SQL Server 2014 and earlier.
Problem: Enabling trace flag 1117 allows all files in a file group to grow at the same time. If you have set your tempdb files to grow equally, this means they will all grow when one grows, which should reduce the possibility of contention.
Learn More...Unequally sized tempdb data files
Issue: Tempdb is typically configured with multiple data files to reduce allocation contention on shared system pages such as PFS, GAM, and SGAM. SQL Server distributes activity across multiple data files using a proportional fill algorithm, where each file receives writes in proportion to its free space relative to the other files in the filegroup.
For the proportional fill algorithm to distribute work evenly, the data files must be sized equally. When the files have different sizes, the algorithm directs more activity to the larger files, defeating the purpose of having multiple files in the first place.
This finding identifies instances where the tempdb data files are not all the same size.
Problem: Unequal tempdb file sizes cause the proportional fill algorithm to send disproportionate activity to the largest files. This concentrates allocation requests on a subset of the data files and reintroduces the very contention that having multiple files was supposed to eliminate, including the PAGELATCH waits on allocation pages that are the most common reason to add files in the first place.
The condition often results from autogrowth events that did not apply equally to all files. Without trace flag 1117 (on SQL Server 2014 and earlier) or the AUTOGROW_ALL_FILES filegroup option (on SQL Server 2016 and later), only the file that triggered autogrowth grows, leaving the others at their original size. Over time, repeated autogrowth events on different files can produce significant divergence in file sizes.
Manual changes can also produce the imbalance. When tempdb files are added to an instance, they sometimes get added at different sizes than the existing files, or initial sizing during installation may not have been applied uniformly. Without periodic review, the imbalance persists indefinitely and the team may not realize it is undermining tempdb performance.
Learn More...File growth settings for tempdb
Issue: Tempdb is the system database used for sorts, hash joins, temporary tables, table variables, version store activity, and many internal operations. Because tempdb is critical to instance-wide performance, its file configuration deserves particular attention, especially the autogrowth settings and the relative sizing of its data files.
This finding flags tempdb file configurations that violate one or more accepted best practices: tempdb data files with uneven growth rates, tempdb database files configured with percentage-based growth, or tempdb database files configured with fixed growth smaller than 64 MB. Each of these conditions can produce performance problems even when tempdb appears to be working normally.
Problem: Uneven growth rates across tempdb data files cause the files to drift out of equal size as autogrowth events accumulate. SQL Server uses a proportional fill algorithm to distribute writes across data files, so unequal files receive unequal activity, which defeats the purpose of having multiple tempdb files in the first place. The original goal of reducing PAGELATCH contention on allocation pages depends on all files being roughly equal in size, and divergent growth rates undermine that.
Percentage-based growth makes the problem worse over time. As a tempdb file grows larger, each subsequent percentage-based growth event is larger than the last, taking more time and producing increasingly imbalanced files. The unpredictable growth size also makes it harder to plan disk space, since the next growth could be small or very large depending on the current file size.
Growth increments smaller than 64 MB cause frequent autogrowth events, each of which briefly pauses tempdb activity while the file is extended. On busy instances, frequent small growths can produce noticeable query stalls and elevated wait times. Combined with Instant File Initialization not being enabled (a separate finding), small growth events become particularly disruptive because each growth must zero out the new space.
These conditions often coexist because they share a common root cause: tempdb was deployed with default settings or with settings inherited from older guidance, and never revisited. The defaults predate modern hardware and modern guidance, and the conditions surface only as performance problems on busy instances where they compound with other tempdb-related issues.
Learn More...Number of tempdb data files
Issue: By default, a SQL Server installation creates only one tempdb data file. Because tempdb is shared by every database and every session on the instance, allocation operations on internal system pages (PFS, GAM, and SGAM) can become a bottleneck when multiple sessions try to allocate space at the same time. The classic symptom is PAGELATCH waits on tempdb pages.
Microsoft's current guidance is to configure the number of tempdb data files as follows: if the server has eight or fewer logical processors, create one data file per logical processor; if the server has more than eight logical processors, start with eight data files and add four more at a time only if allocation contention persists, never exceeding the number of logical processors.
This finding flags instances where the tempdb file count does not align with this guidance, either too few (typically just one file or far fewer than eight on a multi core server) or too many (a one to one match to a high core count without evidence of contention).
Problem: With too few tempdb data files on a busy multi core server, sessions compete for the same allocation pages, producing PAGELATCH_UP and PAGELATCH_EX waits that slow down every workload using tempdb. This includes sorts, hash joins, temporary tables, table variables, and version store activity for snapshot isolation, so the impact is broad and often shows up as general slowness rather than a specific failure.
With too many tempdb data files (for example, 32 files on a 32 core server when the workload does not require it), SQL Server incurs unnecessary management overhead. Proportional fill, autogrowth coordination, and file handle management all become more complex with no offsetting benefit, and contention may shift to other waits without actually improving overall throughput.
The guidance has evolved over the years, and many environments still reflect older advice (one file per core unconditionally) or simply the default single file installation. Either extreme can produce measurable performance issues that are easy to fix once identified.
Learn More...Slow reads or writes in tempdb
Issue: SQL Server tracks file-level I/O performance through the dynamic management functions and views. For tempdb, slow read or write latency is particularly impactful because tempdb is shared by every database and every session on the instance and is involved in nearly every query that performs sorts, hash joins, spills, temporary tables, table variables, or version store activity.
This finding identifies instances where one or more tempdb files (data or log) are experiencing average read or write latency above 100 ms, which is the default threshold used by sp_CheckTempdb.
Problem: Slow tempdb storage produces broad performance impact across the instance because so many operations depend on tempdb. Queries that spill, use temporary tables, or rely on snapshot isolation all wait on tempdb I/O, and slow latency adds directly to query duration. Workloads that are otherwise well-tuned can appear sluggish for reasons that are not obvious without specifically checking tempdb performance.
Sustained high latency on tempdb often indicates underlying storage problems that affect more than just tempdb. Shared storage volumes, virtualization layer issues, contention from other workloads on the same storage, and degrading drives can all produce elevated latency that affects every database on the affected storage. Tempdb is often the most visible symptom because of its high I/O volume, but the root cause is rarely tempdb-specific.
Slow tempdb writes also extend transaction durations indirectly. Operations that need to flush pages from the tempdb buffer pool to disk, write to the version store, or harden log records pay the slow-write cost on every commit that touches tempdb. The result is degraded throughput and increased lock contention because transactions hold resources longer than they should.
The 100 ms threshold is a conservative starting point for investigation. Modern storage on dedicated drives should typically deliver tempdb latency well below 10 ms for both reads and writes, and any sustained value above 100 ms indicates either misconfigured storage, contention from other workloads, or a hardware issue that needs to be addressed.
Learn More...Tempdb file with no growth allowed
Issue: SQL Server database files have an autogrowth setting that controls how the file expands when it runs out of space. Autogrowth can be configured with a fixed or percentage-based growth value, or it can be disabled entirely by setting the growth value to 0. When autogrowth is disabled, the file cannot grow beyond its current size, and any operation that would require additional space fails with an error.
This finding identifies tempdb data or log files configured with autogrowth disabled. The setting is sometimes applied as a perceived safety measure to prevent tempdb from filling a drive, but it produces the opposite of the intended effect.
Problem: Tempdb is shared by every database and every session on the instance and is involved in sorts, hash joins, spills, temporary tables, table variables, and version store activity. When tempdb runs out of space, queries fail with error 1105, sessions are disrupted, and in severe cases the entire instance can become unstable until space is freed.
Disabling autogrowth on tempdb files creates an artificial ceiling that triggers these failures even when free space is still available on the drive. A single large query, an unexpected sort spill, or a long-running transaction holding the version store open can hit the cap and cause widespread query failures across all databases on the instance.
The intended safety benefit (preventing tempdb from filling the drive) is better achieved through proper drive sizing and monitoring than through a hard size limit. A capped tempdb shifts the failure from a drive-level event to a query-level event, but the failure still occurs and is often more disruptive because it surfaces as application errors during normal operation.
The condition is closely related to setting a maximum size on tempdb files, which produces similar problems and is covered separately. Both settings represent attempts to limit tempdb growth that produce more problems than they solve and are not recommended as defensive measures.
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: