December 18, 2015 at 6:55 am
It wasn't clear to me (I maybe skipped something posted): have you definitely identified the CPU pressure as originating from SQL Server? Are there other, non-SQL processes on the box that are consuming CPU cycles?
One common item that can chow CPU in the middle of the night is network backups, especially if they're running on-the-fly compression on each server.
Rich
December 18, 2015 at 7:14 am
It looks like I have a lot of reading to do over Christmas!
It is definitely sqlserver.exe that is using about 90% cpu. The server itself is responsive enough it is just the SQL part I can't get any connections to when it is in this state. As you say the backups do put it under more load but normally I've never seen this go much above 50% for short intervals. In the crashed state it just stays around 90% and never drops. I've always given it time to drop before restarting but it just won't. at least I now know how to get it an see what is happening.
This was happening every 48hrs or so but so far it has been OK since the memory change. I'll keep my fingers crossed for the weekend and the holiday period!
Thanks
Alastair
December 25, 2015 at 3:32 pm
Run EXEC sp_configure 'show', 1
reconfigure
EXEC sp_configure
Verify Priority Boost is set to 0, verify Max Server Memory (MB) is set below physical memory after leaving multiple GB for the OS and other memory-hungry processes, and verify Set Working Set Size is set to 0. If values are different from my verification values, consider the best practice values for each setting (support.microsoft.com is a good place to start). It may be this system needs settings that are not considered a best practice, but more typically it is someone else that had changed them long ago, saw no harm, left them that way, only to come back and bite you with SQL Server's IOCP_LISTENER going deaf & unyielding (likely due to its un-lockable memory ending up in the paging file).
Use perfmon.exe to monitor every Process' Working Set and Private Bytes counters, and sqlservr.exe's %Privileged Time (and %User time). Sample those counters over the system's problematic work periods (I prefer at most a 5 sec polling interval, because a paging event can last 10 seconds or less). Based upon that monitoring, consider implementing Lock Pages In Memory, but realize that if you give SQL Server all the Memory, then the OS and all other processes will get nothing. If the Lock Pages In Memory Advanced Windows Right has already granted to sqlservr.exe, perhaps similar long term monitoring overlooked the cause you are experiencing now. If LPIM is already set and if Max Server Memory was set too high, less physical memory for the IOCP_LISTENER's thread's stack space can be a result. A paging event does not have to have happened at the time when the IOCP_LISTENER dumped. A paging event could have happened minutes before the dump.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply