How We Turned a Sluggish Hospital HIS on SQL Server 2008R2 into a Faster System

System Environment

The hospital HIS backend runs on SQL Server 2008 R2 with more than 1 TB of data. The database server is dedicated to SQL Server and is equipped with 64 CPU cores and 128 GB RAM .

Configuration screenshots:

Observed Database Metrics

Requests per second –

Statement execution –

Wait statistics –

Wait time –

CPU usage –

Memory usage –

Disk queue –

The metrics indicated that the slowdown was systemic – every major resource (CPU, memory, I/O) showed high contention.

Optimization Phase 1 – General Database Tuning

Goal : bring CPU utilisation of heavy statements down to ~70 % and relieve memory pressure.

Actions taken :

Created high‑impact covering indexes on frequently scanned tables.

Adjusted server‑level options: max degree of parallelism, cost threshold for parallelism, and increased max server memory to leave headroom for the OS.

Optimised tempdb – added multiple data files (one per CPU core up to 8), set appropriate autogrowth, and enabled trace flag 1118 for mixed‑extent allocation.

Enabled READ_COMMITTED_SNAPSHOT to reduce blocking on read‑write workloads.

Updated statistics with FULLSCAN and set AUTO_UPDATE_STATISTICS_ASYNC ON.

Expectation : CPU for the most expensive statements would drop to ~70 % and memory pressure would lessen.

Result : The number of long‑running statements fell dramatically and wait‑blocking improved, but overall CPU stayed above 90 % and some transactions still timed out.

Metric comparison:

Optimization Phase 2 – Statement‑Level Tuning

Further analysis highlighted three critical issues:

Memory‑driven I/O pressure.

Persistently high CPU utilisation.

A small set of extremely costly statements.

Targeted actions :

Applied plan guides to statements that could not be changed in application code, forcing a more efficient execution plan.

Re‑engineered interface views: removed user‑defined scalar functions from WHERE clauses, added missing indexes (including filtered and covering indexes), and rewrote joins to be sargable.

Implemented snapshot‑based read‑write separation for reporting workloads by creating a mirrored database with READ_COMMITTED_SNAPSHOT enabled, thereby off‑loading reporting reads from the primary.

Result: execution time and resource consumption of the targeted statements dropped sharply, but overall CPU remained >90 % and the disk queue stayed high.

Before/after screenshots of a representative statement:

Optimization Phase 3 – Deep Metric Analysis

CPU analysis

The performance counter SQL Compilations/sec peaked at >2000, indicating that ad‑hoc queries were being compiled repeatedly because they were not parameterised.

Solution: enable forced parameterisation at the server level.

sp_configure 'forced parameterization', 1;
RECONFIGURE;

After enabling forced parameterisation, compilation rate dropped and CPU utilisation showed a modest improvement, as illustrated below:

Memory analysis

Memory diagnostics revealed that the SQLOPTIMIZER single‑page cache consumed >80 GB while the data‑page cache held only ~20 GB, creating severe memory pressure.

Standard DBCC commands ( DBCC FREESYSTEMCACHE, DBCC FREEPROCCACHE) could not release the optimizer cache, so the SQL Server service was restarted.

Post‑restart memory usage dropped dramatically, as shown:

Even after these measures the system became noticeably faster, yet CPU stayed above 90 % and the disk queue remained high, indicating residual bottlenecks.

Key Takeaways

Comprehensive baseline collection (wait stats, CPU, memory, disk queue) is essential before any change.

General tuning (indexes, tempdb layout, server options, snapshot isolation) reduces obvious blocking but may not address high compilation overhead.

When application code cannot be altered, plan guides and view redesign can mitigate expensive statements.

Forced parameterisation is an effective remedy for environments with many ad‑hoc queries.

Large optimizer caches may require a service restart; monitor SQLOPTIMIZER cache usage regularly.

Software‑level fixes should be exhausted before considering hardware upgrades; read‑write separation or scaling may be needed only after all tunable knobs are exhausted.