Finding out what makes SQL slow

An SQL statement can be slow for a lot of reasons. Here, we will provide a short list of these reasons, with at least one way of recognizing each.

Getting ready

If the SQL statement is still running, look at Chapter 8, Monitoring and Diagnosis.

How to do it…

The core issues are likely to be the following:

• You’re asking the SQL statement to do too much work.
• Something is stopping the SQL statement from doing the work.

This might not sound that helpful at first, but it’s good to know that there’s nothing really magical going on that you can’t understand if you look.

In more detail, the main reasons/issues are these:

• Returning too much data.
• Processing too much data.
• Index needed.
• The wrong plan for other reasons—for example, poor estimates.
• Locking problems.
• Cache or input/output (I/O) problems. It’s possible the system...