SQL server processor spikes

Hello,

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.
Bonnie
BonnieKAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
The ERP itself may be the culprit if it doesn't have a way to limit the memory usage and it's "stealing" memory from SQL Server.
It's always good policy to have your databases running in a dedicated server so it can use all the available resources. Sharing resources has only this kind of issues.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you provide the amount of RAM and the number of processors the server has?
Also check in SQL Server for the values configured for Min and Max server memories.
0
 
BonnieKAuthor Commented:
This is a 5 year old server, about to be retired next month.  It has 32 GB RAM and 2 xeon E5620@2.4GHz quad core processors.  Today, the server ran fine, with minimal spikes.  None lasted long enough to disconnect our ERP users.

I am trying to find out what is running when it does spike for a long time (>3-4 seconds)

I have seen some articles on how I can run queries to see what was running at a specific time, but they are a bit over my head and I was hoping for some hand holding...  That's why I am also trying to get help in figuring out how read the expensive queries data in the activity monitor.

Thanks
Bonnie
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
BonnieKAuthor Commented:
Also, sorry, where do I check for the min / max server memory in SQL?
0
 
BonnieKAuthor Commented:
OK, I found it - minimum = 0MB and maximum = 16384 MB
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So it's using only half of the server memory.
Do you know why SQL Server instance is limited only to 16GB of memory usage?
Meanwhile you can try to increase the max server memory for the SQL Server instance. I would say start with 24GB (24576 MB) but you can go until 28GB if you need to. Just leave between 4GB to 8GB of memory for the OS.
0
 
BonnieKAuthor Commented:
Hi Vitor,

Unfortunately it is not a dedicated server :(  

Our ERP program also runs on it and I think that may be why we only have it maxing at 1/2 the resources.  I will check with the ERP vendor to see if there is any issue in changing this...

What I am really interested in is finding the source of the spikes.  It may be a query that needs to be rewritten...

Thanks,
Bonnie
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Question answered and solution provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.