Best Practices
Page Description
This dashboard evaluates SQL Server best practices across three levels: operating system, instance, and databases. Its goal is to identify settings that deviate from a recommended baseline, values that should be reviewed, and configurations that might only be acceptable in specific scenarios.
The page works as a quick configuration health audit. It helps validate general server information, CPU topology, available memory, service account, power plan, and several operating system policies that directly influence SQL Server performance and stability.
It also reviews internal instance settings such as minimum and maximum memory, parallelism, backup compression, trace flags, and advanced affinity or compatibility options. At database and file level, it helps detect deviations in statistics, Query Store, Page Verify, autogrowth, collation, and other settings that are usually worth standardizing.
Variables
| Variable | Description |
|---|---|
$Server_Name | Lets you select the SQL Server instance or server to review. Its values come from the dashboard selector at the top of the page. |
Server Configuration
This section groups together general server and operating system information that affects SQL Server behavior. It combines identification, hardware, memory, and host-level settings to verify that the server foundation is consistent with production use.
Instance Name
- Description: Shows the selected instance name so you can confirm the analysis is targeting the expected server.
- Panel type: stat
Practical recommendation: Always confirm that the selected instance is the correct one before interpreting the rest of the dashboard.
Windows Version
- Description: Shows the Windows version hosting the SQL Server instance.
- Panel type: stat
Practical recommendation: Check that the operating system version is still supported and aligned with the SQL Server version in use.
Service Name
- Description: Shows the account assigned to the SQL Server service.
- Panel type: stat
Practical recommendation: Validate that a dedicated service account is being used and that it only has the privileges required for the instance.
Last Refresh
- Description: Shows the date and time of the latest data collected by Coyote Monitor for the selected server.
- Panel type: stat
- Units: Date and time
Practical recommendation: If the timestamp is stale, review the collector, connectivity, and source permissions before trusting the rest of the page.
Socket Number (NUMA)
- Description: Shows the number of sockets detected on the server, useful for interpreting NUMA layout and edition or licensing limits.
- Panel type: stat
- Units: Sockets
Practical recommendation: If the value is unexpected, compare it with the host or VM configuration because an incorrect topology can affect performance and licensing.
Core Number
- Description: Shows the total number of cores visible on the server.
- Panel type: stat
- Units: Cores
Practical recommendation: Compare this with the SQL Server edition and the actual CPU assignment to detect unwanted limits or provisioning issues.
Sockets vs Cores Relation
- Description: Checks whether the socket-to-vCore relationship is consistent with the detected SQL Server edition and version. Older versions might not provide enough information to validate it.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Correct: 1:x | Correct ratio for the detected edition | |
| No Socket Info | Not enough data to validate | |
| Not Correct... | Ratio should be reviewed |
Practical recommendation: If the ratio is incorrect, review the topology exposed by the OS and hypervisor because it can affect NUMA behavior, performance, and licensing.
Memory (Physical+Virtual)
- Description: Shows the total server memory as the sum of physical and virtual memory available to the environment.
- Panel type: stat
- Units: MB
Practical recommendation: Use this value as the reference point when reviewing max server memory and the memory left for the operating system.
SQL Server Agent Enabled
- Description: Indicates whether SQL Server Agent is enabled to run scheduled jobs, alerts, and automation.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Agent disabled | |
| Enabled | Agent enabled |
Practical recommendation: If it is disabled, confirm that this is intentional because many production environments depend on Agent for backups, maintenance, and operational jobs.
Energy High Performance
- Description: Checks whether the power plan is set to High Performance to avoid performance penalties caused by power saving behavior.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Non-recommended plan | |
| Enabled | Recommended plan |
Practical recommendation: Microsoft guidance generally favors avoiding power saving plans on SQL Server hosts, so review both the host and virtualization layer if it is disabled.
Lock Pages in Memory
- Description: Indicates whether the service account has the Lock Pages in Memory privilege to help stabilize SQL Server memory usage.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Privilege missing | |
| Enabled | Privilege granted |
Practical recommendation: If you enable LPIM, also set max server memory, because locking pages without a proper upper limit can pressure the operating system.
Perform Volume Maintenance Task
- Description: Checks whether the Perform Volume Maintenance Tasks privilege is granted, which is required for instant file initialization of data files.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Privilege not granted | |
| Enabled | Privilege granted |
Practical recommendation: Enable it whenever possible to reduce data file growth and restore times, especially on large databases.
Instance Configuration
This section reviews the internal SQL Server instance configuration. It focuses on version, edition, memory, parallelism, affinity, compression, high availability, and advanced options that often affect performance, daily operations, and configuration consistency.
Version
- Description: Shows the SQL Server version installed on the instance.
- Panel type: stat
Practical recommendation: Verify that the instance is on a supported branch and current enough before evaluating other best practice findings.
Fill Factor
- Description: Shows the global instance fill factor, meaning the target page occupancy used when indexes are created or rebuilt.
- Panel type: stat
- Units: Percentage
Thresholds:
| Value | Color | Description |
|---|---|---|
| Other values | Value should be reviewed | |
| > 80 | Acceptable range | |
| > 100 | Invalid or low-value setting |
Practical recommendation: Avoid setting a global fill factor without evidence; it is usually better to tune only the indexes that actually suffer from page splits or fragmentation.
Edition
- Description: Shows the SQL Server edition in use, which matters for CPU, memory, and feature limits.
- Panel type: stat
Practical recommendation: Compare the edition with the features in use and the hardware limits visible on the instance.
Parallelism
- Description: Evaluates whether parallelism settings are correct, mainly for MAXDOP and cost threshold for parallelism.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Correct | Correct setting | |
| To Check | Setting should be reviewed | |
| 0 | Problematic value | |
| > 25 | Intermediate threshold | |
| > 50 | Recommended threshold | |
| > 51 | Non-standard value |
Practical recommendation: Set MAXDOP according to Microsoft guidance for NUMA and logical processors, and avoid leaving cost threshold for parallelism too low without workload validation.
Priority Boost
- Description: Indicates whether a legacy option is enabled that raises SQL Server priority over other Windows processes.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Recommended state | |
| Enabled | State should be reviewed |
Practical recommendation: Keep this option disabled unless there is an exceptional, documented reason to use it.
Optimize For AdHoc Workloads
- Description: Indicates whether the instance reduces memory pressure from single-use execution plans in ad hoc heavy workloads.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Option disabled | |
| Enabled | Option enabled |
Practical recommendation: This is often useful in OLTP environments with many ad hoc statements and large numbers of single-use plans in cache.
Common Criteria Compliance
- Description: Indicates whether a compliance-related option is enabled that can add overhead in some workloads.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Recommended state | |
| Enabled | State should be reviewed |
Practical recommendation: If it is enabled, confirm that it supports a real compliance requirement and is not just a legacy leftover.
Affinity Mask
- Description: Checks whether SQL Server uses manual CPU affinity instead of letting the operating system distribute the workload.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Automatic management | |
| Enabled | Manual affinity |
Practical recommendation: Keep affinity automatic unless there is a very specific architectural or troubleshooting reason.
Affinity IO Mask
- Description: Indicates whether SQL Server disk I/O is manually bound to a subset of CPUs.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Automatic management | |
| Enabled | Manual affinity |
Practical recommendation: Avoid it unless you have a clearly validated reason, because an incorrect mask can worsen workload distribution.
Min Server Memory
- Description: Shows the minimum amount of memory the instance can keep once workload has reached it.
- Panel type: stat
- Units: MB
Practical recommendation: Use it carefully, especially on consolidated or virtualized servers, so you do not reserve memory that the operating system might need.
Max Server Memory
- Description: Shows the upper memory limit SQL Server can use for the buffer pool.
- Panel type: stat
- Units: MB
Practical recommendation: Set it explicitly so memory remains available for the OS, other processes, and components not governed by the buffer pool limit.
Instance Compatibility Level
- Description: Indicates the highest compatibility level supported by the instance version.
- Panel type: stat
Practical recommendation: Use it as a reference when planning database compatibility upgrades after functional and performance testing.
Compress Backup
- Description: Indicates whether backup compression is enabled by default.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Compression disabled | |
| Enabled | Compression enabled |
Practical recommendation: It is usually worth enabling, but validate CPU impact during the backup window.
Blocked Process Threshold
- Description: Shows the instance-level threshold configured to identify blocked processes.
- Panel type: stat
Thresholds:
| Value | Color | Description |
|---|---|---|
| Other values | Acceptable value | |
| > 1 | Value should be reviewed |
Practical recommendation: If you enable it for diagnostics, pair it with a clear blocking capture and review process.
TempDB Files
- Description: Shows the number of TempDB data files to verify whether the configuration follows a reasonable baseline.
- Panel type: stat
- Units: Files
Thresholds:
| Value | Color | Description |
|---|---|---|
| Other values | Value should be reviewed | |
| > 8 | Recommended value | |
| > 9 | Initial excess | |
| > 12 | Intermediate expansion | |
| > 13 | Excess to review | |
| > 16 | Advanced expansion | |
| > 17 | Very high value |
Practical recommendation: As a starting point, use up to eight files of equal size and equal growth, and only add more in groups of four if you observe real TempDB contention.
Affinity Mask 64
- Description: Reviews the affinity mask 64 option on servers with more than 64 logical CPUs.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Automatic management | |
| Enabled | Manual affinity |
Practical recommendation: Leave it disabled unless you have a specific affinity design for large hardware and it is fully documented.
Affinity IO Mask 64
- Description: Reviews the affinity I/O mask 64 option on high CPU density servers.
- Panel type: stat
Practical recommendation: If it is used, validate that it does not overlap with other affinity masks and that it exists for a real performance reason.
Is Clustered
- Description: Indicates whether the instance is deployed on a failover cluster.
- Panel type: stat
Practical recommendation: Use this information to interpret the rest of the high-availability checks correctly.
Is HADR Enabled
- Description: Indicates whether the HADR property is enabled on the instance for Always On capabilities.
- Panel type: stat
Practical recommendation: If your design includes Availability Groups, confirm that this property is enabled across all relevant nodes.
Is Integrated Security Only
- Description: Indicates whether the instance only allows integrated authentication or also allows SQL Server authentication.
- Panel type: stat
Practical recommendation: If mixed mode is enabled, review local account exposure, password policies, and whether SQL authentication is really needed.
Checksum Backup
- Description: Indicates whether backup checksum is enabled by default to validate integrity during backup creation.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Checksum disabled | |
| Enabled | Checksum enabled |
Practical recommendation: Enabling checksum improves trust in backups, but it does not replace regular restore testing.
Core Number Not In Use
- Description: Indicates whether there are available cores that SQL Server is not using.
- Panel type: stat
- Units: Cores
Thresholds:
| Value | Color | Description |
|---|---|---|
| Other values | No issue detected | |
| > 1 | Unused cores detected |
Practical recommendation: If unused cores appear, review edition limits, affinity, VM configuration, and licensing constraints.
Core Number Assigned
- Description: Shows the actual number of cores assigned for SQL Server to use.
- Panel type: stat
- Units: Cores
Practical recommendation: Compare it with total visible cores and edition limits to detect incomplete or intentionally restricted CPU assignment.
Is Automatic Tuning Enabled
- Description: Indicates the state of Automatic Tuning and whether configuration is complete, partial, or not applicable.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| N/A | Not applicable | |
| Not Configured | Not configured | |
| Enabled | Partial configuration | |
| Almost Configured | Advanced configuration |
Practical recommendation: If you choose to use Automatic Tuning, introduce it gradually and monitor the results to validate engine-driven changes.
Collation
- Description: Shows the instance collation, which defines text comparison and sort rules.
- Panel type: stat
Practical recommendation: Review this value before consolidating databases or moving workloads between instances to avoid collation conflicts.
Is FullText Installed
- Description: Indicates whether Full-Text Search is installed on the instance.
- Panel type: stat
Practical recommendation: If applications rely on full-text search, confirm that the component is installed and maintained in every required environment.
Enabled Trace Flags
- Description: Shows the trace flags currently enabled on the instance.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| No trace flags | No active flags |
Practical recommendation: Keep a documented inventory of enabled trace flags and review it after every CU or version change.
Databases Out of AG
- Description: Checks whether any databases are outside Availability Groups on Always On servers.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| OK | No issue detected | |
| KO | Databases outside AG |
Practical recommendation: When an issue appears, separate documented design exceptions from databases that should really be protected inside the AG.
Non Enterprise Core with more than 20 Cores
- Description: Indicates whether a non-Enterprise Core instance exceeds the core threshold evaluated by the dashboard.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| No | No issue detected | |
| Yes | Review edition or licensing |
Practical recommendation: Validate this result against the exact edition, version, and licensing policy that applies in your environment.
Exceed max server memory
- Description: Indicates whether a Standard or Web edition exceeds the memory limit considered appropriate by the review.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| No | Limit not exceeded | |
| OK | Not applicable or no issue |
Practical recommendation: If an excess is detected, review the configured max server memory value and the effective limit of the edition in use.
Missing Trace Flags
- Description: Shows trace flags that the control considers recommended but are not currently enabled.
- Panel type: stat
Thresholds:
| Value | Color | Description |
|---|---|---|
| Other values | No missing flags | |
| > 1 | Missing flags detected |
Practical recommendation: Do not enable trace flags by habit; always confirm that they apply to your version and workload before turning them on.
TempDB Memory Optimized
- Description: Indicates whether memory-optimized TempDB metadata is enabled.
- Panel type: stat
Value colors:
| Value | Color | Description |
|---|---|---|
| Disabled | Option disabled | |
| Enabled | Option enabled |
Practical recommendation: Consider enabling it only when TempDB metadata contention exists and the benefit has been validated against real workload behavior.
Extra Trace Flags
- Description: Shows active trace flags that are outside the default recommended baseline.
- Panel type: stat
Practical recommendation: Document every extra flag with its reason, implementation date, and retirement criteria to avoid context-free legacy settings.
Database Configuration
This section reviews database and file configuration. It helps detect deviations from an operational baseline, both in logical database options and in physical file growth and size decisions.
Database Info
- Description: Table used to review the configuration of each database and detect deviations from common best practices.
- Panel type: table
Value colors:
| Column | Value | Color | Description |
|---|---|---|---|
| Auto Create Stats | Disabled | Automatic creation disabled | |
| Enabled | Automatic creation enabled | ||
| Auto Shrink | Disabled | Recommended state | |
| Enabled | State should be reviewed | ||
| Auto Update Stats | Disabled | Automatic update disabled | |
| Enabled | Automatic update enabled | ||
| Query Store | Disabled | Query Store disabled | |
| Enabled | Query Store enabled | ||
| N/A | Not applicable | ||
| Enabled Secondary | Enabled on secondary | ||
| Page Verify | CHECKSUM | Recommended setting | |
| Compatibility Level | Not Correct... | Level should be reviewed | |
| State | ONLINE | Operational database | |
| RESTORING | Database restoring | ||
| Owner | sa | Recommended owner | |
| Target Recovery Time | Not Correct... | Value should be reviewed | |
| N/A | Not applicable | ||
| N/A (empty) | Not applicable | ||
| CDC | Disabled | CDC disabled | |
| Enabled | CDC enabled | ||
| Log Reuse Wait | NOTHING | No relevant wait | |
| Automatic Tuning | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| N/A | Not applicable | ||
| Equal Collation Inst | NO | Different from instance | |
| YES | Matches instance | ||
| Delayed Durability | Disabled | Disabled | |
| Enabled | Enabled | ||
| Forced | Forced | ||
| Read Only | NO | Writable database | |
| YES | Read-only database | ||
| Is Legacy CE | NO | Legacy CE not in use | |
| YES | Legacy CE in use | ||
| N/A | Not applicable | ||
| Is Partitioned | NO | Not partitioned | |
| YES | Partitioned | ||
| N/A | Not applicable | ||
| Out Of AG | N/A | Not applicable | |
| Not in AG | Outside the AG | ||
| ADR | Disabled | ADR disabled | |
| Enabled | ADR enabled | ||
| N/A | Not applicable | ||
| Incremental Statistics | Enabled | Favorable usage | |
| Disabled | Option should be reviewed | ||
| N/A | Not applicable | ||
| RCSI | Disabled | Snapshot reads disabled | |
| Enabled | Snapshot reads enabled | ||
| ACCELERATED PLAN FORCING | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| BATCH MODE ADAPTIVE JOINS | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| BATCH MODE MEMORY GRANT FEEDBACK | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| TSQL SCALAR UDF INLINING | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| INTERLEAVED EXECUTION TVF | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| LIGHTWEIGHT QUERY PROFILING | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| MEMORY GRANT FEEDBACK PERCENTILE GRANT | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| MEMORY GRANT FEEDBACK PERSISTENCE | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| DOP FEEDBACK | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| OPTIMIZED SP EXECUTESQL | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| CE FEEDBACK | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| BATCH MODE ON ROWSTORE | Disabled | Feature disabled | |
| Enabled | Feature enabled | ||
| OPPO | Disabled | Feature disabled | |
| Enabled | Feature enabled |
Practical recommendation: Use this table as a per-database checklist and prioritize Auto Shrink, statistics, Query Store, Page Verify, compatibility, collation, and optimizer-related options before standardizing the environment.
Database Files
- Description: Table used to review each database file, focusing on autogrowth, maximum size, and VLF count.
- Panel type: table
Value colors:
| Column | Value | Color | Description |
|---|---|---|---|
| Autogrowth In Percentage | NO | Fixed growth | |
| YES | Percentage growth | ||
| Virtual Log Files | > 1000 | Excessive VLF count | |
| Autogrowth Value | Not Correct... | Growth increment should be reviewed | |
| Max Size In Mbs | Unlimited | Unlimited maximum size |
Practical recommendation: Avoid percentage growth and define fixed increments sized for the workload, because this gives better control over fragmentation, growth time, and VLF creation.