Large Number of Databases on 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Large Number of Databases on 2005

For anyone with a larger number of databases (500+): How many do you have in a single instance. If you are using multiple instance on a single server, how many dbs per instance. This is why I’m asking We are experiencing 701 "out of system memory" and temporary (usually) system freezes when the error occurs. We have 32bit 2005 version 9.00.2153.00, 32GB of memory, AWE enabled, quad dual-core 3GHz hyperthreaded server. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases…

I am completely stunned to hear PSS does not think this is a VAS problem.
was this escalated or handled by first level? "out of system memory" is a VAS problem.
the system freeze is the internal garbage collector working to clean up VAS.
is it possible that after the system recovered from the freeze, there was not an apparent VAS pressue, because it was just cleaned up? high number of DB is one of the consumers of VAS, not the only.
intermediate data structures from a hash join,
database backups
xprocs, OLE, CLR
all burn VAS, is your OS 32 or 64 bit? can it be on 64-bit?
is there a reason SQL cannot be 64 bit?
that why we have it
I would be very scared of having HT enabled on this system ________________________________________________________________
for finding VAS issues
Internal virtual memory pressure
VAS consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV.
VAS summary can be queries using the following view. — virtual address space summary view
— generates a list of SQL Server regions
— showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1
END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
— combine all allocation according with allocation base, don’t take
into
— account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
— we shouldn’t be grouping allocations with zero allocation base
— just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
The following queries can be used to assess VAS state. — available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0 — get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0 If the largest available region is smaller than 4 MB,
we are likely to be experiencing VAS pressure.
SQL Server 2005 monitors and responds to VAS pressure.
SQL Server 2000 does not actively monitor for VAS pressure,
but reacts by clearing caches when an out-of-virtual-memory error occurs.

Thanks for comment. It turns out we were running out of memory in the visible region (MemToLeave/VAS). The first PSS techs had no idea what they were talking about (big shock). In short the amount of space allocated to this region of memory on Win32 is limited (~1.2GB/instance). Having a very large number of active databases (1500 to 2000) consumed the majority of this memory, leaving to little to support larger multi-page allocations that we periodically are encounter (large plan optimization, backup, etc). The quick fix is to distribute the databases over multiple instance and or servers. The ideal solution is to migrate to 64 bit where this are of memory is limited only by the physical memory limitations of the server.
MemToLeave/VAS is major issue in 32 bit installations…
I am experiencing the same even after increasing the memory using -g switch but installation is 2000 sp4.
MohammedU.
Moderator
SQL-Server-Performance.com
MohammedU,
In 2005 the -g parm is likely to make your situation worse b/c it takes away from the visible area. You should discontinue use of the -g parm asap. Also, we found index accounting (visible via sys.dm_db_index_usage_stats DMV) was consuming a large amount of our visible due to the number of databases being used. We disabled index usage stats collection with trace flag 2330 to conserve on visable VAS
Sorry, juat saw that you are using 2000. It’s likely -g works the same way, but I would check w/PSS. Gppd luck luck getting to a tech who has a clue
It was PSS recommendation increase memory size using -g switch…
I agree with you getting the rigth tech when call PSS is the main thing…
MohammedU.
Moderator
SQL-Server-Performance.com
don’t be too hard on the first level guys
they are just there to answer the phone
and help you with PSSDIAG they try to help you solve your problem
but the skill to determine the most likely cause is hard to acquire
youngs guys tend to guess from a laundry list of usual suspects they want to be able to solve a problem
instead of automatically kicking it up to the escalation people
so even if it is obvious your problem is above their skill level
humor them and let them take a few stabs at it
before asking for escalation VAS is not something a newbie knows how to diagnose,
especially since the problem can disappear
In my case I gave them SQL DUMP also but they could not conclude what is the application and/or dll causing this issue…
MohammedU.
Moderator
SQL-Server-Performance.com
]]>