Task:
Identify the issue at hands and resolve.

Action:
An application upgrade was run for which a brief downtime was obtained and thereafter the new code started hitting the database server.

It was identified that a certain query coming from a particular client API was taking very long and was also consuming a chunk of CPU.

Soon the CPU utilization was hitting the roof and overall response time for all the queries went too high. Investigation revealed that a particular query which was hitting the database server hundreds of times every minute was inducing implicit conversion on a large table.

There was a mismatch in the parameter data type and the column data type which caused the database engine to scan the related index instead of effecting a seek operation.
The required information was shared with the application team and soon a fix was introduced which helped alleviate the problem in a short span of time.

Result:
The issue got within a brief span of time with minimal impact to the client.