Server Health Report – VMEnt\SQL25 – 2026-11-13

Tuning goal: Server Health

Server: VMEnt\SQL25  |  Version: SQL Server 2025 (17.x), build 17.0.1000.7 RTM  |  Primary database context: master

Executive summary

Overall posture: high operational risk despite currently healthy memory and I/O indicators. The most urgent risks are the RTM build being 11 cumulative updates behind, 10 deadlocks within seven days, no recorded CHECKDB run for any listed database, incomplete backup coverage, and an elevated compilation rate. Current memory health is LOW across the full 63.9-minute engine history window, and no sessions are waiting on RESOURCE_SEMAPHORE.
🔴 CU outdatedInstalled 17.0.1000.7; latest CU11 build 17.0.5065.9
10 deadlocksDetected in the past 7 days
13 databasesAll reported with CHECKDB never run
11,668/secSQL compilations versus 16,048 batch requests/sec
11.5%Single-use plan ratio; 100.6 MB cache
LOWAll 256 memory-health snapshots

Top priorities

  1. Patch SQL Server 2025. Lifecycle is Green through 2031-01-06, but CU currency is Red — Outdated. Install CU11, build 17.0.5065.9, after normal testing and change control.
  2. Run and schedule DBCC CHECKDB. Every one of the 13 reported databases has “Never Run” in the supplied evidence. Treat this as a critical integrity-maintenance gap.
  3. Analyze the deadlock burst. Ten deadlocks occurred in seven days, including five within approximately 10 seconds on 2026-11-13 12:22:48–12:22:57 UTC. Use the Fix Deadlocks goal; the supplied XML is truncated and does not expose the involved database objects.
  4. Correct backup and recovery gaps. No full backup is recorded for master, model, msdb, BenchDeploy, RubricArchive, TunerBench, IndexTunerChallenge, or several benchmark databases. FULL-recovery databases also have no recorded log backups.
  5. Reduce compilation and plan-cache waste. SQL Compilations/sec is 11,668 against 16,048 Batch Requests/sec, while SQL Re-Compilations/sec is 1,713. Enable optimize for ad hoc workloads after validating workload behavior; investigate high compilation sources.
  6. Apply the highest-value indexes. Missing-index candidates affect RetailOps, SalesDemo, and ForumDemo. Candidate sizing is estimated because column widths and existing index definitions were not supplied.

Scope and confidence

The evidence covers approximately 2.5 days since the 2026-11-11 03:22:27 server restart. Plan cache, index usage, missing-index, and operational statistics are therefore not long-term history.

AreaAssessmentConfidence
CU and lifecycleLifecycle green; CU currency red and outdated.High
MemoryEngine memory health LOW for 2026-11-13 14:40:46–15:44:38, 63.9 minutes; this history holds at most about an hour and resets when the engine restarts.High for the reported window
DeadlocksTen events are confirmed, but the supplied XML is truncated.High for count; low for root cause
IndexesThree high-value candidates are confirmed; physical size estimates are ranges.Medium
BackupsCoverage findings are based on available msdb backup history.High for recorded history; external backup repositories are not represented

