100% CPU After a Release at a Public Institution: Down to 5% in Half a Day with a View Rewrite and a Filtered Index
After a new release of a public institution's citizen application system, CPU usage on the database server locked at 100%. Aryasoft traced the problem to a single view queried by a new tracking screen, rewrote the view and added a filtered index covering only open records. CPU usage dropped to 5% after half a day of work, with no rollback and no server restart.
average during business hours
logical reads, tracking screen query
tracking screen's data query
active requests on the database
About the Client
-
IndustryPublic Sector
-
EnvironmentLine-of-business application managing citizen applications, heavily used during business hours
-
Client TypePublic institution (name withheld)
-
Database TechnologyMicrosoft SQL Server, on-premises infrastructure
-
Business PriorityService continuityUninterrupted citizen servicesControlled change
Background
A New Release Went Live and CPU Locked at 100%
The institution runs a line-of-business application on SQL Server where citizen applications are recorded, routed between departments and resolved. A new release introduced a tracking screen that let staff monitor open applications by department and status.
The release went live before business hours. As staff began to log in, CPU usage on the database server climbed quickly and soon locked at 100%. Screens slowed down, the application started to time out and application processing began to stall.
Rolling back the release would also have rolled back other fixes shipped with it. The institution needed a permanent fix without stopping the service or reverting the release.
Challenges
The View Returning Open Applications Scanned the Whole Table
The new tracking screen queried the view below every time it opened and at regular refresh intervals. Only a small fraction of the tens of millions of rows in the application table were open, but the conditions in the view prevented SQL Server from going straight to that small set.
AS
SELECT b.BasvuruId, b.BirimId, b.BasvuruTarihi,
dbo.fn_DurumAdi(b.DurumId) AS DurumAdi,
br.BirimAdi
FROM dbo.Basvuru AS b
INNER JOIN dbo.Birim AS br
ON br.BirimId = b.BirimId
WHERE ISNULL(b.KapanisTarihi, '19000101') = '19000101';
-- Column wrapped in ISNULL: no index seek, the table is scanned end to end
-- Scalar function runs separately for every scanned row
-- No supporting index for open records
100% CPU During Business Hours
Every staff member who opened the new screen triggered the same heavy query. As concurrent requests grew, CPU locked at 100% and every other operation started to wait.
Non-SARGable Predicate
Wrapping the KapanisTarihi (closing date) column in ISNULL prevented SQL Server from using an index to reach open records directly; every execution scanned the entire table.
Row-by-Row Scalar Function
The scalar function returning the status name was called separately for every scanned row, multiplying the CPU cost and making the plan inefficient.
No Rollback Option
The release contained other critical fixes and couldn't be reverted. The fix had to be applied on the live system without stopping the service.
Solution
Rewriting the View and Adding a Filtered Index for Open Records
Aryasoft resolved the problem in half a day by changing only database objects, with no change to the application code.
Live Workload Analysis
sp_WhoIsActive, Query Store and DMVs were used to rank queries by CPU. Most of the total CPU time came from a single family of queries called by the new tracking screen.
Execution Plan Review
The plan showed a full scan of the application table on every execution, with the scalar function called for every scanned row. Open applications were only a small percentage of the table.
Rewriting the View
After confirming that the data contained no 1900-01-01 dates, the ISNULL predicate was replaced with the equivalent KapanisTarihi IS NULL. A join to the status table replaced the scalar function. The view's columns didn't change, so the application needed no change.
A Filtered Index for Open Records
A filtered index containing only unresolved applications was created, keyed on the columns the screen filters by. Because it holds only open records rather than the whole table, it stays small and cheap to maintain.
Testing and Controlled Rollout
The changes were first validated in the test environment by comparing result sets, then applied online in production. CPU, query durations and wait statistics were monitored after the change.
Aryasoft Fix
CREATE OR ALTER VIEW dbo.vw_AcikBasvurular
AS
SELECT b.BasvuruId, b.BirimId, b.BasvuruTarihi,
d.DurumAdi, br.BirimAdi
FROM dbo.Basvuru AS b
INNER JOIN dbo.Durum AS d ON d.DurumId = b.DurumId
INNER JOIN dbo.Birim AS br ON br.BirimId = b.BirimId
WHERE b.KapanisTarihi IS NULL;
-- Filtered index containing only open applications
CREATE NONCLUSTERED INDEX IX_Basvuru_Acik
ON dbo.Basvuru (BirimId, DurumId, BasvuruTarihi)
WHERE KapanisTarihi IS NULL
WITH (ONLINE = ON);
Results & Benefits
CPU Usage Dropped from 100% to 5%
Instead of scanning the whole table, the tracking screen's query now reads from a small index that holds only open applications. The application was back to normal the same day.
CPU Pressure Removed
Average CPU usage during business hours dropped from 100% to around 5%, freeing capacity on the server for new workloads.
Millisecond Response Times
The tracking screen's data query went from seconds to milliseconds, and the timeouts stopped.
No Rollback
The fix stayed at the view and index level. The application code was unchanged, the release wasn't reverted and the server wasn't restarted.
Uninterrupted Public Service
Application processing continued throughout. The whole engagement, from analysis to production rollout, took half a day.
Key Takeaway
Releases Need Performance Testing, Not Just Functional Testing
A single view behind a new screen can lock up an entire server once it meets production data volumes and real user counts; with small test data, the problem stays invisible. Reviewing the execution plans of new views and queries against production-sized data catches non-SARGable predicates such as ISNULL and row-by-row scalar functions before release. Filtered indexes are one of the most efficient tools for screens that constantly query a small subset of a table. For design details, see our SQL Server index guide.
Is a New Release Putting Your SQL Server Under Pressure?
Aryasoft analyzes expensive queries, execution plans, views and indexes on the live workload, and applies permanent fixes without stopping the application.