Section 12.2. The Components of Server Load

12.2. The Components of Server Load

Load, in information technology, ultimately boils down to a combination of excessive CPU consumption, too many input/output operations and insufficient network speed or bandwidth. It's quite similar to the "critical tasks" of project management, where one bottleneck can result in the whole system grinding not to a halt, but to an unnaceptable level of slowness. If processes that are ready to run must wait for some other processes to release the CPU, the system is overloaded. If the CPU is idle, waiting for data to be sent across the network or to be fetched from persistent storage, the system is overloaded too.

"Overloaded," though, mustn't be understood as an absolute notion. Systems may be compared to human beings in respect of the fact that load is not always directly proportional to the work accomplished. As C. Northcote Parkinson remarked in Parkinson's Law , his famous satire of bureaucratic institutions:

Thus, an elderly lady of leisure can spend the entire day in writing and dispatching a postcard [...]. The total effort that would occupy a busy man for three minutes all told may in this fashion leave another person prostrate after a day of doubt, anxiety, and toil.

Poorly developed SQL applications can very easily bring a server to its knees and yet not achieve very much. Here are a few examples (there are many others) illustrating different ways to increase the load without providing any useful work:

Hardcoding all queries

This will force the DBMS to run parser and optimizer code for every execution, before actually performing any data access. This technique is remarkably efficient for swamping the CPU.

Running useless queries

This is a situation more common than one would believe. It includes queries that are absolutely useless, such as a dummy query to check that the DBMS is up and running before every statement (true story), or issuing a count(*) to check whether a row should be updated or inserted. Other useless queries also include repeatedly fetching information that is stable for the entire duration of a session, or issuing 400,000 times a day a query to fetch a currency exchange rate that is updated once every night.

Multiplying round-trips

Operating row-by-row, extensively using cursor loops , and banishing stored procedures are all excellent ways to increase the level of "chatting" between the application side and the SQL engine, wasting time on protocol issues, multiplying packets on the network and of course, as a side benefit, preventing the database optimizer from doing its work efficiently by keeping most of the mysteries of data navigation firmly hidden in the application.

Let me underline that these examples of bad use of the DBMS don't specifically include the "bad SQL query" that represents the typical SQL performance issue for many people. The queries described in the preceding list often run fast. But even when they run at lightning speed, useless queries are always too slow: they waste resources that may be in short supply during peak activity.

There are two components that affect the load on a database server. The visible component is made up of the slow "bad SQL queries " that people are desperate to have tuned. The invisible component is the background noise of a number of queries each of acceptable speed, perhaps even including some very fast ones, that are executed over and over again. The cumulative cost of the load generated by all this background noise routinely dwarfs the individual load of most of the big bad queries. As Sir Arthur Conan Doyle put in the mouth of Sherlock Holmes:

It has long been an axiom of mine that the little things are infinitely the most important.

As the background noise is spread over time, instead of happening all of a sudden, it passes unnoticed. It may nevertheless contribute significantly to reducing the "power reserve" that may be needed during occasional bursts of activity.

Repetitive short-duration mediocre statements often load a server more than the big bad SQL queries that take a long time to run.