Detailed prioritized recommendations

  1. Patch the engine to the latest SQL Server 2025 CU — Critical, high confidence.

    Installed build is 17.0.1000.7; latest published CU is CU11, build 17.0.5065.9. The supplied status is Outdated, with CU age 0 months and Active CU servicing. “Outdated” means two or more CUs behind. Follow the supplied recommendation: upgrade to the latest CU for stability and security. Lifecycle is a separate dimension: SQL Server 2025 mainstream support ends 2031-01-06 and extended support ends 2036-01-06, so lifecycle is Green.

  2. Establish integrity maintenance immediately — Critical, high confidence.

    CHECKDB has never run for IndexTunerChallenge, BenchDeploy, IndexTunerChallenge_bench_challenge, TunerBench, SQLStormOltp, SQLStormOltp_bench_sqlstorm-oltp, RubricArchive, tpch10, tpch10_bench_tpch10-realistic, ForumDemo, ShopDemo, SalesDemo, or RetailOps. Run CHECKDB during controlled maintenance, then schedule it at an interval appropriate to database size and recovery objectives. No database size or CHECKDB duration was supplied, so a safe duration estimate cannot be made.

  3. Investigate deadlocks using the Fix Deadlocks goal — Critical, high confidence for occurrence.

    Ten deadlocks were reported in the past seven days. A burst of five events occurred on 2026-11-13 between 12:22:48 and 12:22:57 UTC, and five earlier events occurred on 2026-11-10 between 20:59:12 and 20:59:48 UTC. Database names, statements, lock resources, and the complete graphs are not available because the input truncates each XML document. Do not infer the victim query or involved database from the truncated identifiers.

  4. Repair backup and recovery coverage — Critical, high confidence.

    No full backup is recorded for master, model, msdb, IndexTunerChallenge, BenchDeploy, IndexTunerChallenge_bench_challenge, TunerBench, SQLStormOltp_bench_sqlstorm-oltp, RubricArchive, or tpch10_bench_tpch10-realistic. BenchDeploy, model, RubricArchive, and TunerBench are in FULL recovery with no recorded log backups. SQLStormOltp, ForumDemo, ShopDemo, SalesDemo, and RetailOps have full backups recorded approximately three days before the report; tpch10 has one recorded approximately 14 days before the report. Backup destinations, retention requirements, and RPO/RTO were not supplied, so deterministic BACKUP commands cannot be generated safely.

  5. Reduce compilation and plan-cache pollution — High priority, medium confidence.

    SQL Compilations/sec is 11,668 against 16,048 Batch Requests/sec, approximately 72.7% when calculated from the supplied counters. SQL Re-Compilations/sec is 1,713. The plan cache contains 2,694 plans, including 309 single-use plans occupying 100.6 MB of a 540.6 MB cache. Enable optimize for ad hoc workloads after testing. Review dynamic SQL, literal-heavy application SQL, and unnecessary recompiles.

    No sp_executesql batch has more than one cached plan per database and SET-options combination. Therefore, do not enable OPTIMIZED_SP_EXECUTESQL based on this evidence: the required duplicate-plan compilation-storm evidence is absent, and RESOURCE_SEMAPHORE_QUERY_COMPILE waits are zero.

  6. Implement and validate high-value missing indexes — High priority, medium confidence.

    Run a comprehensive Index Tuning analysis for RetailOps, SalesDemo, and ForumDemo before production deployment. Candidate storage estimates are ranges because the input does not provide key-column widths, included-column widths, clustering-key width, compression, fill factor, or existing index definitions.

    Database and tableCandidateEvidenceEstimated new size
    RetailOps.dbo.CustomersNonclustered key Region100,000 rows; table data 12.2 MB; existing nonclustered indexes 11.0 MB; 50,002 seeks/scans; 72.5% impactEstimated 1–4 MB. Based on 100,000 rows and unknown key/row locator overhead; no included columns.
    SalesDemo.dbo.OrdersNonclustered key AccountId, Status50,000 rows; 23,459 seeks/scans; 94.6% impactEstimated 1–3 MB. Based on 50,000 rows and unknown key/row locator overhead.
    ForumDemo.dbo.VotesNonclustered key PostId, VoteTypeId945,284 rows; table data 12.1 MB; existing nonclustered indexes 23.9 MB; 4 seeks/scans; 95.6% impactEstimated 8–24 MB. Based on 945,284 rows and unknown key/row locator overhead.

    Estimated aggregate storage: approximately 10–31 MB of new index storage. This is an estimate, not a reported size. No indexes are recommended for dropping or modification, so estimated reclaimed space is 0 MB and net change is approximately +10–31 MB.

  7. Correct database statistics settings — High priority, high confidence.

    ForumDemo and SQLStormOltp have both AUTO_CREATE_STATS and AUTO_UPDATE_STATS disabled. tpch10 also has both disabled. This can contribute to poor estimates and plan instability. Enable them after workload testing. Auto-update statistics asynchronous behavior is not enabled in these databases; enable it where reduced synchronous statistics waits are desirable and operational testing supports it.

  8. Review Query Store read-only databases — Medium priority, high confidence.

    Query Store is configured as READ_WRITE but is actually READ_ONLY for IndexTunerChallenge_bench_challenge, SQLStormOltp_bench_sqlstorm-oltp, and tpch10_bench_tpch10-realistic because each database is read-only. CE feedback and DOP feedback cannot run there because Query Store must be READ_WRITE. Correct the database read-only state only if that state is unintended; otherwise this is expected behavior.

    For the other databases, CE feedback can run where compatibility is 160 or higher, CE_FEEDBACK is ON, and Query Store is READ_WRITE. CE feedback for expressions is also reported ON. DOP feedback is ON and can run under the same Query Store and compatibility prerequisites. It lowers DOP only after observing repeating parallel queries, never above MAXDOP and never below 2. The server has four logical CPUs, MAXDOP 4, and cost threshold 5; these settings should be validated against workload behavior rather than changed solely because feedback is available.

  9. Review compatibility level 160 databases for OPPO — Low priority, medium confidence.

    OPPO is not active in ForumDemo, SQLStormOltp, SQLStormOltp_bench_sqlstorm-oltp, tpch10, and tpch10_bench_tpch10-realistic because they use compatibility level 160 rather than 170. Do not raise compatibility solely to obtain OPPO. If a compatibility change is justified, test the workload at level 170 and use Query Store to identify and correct regressions. OPPO is already available on SQL Server 2025 where compatibility is 170 and the database setting is ON.

  10. Review server configuration and file growth — Medium priority, high confidence.

    MAXDOP 4 is aligned with the four logical CPUs as a starting point. Four equally sized tempdb data files of 72 MB each with 64 MB fixed growth meet the one-file-per-logical-CPU guidance for four CPUs. Tempdb configuration is therefore not a finding.

    Four log files use percent growth: ForumDemo 17.8 MB at 10%, IndexTunerChallenge 2,553.6 MB at 10%, SQLStormOltp 0.8 MB at 10%, and tpch10 0.8 MB at 10%. Replace percent growth with fixed increments sized to observed workload, after measuring growth frequency. VLF counts are not excessive: the highest is 82 in IndexTunerChallenge, below the approximately 1,000-VLF concern threshold. All reported truncation holdups are NOTHING or none.

    Instant File Initialization is enabled for the SQL25 service account. This benefits data-file growth and restores; log files do not use IFI.

  11. Address query patterns that are likely expensive — Medium priority, medium confidence.

    The one-execution ForumDemo query hash E91FED510A31399B consumed 9,971,678 microseconds CPU, 507,941 logical reads, and a maximum 10,376 KB grant. It uses functions such as YEAR and CONVERT on date columns and nested subqueries. Rewrite to sargable date ranges and validate with Query Store and an actual execution plan.

    The SalesDemo report query hash 52DA768E4A0B830A used 30,664 KB maximum grant, 9,187 logical reads, and 213,398 microseconds average CPU over 20 executions. Its DATEDIFF predicate should be reviewed for sargability. The input shows no current RESOURCE_SEMAPHORE waiters and no spills, so this is optimization opportunity rather than confirmed current memory pressure.

    The customer query using UPPER(c.Email) may prevent a direct seek unless a suitable computed/indexed design or collation strategy exists. Validate before changing schema.

