How a Bad query can rise up your CPU to ceiling

Once it happened quite a black Friday for my environment after a deployment. The application got sucked so that becoming worst and worst. The average CPU raised over 80% (Figure 1). I just captured the moment and want to share.

Figure 1. Very high CPU on a 24 core with 128GB RAM

One of the bad queries found during the performance tuning is the next one:

The problem was identified as overloaded application queues that were processing XML files. The instance was running this code in 20 databases  simultaneously and it practically happened a complete blocking.

Here are some advice:

  • DO NOT USE functions in comparison unless you must to. Try to avoid.
  • DO NOT USE many @parameters in queries.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.