Over the last week or so our ERP server has been freezing. This happens when the sqlservr processes goes up to 99%. The SQL process jumps around a lot. It will sit at single digits for a while, then climb and drop. But dozens of times throughout the day it has gone up to 99% and sits there for 3-5 seconds. This is causing our ERP users to get disconnected during transactions and records are getting locked. I am looking at Recent Expensive Queries, but I cannot pinpoint any query that is doing this.
I don't really know how to read this. Is there a way to figure out which query or queries are causing our processor to spike? Unfortunately I do not know anything about performance tuning SQL or researching the cause of this issue.
Thanks for any help.