Performance and resource health

SignalEvidenceInterpretation
WaitsQDS_PERSIST_TASK_MAIN_LOOP_SLEEP: 50.18%; QDS_ASYNC_QUEUE: 48.74%These are predominantly Query Store background waits with low signal waits, not evidence of CPU saturation. Review Query Store operation only if persistence latency or storage behavior is observed.
I/OHighest listed latency: ForumDemo data file overall 2 ms; most files 0–1 msReported storage latency is healthy for this sample. The sample covers only 2.5 days.
Memory healthAll 256 snapshots LOW from 14:40:46 to 15:44:38, 63.9 minutes; allocation potential 21,236–21,237 MB; reclaimable cache 973 MBNo engine-identified memory pressure. This is at most about an hour of history and resets at restart; it is not long-term evidence.
ClerksSQL buffer pool 973.66 MB; SQL plans 306.17 MB; bound trees 220.71 MBSQL buffer pool is normally a leading clerk. No lock-manager or query-grant clerk was reported as disproportionately large.
Lock manager2.42 MBNot near the 20–25% critical threshold; no immediate lock-memory finding.
Memory grantsNo RESOURCE_SEMAPHORE waiters; observed request 1,024 KBNo current grant-wait pressure. Large historical grants still warrant query tuning.

On this evidence, do not report memory pressure from point-in-time sections. If later RESOURCE_SEMAPHORE waiters appear, prioritize large-grant queries and consider Resource Governor controls. The emergency command DBCC FREESYSTEMCACHE ('Lock Manager : Node 0') is not recommended now because lock-manager memory is only 2.42 MB and the supplied evidence does not show excessive lock memory.

Configuration optimizations

  • Enable optimize for ad hoc workloads: justified by 309 single-use plans consuming 100.6 MB and an 11.5% single-use ratio. This is a workload-level mitigation, not a substitute for parameterization and application query review.
  • Enable automatic statistics creation and update: required for ForumDemo, SQLStormOltp, and tpch10 based on the reported settings.
  • Fixed file growth: replace the four reported 10% log-growth settings with fixed increments based on observed growth volume.
  • Do not change tempdb file count: four data files for four logical CPUs are consistent with the stated guidance, and all four are equally sized at 72 MB with 64 MB growth.
  • Do not change MAXDOP automatically: MAXDOP 4 is already a reasonable starting point for four logical CPUs. Review actual runtime behavior and parallel waits first.
  • Backup defaults: backup checksum default and backup compression default are both 0. Enable them if backup CPU, storage, and compatibility testing support the change.

Index optimization opportunities

The three candidates below should be validated with the Index Tuning goal for each affected database. Missing-index statistics are only 2.5 days old and may be workload-biased.

PriorityDatabaseRecommended designStorage impact
1RetailOpsdbo.Customers(Region)New estimate 1–4 MB; 100,000 rows; 0 MB reclaimed; net +1–4 MB.
2SalesDemodbo.Orders(AccountId, Status)New estimate 1–3 MB; 50,000 rows; 0 MB reclaimed; net +1–3 MB.
3ForumDemodbo.Votes(PostId, VoteTypeId)New estimate 8–24 MB; 945,284 rows; 0 MB reclaimed; net +8–24 MB.

These estimates use the supplied row counts and assume a rowstore nonclustered index with no included columns, but column widths, clustering keys, compression, fill factor, page overhead, and existing definitions are missing. The combined estimated net storage change is approximately +10–31 MB.

Query Store and intelligent query processing

  • Query Store is READ_WRITE for most databases, with storage utilization between 0.00% and 0.40% of the 1,000 MB limits. This is healthy capacity-wise.
  • IndexTunerChallenge, SalesDemo, and ShopDemo use Capture Mode ALL. Consider AUTO capture after confirming that important workload coverage remains adequate; ALL can increase capture volume and plan churn.
  • CE feedback is active where the reported prerequisites are met. It cannot run in the three read-only benchmark databases. CE feedback for expressions is explicitly reported ON; no claim is made for databases where it is not reported.
  • DOP feedback is active where the reported prerequisites are met. It is not a replacement for correct MAXDOP and cost threshold settings and does not recompile plans.
  • OPPO is not active in the five compatibility-level-160 databases. Review, but do not automatically change, their compatibility levels.
  • There is no evidence supporting OPTIMIZED_SP_EXECUTESQL: duplicate cached sp_executesql plans are absent and compile-memory waits are zero.

Database integrity and recovery

CHECKDB

All 13 reported databases show “Never Run.” Run CHECKDB for every listed database, beginning with production-relevant databases and databases that lack recent integrity evidence. Use an established maintenance window and ensure sufficient tempdb and storage capacity. CHECKDB results should be retained and reviewed for allocation, consistency, and repair recommendations; do not use repair options without a validated recovery plan.

Backups

Recorded coverage is incomplete. Full backups are not recorded for multiple user and system databases. FULL-recovery databases model, BenchDeploy, TunerBench, and RubricArchive have no recorded log backups. The input does not include backup destinations or retention policies, so backup scheduling must be implemented according to the organization's RPO/RTO rather than a generated destination-specific command.

Security enhancements

  • Transport encryption: No unencrypted TCP/IP or named-pipe connections were reported. Do not flag the four unencrypted shared-memory connections; they are local to the server and do not cross the network. Nine of 13 connections were encrypted, while the remaining four were shared memory. No encryption change is required based on this evidence.
  • Privileged access: Two sysadmin members are reported: sa and VMEnt\astsa. This is a reasonable count, but audit business need, enforce a strong unique password for sa, and disable it if it is not required. Do not remove access without validating emergency and application dependencies.
  • Surface area: External REST endpoint is enabled, remote access is enabled, and filesystem enumeration is enabled. Confirm each is required and restrict access through least privilege and network controls. No change is recommended solely from the setting values without usage evidence.

Concurrency, locking, and deadlocks

Ten deadlocks are confirmed, but the supplied deadlock XML is truncated. Consequently, a reliable blocking diagram cannot be created: session relationships, lock modes, database names, resources, and statements are missing. Run the Fix Deadlocks goal against the complete deadlock graphs.

Only six sessions are shown with one database lock each in the point-in-time lock summary, and no blocking chain is supplied. Do not infer that this snapshot explains the recent deadlocks.

Potentially relevant query patterns include updates that aggregate Orders, reports joining Orders and OrderLines, and queries with large scans. Validate actual lock acquisition order and transaction duration before applying hints. Avoid using ROWLOCK or disabling page and row locks as a generic remedy because those patterns can increase lock counts and lock-manager memory.

