How a Health Check Saved an NHS Trust 40% in Licensing Costs

Public sector IT teams operate under constant pressure: stretched budgets, legacy infrastructure, compliance obligations, and the kind of scrutiny that private sector organisations rarely face. SQL Server estates in NHS Trusts reflect this reality — grown organically over years, rarely rationalised, and carrying licensing costs that no one fully understands.

This is the account of one such engagement. An NHS Trust facing a significant SQL Server licensing renewal, an estate no one had ever properly mapped, and a pressing question: are we paying for what we actually need?

The answer was no. Substantially not.

The Situation

The Trust had been running SQL Server for over a decade. Through a series of mergers, system migrations, and infrastructure projects, the estate had grown to encompass dozens of instances spread across physical hosts, virtual machines, and — more recently — Azure.

Licensing had been handled on a per-renewal basis: the incumbent supplier quoted, the IT team approved, the procurement team processed. No one had asked whether the current configuration matched the current workload.

With a major SQL Server Enterprise licence renewal approaching, the IT director had a simple question: are we paying for the right thing?

To answer that, they needed a health check.

What the Health Check Covered

A structured SQL Server health check is not a spot check. It is a systematic examination of every dimension of the estate:

• Instance inventory: every SQL Server instance, version, and edition currently running
• Licence alignment: edition vs actual workload requirements — is Enterprise justified, or would Standard suffice?
• Configuration review: are key settings (memory, parallelism, tempdb, max server memory) appropriate for each workload?
• Wait statistics analysis: what is SQL Server actually waiting for? Where is the time going?
• Top resource consumers: which queries are driving CPU, I/O, and memory consumption?
• Index analysis: missing indexes causing full scans, unused indexes consuming resources
• Maintenance health: are backups running and verified? Are statistics and indexes being maintained?
• Security posture: SA accounts, orphaned logins, unpatched instances

For this Trust, the assessment covered 34 SQL Server instances across 12 physical and virtual hosts, with 3 Azure SQL instances included.

What We Found

Finding 1: Enterprise Edition Running Standard-Edition Workloads

The most significant finding: 14 of the 22 on-premises SQL Server instances were licenced at Enterprise Edition — despite running workloads that had no requirement for Enterprise-only features.

Enterprise Edition costs roughly 4× more than Standard Edition per core. For on-premises SQL Server, where licences are purchased per physical core (with software assurance), this represented an enormous over-investment.

The workloads on the 14 instances were cross-referenced against the Enterprise-only feature list: no Always On Availability Groups beyond 2 secondaries, no in-memory OLTP, no columnstore indexes on OLTP tables, no data compression beyond what Standard supports since SQL Server 2016.

Conservative savings on licence renewal: 32% of the on-premises licence cost. With some consolidation (see below), this rose to 40%.

Finding 2: Consolidation Opportunities

Several instances were running on separate VMs with minimal workloads — in some cases, single-database instances with fewer than 10 active connections and sub-GB databases.

These had accumulated over years: a system migration here, a new application there, each deployed with its own SQL Server instance as the path of least resistance. Nobody had ever asked whether they could be consolidated.

By mapping instance resource utilisation against host capacity, we identified 8 instances that could be safely consolidated onto 3 existing hosts without performance impact — eliminating 8 licence footprints entirely.

Combined with the Enterprise-to-Standard downgrade, the total licensing cost reduction on renewal reached 40%.

Finding 3: Missing Indexes on Clinical Systems

The performance findings were, in many ways, more operationally significant than the licensing findings.

Two high-criticality clinical systems — a patient administration system and a clinical correspondence workflow — were running with substantial missing indexes. The query workload analysis showed:

• 4 queries on the patient admin system performing full table scans on a 180-million-row table
• The clinical correspondence workflow running a nightly batch that was taking 4.5 hours and regularly bleeding into the working day

Both were fixable without schema changes or application modifications — index additions only.

After index creation:

• The patient admin queries dropped from 8–12 seconds to under 400ms
• The nightly batch completed in 47 minutes

Finding 4: Configuration Drift

Across the estate, default SQL Server configurations had been left in place for years. Notable issues:

• Max Server Memory not set on 9 instances — SQL Server competing with the OS for memory
• Max Degree of Parallelism at default (0 — unlimited) on OLTP workloads, causing excessive parallel query plans and CPU contention
• TempDB on single-file configurations across multiple instances — well-documented as a performance bottleneck under concurrent workload
• Backup verification disabled — backups running, but restores never tested

None of these required new hardware. All were configuration changes that could be applied during a maintenance window.

Finding 5: Unpatched Instances

7 instances were running SQL Server versions more than 2 years behind the current cumulative update. 2 instances were on SQL Server 2012 — end of extended support in July 2022, meaning no security patches since that date.

In an NHS environment, this represents direct compliance and cybersecurity exposure. The two 2012 instances were flagged as immediate action items and scheduled for migration within 30 days.

The Remediation Plan

The health check output was a prioritised remediation plan organised into four tiers:

| Priority | Category | Action | Effort | |---|---|---|---| | P1 — Immediate | Security | Patch unpatched instances; migrate 2012 instances | 2 weeks | | P1 — Immediate | Performance | Apply missing indexes to clinical systems | 1 day | | P2 — Pre-renewal | Licensing | Downgrade 14 Enterprise → Standard; consolidate 8 instances | 6 weeks | | P3 — Ongoing | Configuration | Apply recommended configuration across estate | 4 weeks | | P4 — Maintenance | Operations | Implement automated maintenance (Ola Hallengren scripts), backup verification | 2 weeks |

The P1 actions were completed within the first two weeks. The P2 licensing work was timed to land before the renewal date — delivering the cost saving on the current renewal cycle rather than waiting a further year.

The Outcome

Licensing cost reduction: 40% — achieved entirely through right-sizing and consolidation, with no reduction in capability for workloads that needed Enterprise features.

Performance improvements:

• Patient administration queries: 8–12 seconds → under 400ms
• Clinical correspondence batch: 4.5 hours → 47 minutes

Operational improvements:

• Backup verification in place across all instances
• Automated index and statistics maintenance running nightly
• Configuration standards documented and applied
• Full instance inventory maintained going forward

Compliance:

• All instances on supported SQL Server versions with current cumulative updates
• 2 end-of-life instances migrated to SQL Server 2022

Total cost of the health check and remediation engagement: less than 8% of the first-year licensing saving.

What Made This Possible

The Trust did not have a uniquely dysfunctional SQL Server estate. What they had was entirely typical of organisations that have grown their infrastructure organically over many years: accumulation without rationalisation, licensing without justification, performance problems that had become accepted rather than addressed.

The health check worked because it imposed structure on complexity. It asked the questions that the day-to-day IT operation had never had time or mandate to ask: what are we actually running, what do we actually need, and what is it actually costing us?

The answers were available in the estate itself. They just needed to be surfaced.

Is Your Estate in a Similar Position?

If you are running SQL Server and have never done a structured health check, there is a high probability that you are:

• Licencing more than you need
• Paying for hardware capacity that better queries would make unnecessary
• Running on configurations left at defaults that are not appropriate for your workload
• Carrying performance problems that have become "normal" through familiarity

The only way to know is to look.

---

Thinking about your SQL Server renewal, or just not sure what you're running?

A SQLOPTIMISE Health Check gives you a complete picture of your estate and a prioritised plan to reduce cost and improve performance. Start the conversation →