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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
BonnieKAuthor Commented:
Also, sorry, where do I check for the min / max server memory in SQL?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Question answered and solution provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.