Operational best practices

  • Patch from RTM to CU11 under change control and retain the pre-patch build record.
  • Schedule CHECKDB and retain results for every database.
  • Define full, differential, and log backup schedules from RPO/RTO; verify restores rather than relying only on msdb history.
  • Review log-growth events and convert percent growth to fixed increments, especially IndexTunerChallenge at 2,553.6 MB with 10% growth.
  • Track deadlocks, compilation rate, recompilations, Query Store capture volume, and actual runtime DOP after changes.
  • Remember that the supplied plan cache and index statistics cover only the 2.5-day uptime window.
  • Memory health is not a long-term history: the reported window is 63.9 minutes at most and resets after restart.

Scripts

Enable ad hoc plan stubs to reduce single-use plan-cache waste

USE [master];
GO
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'show advanced options', 0;
RECONFIGURE;
GO

Enable automatic statistics creation and update for databases where both are disabled

USE [ForumDemo];
GO
ALTER DATABASE [ForumDemo] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [ForumDemo] SET AUTO_UPDATE_STATISTICS ON;
GO

USE [SQLStormOltp];
GO
ALTER DATABASE [SQLStormOltp] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormOltp] SET AUTO_UPDATE_STATISTICS ON;
GO

USE [tpch10];
GO
ALTER DATABASE [tpch10] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [tpch10] SET AUTO_UPDATE_STATISTICS ON;
GO

Create the validated RetailOps missing-index candidate

USE [RetailOps];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Customers')
      AND name = N'IX_Customers_Region'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Customers_Region]
    ON [dbo].[Customers] ([Region])
    WITH (ONLINE = ON);
END;
GO

Create the validated SalesDemo missing-index candidate

USE [SalesDemo];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Orders')
      AND name = N'IX_Orders_AccountId_Status'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Orders_AccountId_Status]
    ON [dbo].[Orders] ([AccountId], [Status])
    WITH (ONLINE = ON);
END;
GO

Create the validated ForumDemo missing-index candidate

USE [ForumDemo];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Votes')
      AND name = N'IX_Votes_PostId_VoteTypeId'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId]
    ON [dbo].[Votes] ([PostId], [VoteTypeId])
    WITH (ONLINE = ON);
END;
GO

Run CHECKDB for all databases reported without a prior CHECKDB run

USE [IndexTunerChallenge];
GO
DBCC CHECKDB ([IndexTunerChallenge]) WITH NO_INFOMSGS;
GO

USE [BenchDeploy];
GO
DBCC CHECKDB ([BenchDeploy]) WITH NO_INFOMSGS;
GO

USE [IndexTunerChallenge_bench_challenge];
GO
DBCC CHECKDB ([IndexTunerChallenge_bench_challenge]) WITH NO_INFOMSGS;
GO

USE [TunerBench];
GO
DBCC CHECKDB ([TunerBench]) WITH NO_INFOMSGS;
GO

USE [SQLStormOltp];
GO
DBCC CHECKDB ([SQLStormOltp]) WITH NO_INFOMSGS;
GO

USE [SQLStormOltp_bench_sqlstorm-oltp];
GO
DBCC CHECKDB ([SQLStormOltp_bench_sqlstorm-oltp]) WITH NO_INFOMSGS;
GO

USE [RubricArchive];
GO
DBCC CHECKDB ([RubricArchive]) WITH NO_INFOMSGS;
GO

USE [tpch10];
GO
DBCC CHECKDB ([tpch10]) WITH NO_INFOMSGS;
GO

USE [tpch10_bench_tpch10-realistic];
GO
DBCC CHECKDB ([tpch10_bench_tpch10-realistic]) WITH NO_INFOMSGS;
GO

USE [ForumDemo];
GO
DBCC CHECKDB ([ForumDemo]) WITH NO_INFOMSGS;
GO

USE [ShopDemo];
GO
DBCC CHECKDB ([ShopDemo]) WITH NO_INFOMSGS;
GO

USE [SalesDemo];
GO
DBCC CHECKDB ([SalesDemo]) WITH NO_INFOMSGS;
GO

USE [RetailOps];
GO
DBCC CHECKDB ([RetailOps]) WITH NO_INFOMSGS;
GO

Enable backup checksums and compression as server defaults

USE [master];
GO
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'backup checksum default', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'backup compression default', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'show advanced options', 0;
RECONFIGURE;
GO