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
RESOURCE_SEMAPHORE.
Top priorities
- 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.
- 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.
- 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.
- 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.
- 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 workloadsafter validating workload behavior; investigate high compilation sources. - 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.
| Area | Assessment | Confidence |
|---|---|---|
| CU and lifecycle | Lifecycle green; CU currency red and outdated. | High |
| Memory | Engine 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 |
| Deadlocks | Ten events are confirmed, but the supplied XML is truncated. | High for count; low for root cause |
| Indexes | Three high-value candidates are confirmed; physical size estimates are ranges. | Medium |
| Backups | Coverage findings are based on available msdb backup history. | High for recorded history; external backup repositories are not represented |
Detailed prioritized recommendations
-
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. -
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.
-
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.
-
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.
-
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 workloadsafter 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_EXECUTESQLbased on this evidence: the required duplicate-plan compilation-storm evidence is absent, andRESOURCE_SEMAPHORE_QUERY_COMPILEwaits are zero. -
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 table Candidate Evidence Estimated new size RetailOps.dbo.Customers Nonclustered key Region100,000 rows; table data 12.2 MB; existing nonclustered indexes 11.0 MB; 50,002 seeks/scans; 72.5% impact Estimated 1–4 MB. Based on 100,000 rows and unknown key/row locator overhead; no included columns. SalesDemo.dbo.Orders Nonclustered key AccountId, Status50,000 rows; 23,459 seeks/scans; 94.6% impact Estimated 1–3 MB. Based on 50,000 rows and unknown key/row locator overhead. ForumDemo.dbo.Votes Nonclustered key PostId, VoteTypeId945,284 rows; table data 12.1 MB; existing nonclustered indexes 23.9 MB; 4 seeks/scans; 95.6% impact Estimated 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.
-
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.
-
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.
-
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.
-
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.
-
Address query patterns that are likely expensive — Medium priority, medium confidence.
The one-execution ForumDemo query hash
E91FED510A31399Bconsumed 9,971,678 microseconds CPU, 507,941 logical reads, and a maximum 10,376 KB grant. It uses functions such asYEARandCONVERTon 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
52DA768E4A0B830Aused 30,664 KB maximum grant, 9,187 logical reads, and 213,398 microseconds average CPU over 20 executions. ItsDATEDIFFpredicate 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
| Signal | Evidence | Interpretation |
|---|---|---|
| Waits | QDS_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/O | Highest listed latency: ForumDemo data file overall 2 ms; most files 0–1 ms | Reported storage latency is healthy for this sample. The sample covers only 2.5 days. |
| Memory health | All 256 snapshots LOW from 14:40:46 to 15:44:38, 63.9 minutes; allocation potential 21,236–21,237 MB; reclaimable cache 973 MB | No engine-identified memory pressure. This is at most about an hour of history and resets at restart; it is not long-term evidence. |
| Clerks | SQL buffer pool 973.66 MB; SQL plans 306.17 MB; bound trees 220.71 MB | SQL buffer pool is normally a leading clerk. No lock-manager or query-grant clerk was reported as disproportionately large. |
| Lock manager | 2.42 MB | Not near the 20–25% critical threshold; no immediate lock-memory finding. |
| Memory grants | No RESOURCE_SEMAPHORE waiters; observed request 1,024 KB | No 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.
| Priority | Database | Recommended design | Storage impact |
|---|---|---|---|
| 1 | RetailOps | dbo.Customers(Region) | New estimate 1–4 MB; 100,000 rows; 0 MB reclaimed; net +1–4 MB. |
| 2 | SalesDemo | dbo.Orders(AccountId, Status) | New estimate 1–3 MB; 50,000 rows; 0 MB reclaimed; net +1–3 MB. |
| 3 | ForumDemo | dbo.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:
saandVMEnt\astsa. This is a reasonable count, but audit business need, enforce a strong unique password forsa, 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