Memory best practices
Issue: The minimum value establishes a floor that SQL Server will not release memory below once it has been acquired. The max server memory setting controls the upper limit on memory SQL Server can allocate to its buffer pool and most internal caches. By default, this value is 2,147,483,647 MB (effectively unlimited), meaning SQL Server will consume as much memory as it needs and rarely release it back to Windows.
Problem: When SQL Server is allowed to grow without bounds, it can starve the operating system and other processes of memory. Windows may then resort to paging, where memory pages are written to disk, causing severe performance degradation across the entire server. SQL Server itself may experience instability if Windows signals memory pressure too aggressively. Even on dedicated SQL Server hosts, the operating system, antivirus, monitoring agents, and backup tools all need memory reserved for them.
When minimum and maximum are set to the same value, SQL Server can no longer dynamically free or acquire memory for the buffer pool once the limit is reached. This eliminates the memory manager's ability to respond to pressure from the operating system or other applications, which can cause plan cache instability, problems with query memory grants, and unpredictable behavior under load.
When minimum is set above maximum, the configuration is invalid and SQL Server may refuse to start or behave unexpectedly.
Learn More...Power plan
Issue: Windows Server's default power plan is "Balanced," which dynamically throttles CPU clock speed down during lower utilization to conserve energy and ramps it back up when demand increases. A processor rated at 3.0 GHz may run at 1.2 GHz or lower when the system appears idle, with the CPU only ramping up after load is sustained.
Problem: The CPU ramp-up is not instantaneous, and SQL Server's typical OLTP workload with short, bursty queries often completes at throttled clock speeds before the processor ever reaches full speed. This causes measurable performance loss, commonly 20-25%, and shows up in wait statistics as elevated SOS_SCHEDULER_YIELD and high signal waits that look like CPU pressure but are actually a configuration problem. Queries take longer, throughput suffers, and troubleshooting efforts often get misdirected toward query tuning when the real fix is at the host level.
Learn More...Database snapshot
Issue: A database snapshot is a read-only, point-in-time view of a SQL Server database, implemented via NTFS sparse files that grow as original pages are modified in the source database (copy-on-write). Snapshots are useful for reporting, testing, or quick reverts, but they are often created ad-hoc and left behind indefinitely, accumulating on production instances.
Problem: Every write to the source database triggers a copy-on-write to each snapshot's sparse file, adding I/O overhead that grows significantly with each additional snapshot. Performance can degrade sharply with more than one or two active snapshots on a busy database.
Snapshots also consume increasing disk space over time; if the volume runs out of space, the snapshot goes suspect and becomes unusable. Also, snapshots are not backups; they depend on the source database being online, live on the same storage, and prevent certain operations like restoring the source from backup while they exist.
Learn More...Evaluation Edition
Issue: SQL Server Evaluation Edition is a free, fully-featured version equivalent to Enterprise Edition, intended for testing and evaluation purposes. It runs for 180 days from installation, after which the SQL Server service shuts down and will not restart. Evaluation Edition is often left in place unintentionally when a server is built quickly for a project and the license upgrade is never completed.
Problem: When the 180-day trial expires, SQL Server stops and all databases hosted on the instance become inaccessible — causing an unplanned outage that can catch teams by surprise if no one is tracking the expiration date. Evaluation Edition is also not licensed for production use, creating a compliance risk if discovered during an audit. Because an instance found running Evaluation Edition is essentially on a countdown to failure, the longer it persists the greater the risk of an unexpected outage.
Learn More...Database corruption
Issue: Corruption has been detected in a SQL Server database, typically surfaced through DBCC CHECKDB errors, 823/824/825 I/O errors, or entries in the suspect_pages table in msdb. Corruption almost always originates below SQL Server - in the storage subsystem, disk controllers, drivers, or memory - rather than in SQL Server itself.
Problem: Corrupted pages can cause query failures, incorrect results, or complete loss of access to tables and indexes. Left unaddressed, corruption spreads as affected pages are read and rewritten, and it propagates into backups, shrinking the window in which a clean restore is possible. Some forms of corruption also prevent transaction log backups from succeeding, breaking your recovery chain entirely.
Learn More...Missing integrity checks
Issue: DBCC CHECKDB is the primary tool for verifying the logical and physical integrity of a SQL Server database, detecting corruption from hardware failures, I/O errors, and other storage-level problems. This issue occurs when a database has not had CHECKDB run recently — or ever — meaning no one has verified that its data is actually intact.
Problem: Corruption can occur silently and go undetected until a user hits a bad page or a restore fails. Without regular CHECKDB runs, corruption can spread into backups, so by the time it's discovered you may have no clean recovery point. Running CHECKDB is the only reliable way to confirm your database is healthy and your backups are worth keeping.
Learn More...Alerts
Issue: SQL Server Agent can alert on critical errors, but these alerts are not configured by default. Severity 19-25 covers fatal errors ranging from resource failures to hardware issues and data corruption. Errors 823 (I/O request failure), 824 (logical consistency error), and 825 (read retry required) specifically indicate storage subsystem problems.
Problem: These errors are the earliest warning signs of corruption and hardware failure. Error 825 especially — a read that only succeeded on retry — gives you a window to act before full corruption hits. Without alerts, these events sit quietly in the error log, often unnoticed until corruption has spread into backups.
Learn More...Database page verification
Issue: SQL Server offers three page verification options — NONE, TORN_PAGE_DETECTION, and CHECKSUM — that determine how the database engine validates the integrity of data pages read from and written to disk. CHECKSUM, introduced in SQL Server 2005 and the default ever since, calculates a checksum across the entire page when it is written and re-verifies it on read. TORN_PAGE_DETECTION is an older, less reliable method that only inspects a small number of bits per sector, and NONE performs no verification at all.
This issue arises when one or more databases are configured with PAGE_VERIFY set to NONE or TORN_PAGE_DETECTION, commonly found on databases that were originally created on SQL Server 2000 or earlier and later upgraded, since the upgrade process does not automatically change this setting.
Problem: Without CHECKSUM, SQL Server cannot reliably detect many forms of I/O-related data corruption caused by faulty storage, controllers, drivers, or other hardware issues. TORN_PAGE_DETECTION only catches corruption that occurs mid-write across sector boundaries and misses corruption within a sector, while NONE provides no protection whatsoever.
The real danger is that corruption can silently accumulate and propagate into backups, meaning by the time a problem surfaces — often during a restore, a DBCC CHECKDB, or an application failure — you may no longer have a clean backup to recover from.
Learn More...AlwaysOn Health extended event
Issue: The AlwaysOn_health Extended Events (XE) session is a built-in SQL Server diagnostic session that is automatically created on any instance hosting an Always On Availability Group. It captures critical events related to availability group health, including replica state changes, failover validations, lease expirations, connectivity errors, and redo blocking.
When this session is in a stopped state, SQL Server is no longer recording these diagnostic events to its rollover trace files. This can occur if the session was manually stopped, if it was created without the STARTUP_STATE=ON property, or if it failed to start after a service restart or manual intervention.
Problem: Without the AlwaysOn_health session running, you lose the primary built-in source of historical diagnostic data for troubleshooting Availability Group issues. If a failover, synchronization problem, connectivity disruption, or lease timeout occurs, you will have no retrospective record of what happened, making root-cause analysis extremely difficult or impossible.
The Availability Group Dashboard in SQL Server Management Studio also relies on this session's data to display health information, so stopping it degrades your monitoring visibility. In environments where high availability is critical, this is effectively flying blind during the exact moments when diagnostics matter most.
Learn More...Suspect status
Issue: A SQL Server database enters "suspect" status when the database engine attempts to recover it during startup but the recovery process fails to complete successfully. In this state, the database is marked as unavailable and cannot be accessed by users or applications. Common triggers include corrupted data or log files, missing files, hardware or disk failures, an improper shutdown of the SQL Server service, or insufficient disk space during recovery.
Problem: This is all kinds of bad. A suspect database is completely inaccessible, meaning all connected applications, reports, and business processes that rely on it will fail. Beyond the immediate outage, a suspect status often signals underlying data corruption or storage-level issues that could lead to permanent data loss if not addressed carefully. The longer the database remains in this state, the greater the risk that additional recovery attempts or well-intentioned troubleshooting actions may cause further damage.
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: