Troubleshoot SQL Server performance issues

I wanted to write a reminder for myself on things to do in order to troubleshoot sql server performance issues. The idea is to keep this post updated and continue to add suggestions time to time.

Troubleshoot High CPU usage

Some things that might be worth to check..

• Check for missing indexes/stats?
• Check for cardinality estimation problems
• Check query plan
• Find most expensive queries
• Check for deadlocks

Find most expensive queries

It’s possible to use the activity monitor to find the recent most expensive queries

Check for deadlocks

We can use Xevents in SSMS and the xml_deadlock_report event to find deadlocks

Check post on Xevents: XEvents post

Check index fragmentation and last stats update

right click on index > properties > fragmentation

You should consider to rebuild if the fragmentation is higher than 30%