内存问题:使用几乎所有缓冲区缓存的Tempdb
内存问题:使用几乎所有缓冲区缓存的Tempdb
提问于 2018-11-22 15:24:29
在我今天正在处理的一个服务器上，我看到几乎所有的缓冲区缓存都是由tempdb填充的。因此，服务器上的内存非常少。
CPU
Tempdb：
版本：
Microsoft 2014 (SP2-CU13) (KB4456287) - 12.0.5590.1 (X64) 2018年8月1日01:23:36版权(c)微软公司标准版(64位)WindowsNT6.3 (Build 14393：) (Hypervisor)
4个数据文件= 4096 MB 1日志文件=1536 MB
我的问题是TEMPDB使用13 of的缓冲区缓存。我检查了tempdb中的对象，这些对象是我的sp_blitz临时表中最大的对象，在这些表中没有那么大。
没有为任何数据库启用RCSI，因此不应该成为版本存储问题。
无未结交易
没有打开游标。
当我在tempdb上执行检查点时，大约需要30秒，但是完成。
当我执行dbcc反清洁缓冲区时，缓存缓存中的tempdb的存在会减少到有时1gb，有时4gb，30秒后，它又回到了13 GB的辉煌中。
例如：
dbcc dropcleanbuffers
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 结果就在：
db_name db_buffer_pages db_buffer_MB db_buffer_percent
tempdb 620627 4848 58.09630秒后：
db_name db_buffer_pages db_buffer_MB db_buffer_percent
tempdb 1313835 10264 83.560Tempdb缓冲区缓存峰值使用率(its_over_9000.jpeg)
检查tempdb中的对象：
use tempdb
go
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY TotalSpaceKB desc排名前4位：
TableName SchemaName RowCounts TotalSpaceKB UsedSpaceKB UnusedSpaceKB
#A3B2C869 dbo 0 72 16 56
#A52E4149 dbo 0 72 16 56
#A59B10DB dbo 0 72 16 56
#A68F3514 dbo 0 72 16 56总共有74个物体。
我确实看到了很多(375 000+！)有7965字节空闲空间的页面，内存缓冲区中只有一行计数。使用的查询：
select * from sys.dm_os_buffer_descriptors
where database_id = 2
order by free_space_in_bytes desc (例如
)
file_id page_id page_level allocation_unit_id page_type row_count free_space_in_bytes
1 109763 0 71635384526569472 INDEX_PAGE 1 7965但是有40个字节的空闲空间(1M)甚至更多，见下文。
过滤更多：
select page_type,free_space_in_bytes, count(*)as counter from sys.dm_os_buffer_descriptors
where database_id = 2
group by page_type, free_space_in_bytes
having count(*) > 500
order by free_space_in_bytes desc 
问题
为什么我的tempdb在发出dbcc下拉清除缓冲区后填充得这么快？我是不是漏掉了什么，我该查些什么？
更新2018年11月30日
在将TEMPDB设置为512 MB的4个文件并重新启动服务器后，缓冲区中的MB似乎更低。然而，它仍然是6GB。
关于现在该做什么/检查什么，还有其他的想法吗？
附加信息：
长尾藻
分析器捕获的常量执行查询的示例：
exec sp_reset_connection
SELECT COUNT(*) FROM dbo.SomeTable WHERE Error IS NULL有些连接使用可序列化的：
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level serializable 有些人不
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed麦克斯·梅姆有点偏低：
DBCC页面检查：(3604)；
DBCC页面(2，5,474258，3)；(3604)；
bpage = 0x00000016AA16C000 bhash = 0x0000000000000000 bpageno = (5:474258)
bdbid = 2 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 1952 bstat = 0x109
blog = 0xcdcdcdcd bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000016AA16C000
m_pageId = (5:474258) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8020
m_objId (AllocUnitId.idObj) = -1778255884 m_indexId (AllocUnitId.idInd) = 255
Metadata: AllocUnitId = 71941054260314112 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1
m_freeCnt = 40 m_freeData = 8150 m_reservedCnt = 0
m_lsn = (5148:180860:473) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (5:2) = NOT ALLOCATED SGAM (5:3) = NOT ALLOCATED PFS (5:469104) = 0x4 100_PCT_FULL
DIFF (5:6) = NOT CHANGED ML (5:7) = NOT MIN_LOGGED
Blob row at: Page (5:474258) Slot 0 Length: 8054 Type: 3 (DATA)
Blob Id:2794796220416
000000464FAFA06E: 0044002b 006f0051 00550038 00520058 +.D.Q.o.8.U.X.R.
...@Craig您的输出：
回答 2
Database Administration用户
发布于 2018-11-22 16:20:03
不确定，但是sys.allocation_units和sys.partitions之间的连接并不完全符合文档的要求。例如
select bd.file_id, bd.page_id, p.*
from sys.dm_os_buffer_descriptors bd
left join sys.allocation_units au
on bd.allocation_unit_id = au.allocation_unit_id
left join sys.partitions p
on ( au.type in (1,3) and au.container_id = p.hobt_id )
or
( au.type = 2 and au.container_id = p.partition_id )
where database_id = 2此外，您还可以尝试从Tempdb访问检查几页，以查看页面头和数据是否为您提供了它们来自何处的任何指示。
Database Administration用户
发布于 2018-11-22 17:40:22
您能试一下这个查询并发送输出吗？
SET NOCOUNT ON;
SELECT
(DATEDIFF(n, dtat.transaction_begin_time, GETDATE())) as duration, *
FROM
sys.dm_tran_active_transactions dtat
INNER JOIN sys.dm_tran_session_transactions dtst
ON dtat.transaction_id = dtst.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON dtst.session_id = es.session_id
WHERE es.session_id > 50 谢谢,
克雷格
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/223220
复制相关文章
相似问题
腾讯云开发者
