BonnieK
asked on
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
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
ASKER
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
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
ASKER
Also, sorry, where do I check for the min / max server memory in SQL?
ASKER
OK, I found it - minimum = 0MB and maximum = 16384 MB
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Question answered and solution provided.
Also check in SQL Server for the values configured for Min and Max server memories.