April 26, 2012 at 8:24 am
One of my servers has started locking up.. every few weeks. The SQL Server will stop accepting connections, but the jobs still run. When you try and stop the instance it hangs, and you have to restart the server. Is there anyway that I can now see what was running on the server at that time?
PerfMon shows that the page life goes to zero and the processor's are running between 50% and 75%. Latch waits are high in the 700 range, Database pages are 1,477,000 range, Disk writes/sec are between 0 and 19. Disk reads are between 3 and 45. Disk Queue length is below 0.9. paging file usage is between 23% and 32%. Memory Paging per sec is generally zero but bounces all over the place (0.4 to 62). Available MB is in the 270 range. Percent disk time for the data files tends to run around 5% but has some bumps up to 80%, and the Log file drive is below 0.00%.
April 26, 2012 at 8:42 am
I would wait for this to happen again and investigate the issue logging in with DAC.
High CPU could prevent the instance from accepting new connections, but it's hard to tell without looking at the processes running.
I would log in with the DAC, analyze the running processes and investigate wait types.
-- Gianluca Sartori
April 26, 2012 at 8:58 am
Also, can you please provide the version, edition and bitness of your OS and SQL Server instance?
And the results of this query:
-- memory overview
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS physical_memory_in_GB,
CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2)) AS VAS_GB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_committed_memory_in_GB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_target_memory_in_GB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS min_server_memory_GB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS max_server_memory_GB,
(
SELECT value_in_use
FROM sys.configurations
WHERE name = 'awe enabled'
) AS [awe_enabled]
FROM sys.dm_os_sys_info;
-- cpu overview
SELECT osi.cpu_count AS [Logical CPU Count],
osi.hyperthread_ratio AS [Hyperthread/Core Ratio],
osi.cpu_count / osi.hyperthread_ratio AS [Physical CPU Count],
(
SELECT COUNT(*)
FROM sys.dm_os_schedulers
WHERE scheduler_id < osi.cpu_count
AND is_online = 1
) AS [Logical CPUs Online]
FROM sys.dm_os_sys_info osi;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 9:41 am
You might want to configure a trace to run in the background that logs expensive activity, i.e. high reads > 100,000, high CPU > 10,000, high duration > 15,000 (those values are open to configuration) and have that running. You can then check the trace file when this happens again and figure out what is running during that time that is choking the server.
A couple of helpful posts;
- Set up and schedule a server side trace[/url]
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 27, 2012 at 6:47 am
MS Server 2003 Standard x64 (Version 5.2.3790)
Microsoft SQL Server 2005 - 9.00.4060.00 (X64) Mar 17 2011 13:06:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
physical_memory_in_GBVAS_GBbuffer_pool_committed_memory_in_GBbuffer_pool_target_memory_in_GBmin_server_memory_GBmax_server_memory_GBawe_enabled
11.998192.009.619.700.002097152.000
Logical CPU CountHyperthread/Core RatioPhysical CPU CountLogical CPUs Online
8818
April 27, 2012 at 7:01 am
Thanks. From those numbers it looks like you need to set max memory for the buffer pool. You have it at 2097152GB, which in peak usage times will allow SQL Server to use as much memory as the server contains, and that creates serious problems for Windows. SQL Server is greedy when it comes to memory by design, it is up to us to throttle it appropriately. Here is a good guide on the topic: Suggested Max Memory Settings for SQL Server 2005/2008[/url]
The numbers in the article reflect a dedicate SQL Server box with nothing else running on it. If it is a multipurpose server make sure you account for the other processes and lower max memory appropriately so they will have memory available when they need it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 7:11 am
I did have a trace running... but no data was written out.. I do not want to create large trace files, so if this keeps going on, I might get the settings down to something that works..however when it locks up I expect the trace data being held for a write will be lost in any case.
April 27, 2012 at 7:15 am
opc.three's recommendations definitely need to be applied first and see how things go from there. Once that is done I'm guessing that you will not lock up as "hard" as in the past. You should still consider running some trace to find out what is running and hitting the server so hard though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 27, 2012 at 7:19 am
PS how does this affect your specific situation? We don't know if it was a root cause, but it could have been. When SQL Server starts getting greedy, and Wndows decides it is being starved it will force SQL Server to page out a bunch of its memory which can cause PLE to bottom out, and cause all kinds of other havoc on your system as SQL Server scrambles to serve data after it just had a large section of its buffer pool cleared out. You may want to check your error log for a message that contains wording similar to this:
A significant part of sql server process memory has been paged out.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 7:26 am
My boss states that he has always seen that you have to set the SQL server to Max RAM.. does anyone know of any links from MS (MSDN) that state how to set up RAM.. I will check but if you know of any please post the links.
April 27, 2012 at 7:31 am
What we do get in the log file.. from time to time (including before the lock-ups)
Date4/26/2012 5:42:29 AM
LogSQL Server (Archive #1 - 4/26/2012 5:42:00 AM)
Sourcespid4s
Message
CACHESTORE_BROKERTO (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
Date4/26/2012 5:42:29 AM
LogSQL Server (Archive #1 - 4/26/2012 5:42:00 AM)
Sourcespid4s
Message
CACHESTORE_BROKERREADONLY (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 32 KB
MultiPage Allocator = 0 KB
April 27, 2012 at 7:39 am
dwilliscp (4/27/2012)
My boss states that he has always seen that you have to set the SQL server to Max RAM.. does anyone know of any links from MS (MSDN) that state how to set up RAM.. I will check but if you know of any please post the links.
"Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for the OS and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server."
While this doesn't explicitly state that it should be done it does show that there is a need to do that. I have yet to see where there are no some other processes running on a SQL Server box that require memory.
http://msdn.microsoft.com/en-us/library/ms178067.aspx
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 27, 2012 at 7:40 am
dwilliscp (4/27/2012)
My boss states that he has always seen that you have to set the SQL server to Max RAM.. does anyone know of any links from MS (MSDN) that state how to set up RAM.. I will check but if you know of any please post the links.
How to set it up is in the article I liked to earlier. sp_configure.
If you want official documents from MS on what to set it to, I don't know if there are any. I always point folks to Glenn's article. It's a good, generic place to start.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 7:44 am
From msdn
Use max server memory to prevent the SQL Server buffer pool from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. SQL Server does not immediately allocate the memory specified in max server memory on startup. Memory usage is increased as needed by SQL Server until reaching the value specified in max server memory. SQL Server cannot exceed this memory usage unless the value of max server memory is raised.
...
To reduce the max server memory you may need to restart SQL Server to release the memory.
...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 7:48 am
I found the following on MS site (http://msdn.microsoft.com/en-us/library/ms178067(v=sql.90).aspx)
"Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for Windows 2000 or Windows Server 2003 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server."
So my boss is correct.. the recommended state (by MS) is to let SQL Server get what ever memory it can from the server.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply