November 15, 2013 at 8:31 am
I am trying to find out what could be causing this issue. Why would we be waiting on cpu when its barely being used. Signal waits are varying from 35 to 55% and cpu usage is only at 5% usage.
We are using Windows Server 2012 with SQl Server 2012 Standard edition with cpu5. There are 3 instances on the server each with max memory 50gb memory and the server has a total of 190gb memory. The machine is a 12 core machine with hyperthreading enabled. What types of things should I be looking into.
thanks for any insight. And let me know if you need additional information to help.
November 15, 2013 at 8:59 am
1) exactly what query are you using to identify the signal waits (and waits in general)? My guess is something like Glenn Berry's which is since server uptime or last reset, in which case you will be tracking stuff during all your batch operations such as data loads, reindexing, stats updates, checkdb, etc, which could be meaningless for your operational needs.
2) what are other high waits?
3) do you have any affinity settings other than default?
4) are these virtualized servers by any chance?
5) is anyone complaining about poor performance?
6) there are some very significant bug fixes in SP1 through CU6 (current highest build). I strongly recommend you try to upgrade to that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 15, 2013 at 10:31 am
We actually use Confio's Ignite when I am looking at resources. I have attached a screenshot. The affinity is set to the defaults. No virtuals and no performance complaints. Just very odd behavior. It seems to not being using the cpu on the box.
November 16, 2013 at 8:24 am
Well, I think you are correct - it does seem to be odd behavior. Unfortunately it is so odd that I cannot offer any insight without being on the box. None of the usual suspects come to mind ... sorry!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 4, 2013 at 2:20 pm
For what it's worth, I ran across your post while looking into the same issue and almost the same hardware.
I have no performance issues, so until I can discover a definitive answer I have chosen to interpret it to mean that the high signal waits as a percentage means that the CPU load is extremely low, and therefore a high percentage - as opposed to a high absolute number like 50000 seconds - just means that when compared to the overall number of waits, that signal waits are the highest as a percentage of a very low value. 20% of 1,000,000 is a lot, but 20% of 100 is not.
Having said that, I continue to look for a better explanation.
December 4, 2013 at 2:25 pm
Thank you for the response. That does somewhat make sense. But I have still been researching as well and haven't come across any other explanations.
December 4, 2013 at 2:28 pm
I intend to find the absolute value of all waits for the same period and see what the % of signal waits amounts to to get a better feel for the impact.
October 14, 2014 at 3:11 pm
I'm facing the same issue. And, oddly enough, it is showing the same signal waits in Ignite.
October 14, 2014 at 3:48 pm
Do you see any performance issues or complaints from your users other than it displaying in monitoring. ? If so I would recommend you do performance analysis using wait_stats and see if there is any bottlenecks on CPU. It is also little tricky when we have multiple instances on the same box, I would recommend to check the same from Ignite for other 2 instances and see how they look too.
November 19, 2015 at 11:41 am
I'm investigating a problem that's kind of similar to this. Maybe you've already done this but I would suggest looking at a trace.
We have a front end that throws a really poorly performing query at the sql server every 15 seconds. That query gets parallelized and (since we have MAXDOP set at 0) hogs all of the available cpus. Any other queries that come in while that one is running have to wait.
You might have a similar situation. Lowering MAXDOP might lower the signal wait percentage. But if the poorly performing query is also creating locks, it will also extend those locks. That's kind of my dilemma.
November 19, 2015 at 5:21 pm
dbruton95 (11/19/2015)
I'm investigating a problem that's kind of similar to this. Maybe you've already done this but I would suggest looking at a trace.We have a front end that throws a really poorly performing query at the sql server every 15 seconds. That query gets parallelized and (since we have MAXDOP set at 0) hogs all of the available cpus. Any other queries that come in while that one is running have to wait.
You might have a similar situation. Lowering MAXDOP might lower the signal wait percentage. But if the poorly performing query is also creating locks, it will also extend those locks. That's kind of my dilemma.
Sounds like you've identified the problem code... why not just get the Dev Manager to get one the Developers to fix the bloody thing?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2016 at 3:45 pm
I had the same issue on a server that was receiving a huge number of very fast queries. The issue is that there was 1 or 2 queries that did tables scans and hogged a CPU until it finished. Once I created proper indexes for the query the Signal waits reduced to near normal % and the throughput increased by over 10%.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply