The system used to be quick and now it is slow

Slow by degrees

It did not happen on a particular day. The order screen that once opened instantly now takes eight seconds. The month-end report times out unless it is run in the evening. The overnight import that used to finish in the small hours is still running when the first person logs in. Staff have adapted: they start a search and look at their phone, and they know which screens to avoid on a Monday morning.

Because it crept up, it is easy to accept as the way things are. It rarely needs to be. A system that was quick with less data can usually be made quick again.

What usually lies behind it

In business systems the cause is most often in the database, and most often one of a short list.

Growth. A query that searches a table without a suitable index reads every row. With ten thousand rows nobody notices. With ten million it takes seconds, and it gets a little worse every week.

Queries that fetch too much. A screen that shows twenty orders may be loading every order ever placed and discarding the rest, or running a separate query for each line on the page.

Reports on the live database. A report that reads two years of transactions competes with the people entering today’s. Each can block the other.

The server. It may be short of memory or sitting on slow disks, or the database may have been installed with default settings and never adjusted. Maintenance jobs that keep indexes in order may have stopped running long ago.

The browser. In web applications that stay open all day, a screen can leak memory until it crawls. If refreshing the page makes it quick again, the database is not the culprit.

Measure before changing anything

Everyone has a theory about why the system is slow, and the theories often disagree. A database such as SQL Server keeps its own record of which queries run, how often and how long they take. Reading that record changes nothing on the system, and it usually shows that a small number of queries account for most of the waiting.

You can make a start this week. Ask staff to note which screens and reports are slow, at what time of day, and roughly how long each takes. Check whether the slowness is constant or comes at particular times, which would point to a clash with a scheduled job or a backup.

Your options

Buy a bigger server. Quick to do, and right if the measurements show the server is short of memory or processing power. It is rarely the lasting fix. Hardware gives a one-off improvement, while a query that scans a growing table keeps getting slower. Some time later you are back where you started with a larger bill, particularly in the cloud, where the bigger machine is charged for every month.

Tune what you have. Add the missing indexes, rewrite the worst queries, correct the server settings and restore the maintenance jobs. Your IT provider or a database administrator can do the server side. The query work needs someone who can read the application.

Move reporting off the live database. Where reports are the problem, running them against a copy removes the contention.

Archive old data. If the system holds fifteen years of records and people use the last two, moving the rest out makes everything smaller.

Where we would start

We would measure. Our SQL Server performance service starts from the database’s own evidence and deals with the few queries that cost the most. Each change is timed before and after. If the findings point to something larger, such as a design that cannot cope with the volume, we say so and set out the choices. Keeping the system quick afterwards is part of a maintenance arrangement.