Once it happend 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) and we started to settle bad made things, but the situation went worst - 100% CPU as in the Figure 2 below. That's why I just captured the moment and want to share.

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

The problem was identified as overloaded application queues, processing XML files. After a together work with the developers, we detected that the Queues can be deleted. BUT the delete and select queries as part of a stored procedure were badly designed. The instance was running over 20 databases running the same code ... and it practically happened a complete blocking.

Here are some suggestions.

- DO NOT USE functions in comparison unless you must to. Try to use as less as possible in case you must.

- DO NOT USE many @parameters in queries, it kills the database engine.

Above are queries that I caught with sp_WhoIsActive.

The bad design of the qeuries is the fact that there are many functions and parameters used in the comparison, so that the execution plans of them are very bad.

Figure 2. 100% CPU on a 24 core server with 128GB RAM for more than 10 minutes

I was wordless and decided to kill all those queries and do a code-rework before proceeding. In the end it was a Happy End.