March 9, 2014 at 8:11 am
Perry Whittle (3/9/2014)
muthyala_51 (3/8/2014)
any suggestions?For what?
anything now would just be speculation since the instance was restarted
I am trying to find the exact reason behind why the server behaved like this. Choked for memory even it had enough memory.
March 9, 2014 at 8:44 am
Again, anything we say is just speculation. We don't have access to your server and the logs. You need to check the all the logs on the server to try and determine what occurred.
March 9, 2014 at 2:57 pm
muthyala_51 (3/9/2014)
Perry Whittle (3/9/2014)
muthyala_51 (3/8/2014)
any suggestions?For what?
anything now would just be speculation since the instance was restarted
I am trying to find the exact reason behind why the server behaved like this. Choked for memory even it had enough memory.
To know the exact reason you should do a lot of troubleshooting not just shallow look ups. The information you provided was not enough. So pls try to answer the questions by the people so that you can get close to the solution.
--
SQLBuddy
March 10, 2014 at 1:35 pm
I went through all the logs and grabbed some info for you guys. The following links gives us the exact scenario which happened at that time.....
Error 1: There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed.
Error 2: New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 1680 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries.
http://www.sqlservercentral.com/blogs/aschenbrenner/2011/10/25/troubleshooting-threadpool-waits/
We have a 32 CPU on our production server and max worker threads setting is set to 2048. But according to microsoft recommendation it should be 960.
March 10, 2014 at 3:32 pm
muthyala_51 (3/10/2014)
I went through all the logs and grabbed some info for you guys. The following links gives us the exact scenario which happened at that time.....Error 1: There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed.
Error 2: New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 1680 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries.
http://www.sqlservercentral.com/blogs/aschenbrenner/2011/10/25/troubleshooting-threadpool-waits/
We have a 32 CPU on our production server and max worker threads setting is set to 2048. But according to microsoft recommendation it should be 960.
Error 1: Check this
Error 2: Try changing max worker threads to default value and test it.
What are your CPU settings on the server ? MAXDOP, CPU Affinity etc .. Do you have CLR , Linked servers hosted on SQL Server ?
--
SQLBuddy
March 10, 2014 at 6:12 pm
CLR is enabled and the server holds linked servers. Maxdop setting is 4. For CPU settings, both processor affinity mask and i/o affinity mask were checked. We also have extended stored procs on the servers.
March 11, 2014 at 12:33 am
How physical do you have and what is max server mem set to?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 11, 2014 at 6:25 am
500 GB RAM. 425 GB max memory setting.
March 11, 2014 at 9:44 am
muthyala_51 (3/10/2014)
CLR is enabled and the server holds linked servers. Maxdop setting is 4. For CPU settings, both processor affinity mask and i/o affinity mask were checked. We also have extended stored procs on the servers.
Did you check for any CPU pressure on the server ? Especially due to MAXDOP. Check for CX_Packet WAITS.
You should try to tune Worker Threads and MAXDOP settings for further troubleshooting.
--
SQLBuddy
March 11, 2014 at 10:29 am
What's your SQL Build no ? Also try to collect these counters (Perfmon) which gives us more insight ..
Processor – %Privileged Time
Processor – %Processor Time
Processor – %Interrupt Time
Processor – Interrupts\Sec
Process(sqlservr) - %Processor Time
System – Processor Queue Length
System - Processor Queue Length
System - Context Switches/sec
SQLServer:SQL Statistics Compilations/sec
SQLServer:SQL Statistics Re-Compilations/sec
--
SQLBuddy
March 11, 2014 at 10:33 am
muthyala_51 (3/11/2014)
500 GB RAM
Bit of an odd figure, i'm assuming that's 512GB RAM?
muthyala_51 (3/11/2014)
425 GB max memory setting.
Given the fact you have a lot of CLR and ext stored proc, etc i would lower the max memory setting a little. Monitor the server and if necessary increase or decrease again. My temptation would be to lower it to 384GB and then monitor.
You have to reserve enough RAM for the following
Ext Stored Proc
CLR
Windows OS mem manager
Any other apps running on the server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 13, 2014 at 10:40 am
muthyala_51 (3/11/2014)
425 GB max memory setting.Given the fact you have a lot of CLR and ext stored proc, etc i would lower the max memory setting a little. Monitor the server and if necessary increase or decrease again. My temptation would be to lower it to 384GB and then monitor.
You have to reserve enough RAM for the following
Ext Stored Proc
CLR
Windows OS mem manager
Any other apps running on the server
But during the downtime all the memory was able to windows. As the sql server was just trying to grab memory from the windows after restart.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply