SQL Server 2005性能排错(11)

select record 
from sys.dm_os_ring_buffers 
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

<Record id="1234" type="RING_BUFFER_BUFFER_POOL" time="345640123">
< BufferPoolFailure id="FAIL_OOM">
<CommittedCount>84344 </CommittedCount> 
<CommittedTarget>84350 </CommittedTarget > 
<FreeCount>20</FreeCount> 
<HashedCount>20345</HashedCount> 
<StolenCount>64001 </StolenCount> 
<ReservedCount>64001 </ReservedCount> 
</ BufferPoolFailure >

-- 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 

-- 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