July 8, 2010 at 1:57 pm
I have a SQL 2008 SP1 instance running on Win Server 2003 Enterprise SP2. This is a 32 bit machine with 4 GB of RAM.
I have the /3GB switch and AWE is not enabled. We have an engineering application that generates about .5 million rows in a single table. It's pure SQL via a stored proc and there is basically nothing else running on the SQL side (no CLR, mirroring, etc).
When we fire up 15 instances of the application and start writing data Task manager shows physical memory being consumed until SQL Server eventually errors with FAIL_PAGE_ALLOCATION 1. Management sees no reason why 15 exe's cant write data to a single table without SQL errors.
We are considering a test with the -g startup switch, but would like to confirm what is consuming memory that does not appear to be released when the application shuts down.
The SQL below shows high amount of committed KB in MEMORYCLERK_SQLBUFFERPOOL
SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0
I have also noticed increasing number of rows in sys.dm_exec_connections, and have spoken to developer about reusing connections on client side.
The proc is a simple proc that takes shapshot of input data, and inserts a single row in output table. We don't have option to add more server memory or change hardware. We are not considering use of AWE as this would not "fix" the problem, but would only delay eventual error.
It seems that once memory errors occur and that client exe is shutdown, SQL Server still cannot recovery memory, even after running DBCC commands.
I am wondering how to identify (and rule out) the following:
1) memory pressure due to multiple connections opened by client
2) errors caused by executing the proc (use of proc cache, query optimization, etc)
3) error caused by table inserts
4) error caused by SQL not releasing memory
Thanks in advance
July 8, 2010 at 7:43 pm
Some interesting reading has pointed me to this:
SELECT TOP 10 [type], sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC;
Which shows the top 10 as...
OBJECTSTORE_SNI_PACKET.................1460872
MEMORYCLERK_SQLCONNECTIONPOOL.....429904
MEMORYCLERK_SNI..............................114872
MEMORYCLERK_SQLSTORENG 88440
MEMORYCLERK_SQLGENERAL 45744
USERSTORE_TOKENPERM 24720
USERSTORE_SCHEMAMGR 1488
MEMORYCLERK_SOSNODE 1376
OBJECTSTORE_SERVICE_BROKER 400
USERSTORE_OBJPERM 248
I am now tying to determine what the top 2 items represent? Can anyone point me to a good definition or explanation of these:
OBJECTSTORE_SNI_PACKET 1460872
MEMORYCLERK_SQLCONNECTIONPOOL429904
FYI - I did not find that this post offered a good explanation of the SNI packet or SQLCONNECTIONPOOL also when I searched books online I did not get a definition for either.
http://www.sqlservercentral.com/Forums/Topic792418-146-1.aspx
Thanks
July 16, 2010 at 7:55 am
I am contributing the high SNI setting to approximately 100,000 user connections that were not closed by the client application, in the belief that connection pooling was limiting SQL sever connections. Here is a related post and answer.
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b6d25898-2b55-46fe-be68-b42805c38fe5"> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b6d25898-2b55-46fe-be68-b42805c38fe5
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply