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:

SELECT   TOP (@ProcessBatchCount_p) *
FROM     dbo.Queues AS Q WITH (NOLOCK)
WHERE    ((Q.QueueTypeID NOT IN (@queueTypeBetSlipWon_p, @queueTypeBetSlipCancelled_p, @queueTypeBetSlipRejected_p, 
@queueTypeBetSlipRefoundCancelled_p, @queueTypeBetSlipEndGame_p)
           AND Q.AttemptCounter < @attemptCounter_p)
          OR (Q.QueueTypeID = @queueTypeBetSlipWon_p
              AND Q.AttemptCounter < @winAttemptCounter_p)
          OR ((Q.QueueTypeID IN (@queueTypeBetSlipCancelled_p, @queueTypeBetSlipRejected_p, 
  @queueTypeBetSlipRefoundCancelled_p)
               AND Q.AttemptCounter < @refundAttemptCounter_p))
          OR (Q.QueueTypeID = @queueTypeBetSlipEndGame_p
              AND Q.AttemptCounter < @endGameAttemptCounter_p))
         AND Q.QueueTypeID NOT IN (SELECT Item
                                   FROM   #resultsQueueType)
         AND Q.QueueTypeID NOT IN (SELECT Item
                                   FROM   #betSlipItemsQueueType)
         AND (Q.ProcessOn IS NULL
              OR Q.ProcessOn < @UtcTimeNow)
ORDER BY Q.ProcessOn;

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.