主页 / dba / 问题

问题[tempdb](dba)

Martin Hope
stack_henk
Asked: 2023-02-24 19:25:04 +0800 CST
  • 6

我正在争论是否还需要在tempdb本身上设置 ALLOW SNAPSHOT ISOLATION，还是仅在用户数据库上设置功能才能启动（除了引用此功能的代码之外）？

大多数文章和示例似乎只在用户数据库本身上设置属性，并将tempdb保持为 OFF。但后来我发现了这个链接Pro SQL Server 2005 Database Design and Optimization，它说：

（您甚至可以在 tempdb 上执行此操作）
alter database tempdb set allow_snapshot_isolation on

除了用户数据库还是仅用户数据库之外，是否还需要在tempdb上设置它？

主要目的是用于需要启用快照隔离的应用程序。

tempdb
Martin Hope
Eitan Blumin
Asked: 2022-01-24 03:44:40 +0800 CST
  • 6

由于在用户数据库上运行 DBCC CHECKDB 时缓冲区锁存超时，我们的 Ola Hallengren IntegrityCheck 作业失败。

但是，报告的缓冲区锁存超时在 TempDB（数据库 ID 2）中。

作业输出：

Date and time: 2022-01-22 09:04:15 [SQLSTATE 01000]
Database context: [master] [SQLSTATE 01000]
Command: SET LOCK_TIMEOUT 600000; DBCC CHECKDB ([SentryOne]) WITH NO_INFOMSGS, ALL_ERRORMSGS, MAXDOP = 4 [SQLSTATE 01000]
Msg 845, Sev 17, State 1, Line 1 : Time-out occurred while waiting for buffer latch type 2 for page (6:222), database ID 2. [SQLSTATE 42000]
Outcome: Failed [SQLSTATE 01000]
Duration: 12:40:32 [SQLSTATE 01000]
Date and time: 2022-01-22 21:44:47 [SQLSTATE 01000]

SQL 错误日志中的消息：

Date 1/22/2022 9:35:22 PM
Log SQL Server (Archive #1 - 1/23/2022 12:00:00 AM)

Source spid777

Message
A time-out occurred while waiting for buffer latch -- type 2, bp 0000016D7A1DE340, page 6:222, stat 0x40d, database id: 2, allocation unit Id: 536870912/281475513581568, task 0x0000016CD624E4E8 : 2, waittime 300 seconds, flags 0x1a, owning task 0x0000016CD624E4E8. Not continuing to wait.

BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

DBCC CHECKDB (SentryOne) WITH all_errormsgs, no_infomsgs, maxdop = 4 executed by NT SERVICE\SQLSERVERAGENT terminated abnormally due to error state 6. Elapsed time: 12 hours 39 minutes 57 seconds.

[INFO] Database ID: [12]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '1225176490'

[WARNING] ALTER or DROP TABLE could not clean up root row within 10 seconds.

有问题的页面是 TempDB 中的 IAM 页面。它的 DBCC PAGE 输出：

PAGE: (6:222)

BUFFER:

BUF @0x0000016D6B622C40

bpage = 0x00000161A6C52000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000016D6B622AD0
bsort_r_prevbP = 0x0000016D6B622B80 bhash = 0x0000000000000000 bpageno = (6:222)
bpart = 4 ckptGen = 0x0000000000000000 bDirtyRefCount = 0
bstat = 0x9 breferences = 3 berrcode = 0
bUse1 = 19321 bstat2 = 0x0 blog = 0x215a215a
bsampleCount = 1 bIoCount = 0 resPoolId = 0
bcputicks = 330 bReadMicroSec = 848 bDirtyContext = 0x0000000000000000
bDbPageBroker = 0x0000000000000000 bdbid = 2 bpru = 0x0000016D3A170040

PAGE HEADER:

Page @0x00000161A6C52000

m_pageId = (6:222) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 1837007664 m_indexId (AllocUnitId.idInd) = 28675
Metadata: AllocUnitId = 8071415347312328704
Metadata: PartitionId = 2162691495132069888 Metadata: IndexId = 0
Metadata: ObjectId = -1083263203 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 90 m_slotCnt = 2 m_freeCnt = 6
m_freeData = 8182 m_reservedCnt = 0 m_lsn = (324:1435184:42)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -551517712 DB Frag ID = 1

Allocation Status

GAM (6:2) = ALLOCATED SGAM (6:3) = ALLOCATED
PFS (6:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (6:6) = NOT CHANGED
ML (6:7) = NOT MIN_LOGGED

IAM: Header @0x00000036C4278064 Slot 0, Offset 96

sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (8:511232)

IAM: Single Page Allocations @0x00000036C427808E

Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)

IAM: Extent Alloc Status Slot 1 @0x00000036C42780C2

(8:511232) - (8:592656) = NOT ALLOCATED
(8:592664) - = ALLOCATED
(8:592672) - (8:770424) = NOT ALLOCATED

我发现这个似乎最相关的 DBA StackExchange 问题： 出现错误“在等待页面 (1:3564879) 的缓冲区闩锁类型 2 时发生超时，数据库 ID 7。” 但是 ID 为 7 的数据库不存在

但是，在这种情况下，报告的超时页面位于用户数据库中，而不是 TempDB 中。

此外，我们使用的是最新的 SQL Server 版本（2019-CU14 Enterprise），因此无论如何都不应该发生这种情况。

我确实注意到 TempDB 数据文件在下午 6 点和 9:40 左右的延迟非常高。不过，我不确定这是否相关，因为下午 6 点的延迟峰值更高，但这项工作仅在晚上 9:44 左右失败。

TempDB 数据文件延迟

这些延迟峰值与 Azure Monitor 指标VM Cached IOPS Consumed Percentage和VM Cached Bandwidth Consumed Percentage.

附加信息：

  • 服务器安装在 Azure VM 上：Standard E8s v3 (8 vcpus, 64 GiB memory)
  • 有问题的用户数据库 (SentryOne) 具有内存优化表。
  • TempDB 内存优化元数据已禁用。
  • 即时文件初始化已启用。
  • 内存中的锁定页面已禁用。

任何想法为什么会发生这种情况以及将来如何防止它？

谢谢！

sql-server tempdb
Martin Hope
PTL_SQL
Asked: 2022-01-20 18:15:26 +0800 CST
  • 0

我刚刚在我的个人 SQL 实例中发出了嘘声。我想将 tempdb 文件分别移动到 C:\tempdb.mdf 和 C:\templog.ldf。alter database ...modify file... 命令成功运行。但是，我没有意识到我不能在 C:\ 中创建文件，只能在那里创建文件夹。因此，在运行修改文件命令并重新启动服务后，并没有创建新文件，即 C:\tempdb.mdf 和 C:\templog.ldf。

当我尝试获取 tempdb 数据库的属性以查看文件指向的位置时，我发现我无法获取属性，也无法再连接到数据库实例。因此，即使我可以在配置管理器中重新启动服务，我也无法使用 SSMS 或 SQLCMD 连接到实例。我收到找不到文件的错误。

除了重新安装实例（因为我没有主数据库的备份），请问有没有其他方法可以解决这个问题？

谢谢你。

sql-server tempdb
Martin Hope
Davide De Pretto
Asked: 2021-01-24 16:04:16 +0800 CST
  • 4

当我在 Azure SQL 数据库上发现一些奇怪的东西时，我正在学习 RCSI。

DMVsys.dm_tran_version_store_space_usage始终将 0 报告为版本存储使用的空间，即使我之前运行了 CRUD 工作负载。

为了演示这种行为，我创建了一个小测试。

-- Server info
select @@version as sql_version;

-- Database info
select
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
from sys.databases
where database_id = db_id();

-- Just to be sure the current database has its version store empty
select reserved_page_count as pre_workload_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

-- Test workload
drop table if exists RCSI_TEST;

create table RCSI_TEST (
    id uniqueidentifier default newid()
);
go

insert into RCSI_TEST default values;
go 100

update RCSI_TEST
set id = newid();

delete from RCSI_TEST;

-- Metrics
select reserved_page_count as post_workload_page_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

waitfor delay '00:01:30'; -- Just to be sure! ;)

select reserved_page_count as post_cleaning_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

我在 SQL Server 2019 Developer Edition（在 Docker 上）和 Azure SQL 数据库（层 S0、10DTU）上运行了这个脚本，结果如​​下。

SQL Server 2019

sql_version
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
8

post_cleaning_space_count
0

Azure SQL 数据库

sql_version
Microsoft SQL Azure (RTM) - 12.0.2000.8     Oct  1 2020 18:48:35    Copyright (C) 2019 Microsoft Corporation

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
0

post_cleaning_space_count
0

SQL Server 2019 似乎表现正确，在工作负载之后立即在版本存储中报告 8 页的数据，然后在一分钟左右后清理。但是，在 Azure SQL 数据库上，使用的空间始终为零！这是正确的行为吗？这是什么意思？

联机丛书报告此 DMV 与 SQL Server 和 Azure SQL 数据库兼容，但还说“以下查询可用于确定 tempdb 中消耗的空间，按SQL Server 实例中每个数据库的版本存储。 ”。据我所知，Azure SQL 数据库在设计上对其父实例的范围确实有限。这可能是根本原因吗？

https://learn.microsoft.com/it-it/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15

我知道 Azure SQL 数据库默认在 RCSI 下运行，并且它们具有基于您获得的层的固定数量的 tempdb 空间，所以我担心用长时间运行的事务或其他进程来填充它，在版本存储太久。但我无法管理我无法衡量的东西，对吧？

azure-sql-database tempdb
Martin Hope
Anton1151
Asked: 2020-05-21 18:11:58 +0800 CST
  • 1

当我们的一些程序尝试使用系统命名的 PK 约束创建临时表时，我们偶尔会遇到错误“数据库中已经有一个名为 'PK__#TempTab__0796211ACE71813B' 的对象”，如下所示：

CREATE OR ALTER PROCEDURE dbo.StoredProc AS
BEGIN
    CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id))
    ...
END

查询 tempdb.sys.objects 会发现数百个 PK__#TempTab__XYZ 形式的约束。许多是几小时前创建的，从那以后就没有被修改过。当我查看 tempdb.sys.objects 时，活动会话非常少，因此很难相信当前有这么多临时表正在使用。我们确实在许多存储过程中创建了许多类似名称的临时表。

我认为临时表缓存在这里负责。这些 PK 约束似乎一直存在于 tempdb 中，直到它们相关的缓存计划被删除。测试表明，禁用临时表缓存（创建统计信息、添加命名约束等）会导致 PK 约束在临时表超出范围时从 tempdb.sys.objects 中删除。触发重新编译和刷新 proc 缓存也会从 tempdb.sys.objects 中清除这些 PK 约束。

我知道系统命名的约束不能保证是唯一的，如本文所述SQL Server 可以在系统生成的约束名称中创建冲突吗？.

我的问题是：

  • 我是否错误地认为临时表缓存会增加临时表上系统命名约束的名称冲突的机会？
  • 计划缓存膨胀是否会导致我在 tempdb.sys.objects 中看到的大量 PK__#TempTab__XYZ 约束，从而增加两个约束具有相同名称的机会？我试图理解为什么我们经常看到这种罕见的错误。
  • 如果我们不能确保临时表上的系统命名约束永远不会遇到命名冲突，我们可以做些什么来减少这些冲突的机会呢？
sql-server-2016 tempdb
Martin Hope
jack
Asked: 2019-10-11 06:33:57 +0800 CST
  • 3

我想改变 tempdb （我正在增加大小）

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10240KB )
GO

这需要我重新启动 SQL Server 吗？

sql-server tempdb
Martin Hope
user3841709
Asked: 2019-05-29 06:56:28 +0800 CST
  • 1

在尝试调查 SQL Server 数据库的一些性能问题时，我注意到 ASYNC_IO_COMPLETION 和 PAGEIOLATCH_* 的等待统计信息似乎很高。在资源监视器中，我注意到 tempDB 经常（每小时至少 1 次）读取/写入大量数据，总计（B/秒）范围为 5GB - 25GB。

tempDB 位于与数据库和日志文件不同的虚拟磁盘上。TempDB 大小为 4 GB。我们的主数据库和日志文件各为 40GB。我已经尝试为此研究性能计数器，但找不到任何我能理解的东西来弄清楚为什么我会得到某些数字。

通过资源监视器，tempDB 的总数据如此之高是不是很不寻常？我们的主数据库每天仅增长约 50MB。我应该寻找什么来找出导致 tempDB 上如此高数据的原因以及这是否导致 I/O 瓶颈？如果最好的方法是使用特定的 perfmon 计数器，我应该查看哪些计数器以及哪些值是好的或坏的？

sql-server-2012 tempdb
Martin Hope
Learning_DBAdmin
Asked: 2019-04-21 23:47:05 +0800 CST
  • 3

几个月以来我一直在努力解决这个问题，我说这不是数据库问题，并将此案例分配给存储和操作系统团队，然后他们将其分配给我。这个问题反复发生，没有任何明确的发生模式。

我检查了这里提出的相同问题，我可以说这不是数据库损坏的问题，因为我正在使用 Ola Hallengren 的脚本进行维护工作，并且每周对用户和系统数据库进行数据库完整性检查（checkdb），并且没有报告任何问题在那里面。

也为类似的问题访问了第二个链接，并且可以确认 tempdb 处于简单恢复状态。

我为数据和日志添加了一个额外的文件，这样如果一个文件不可用，另一个文件仍然可以访问，但是后来我知道 tempdb 的访问是顺序的，所以只有在第一个文件满时才会转到第二个文件：

临时数据库属性

每次发生此问题时，我都会在 Windows 应用程序日志中看到另一个错误，如下所示：

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

存储团队已将这些文件排除在防病毒扫描之外。

这里要注意的一件事 - 我检查了其他系统数据库及其文件位置，可以看到对于 master 和 model，数据和日志文件在同一个驱动器（E 驱动器）中，而对于 tempdb 和 msdb，数据在 E 驱动器和日志中文件在 G 盘，不知道这是否相关。

当作业触发或任何事件被触发时，是否有任何检查系统数据库的顺序？如果它是那个驱动器的问题，那么 msdb 也在同一个驱动器上。

这是一个集群服务器，数据库驱动器在两台服务器中共享，服务器用于共享点应用程序。

Server Version: Windows Server 2012 Standard
SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) 
    Jan  5 2018 22:11:56 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

我们剩下的唯一选择是重新启动服务或故障转移 - 这样做听起来不太好。

感谢您在这方面的任何帮助。

sql-server tempdb
Martin Hope
Frederik Vanderhaegen
Asked: 2019-02-16 06:37:14 +0800 CST
  • 4

今天我正在使用扩展事件解决 SQL Server 2016 SP2 上 TempDB 的问题，并将 mixed_extent_allocation 和 transaction_log 事件添加到我的跟踪中。

我预计 mixed_extent_allocation 不会出现在我的结果中，因为以下查询返回 0 作为结果：

select is_mixed_page_allocation_on  
from sys.databases 
where database_id=2

但令我惊讶的是，这个事件出现了好几次。这之前是一个以 SGAM 作为上下文和操作 LOP_SET_BITS 的 transaction_log 事件。

这让我很好奇，于是查看了TempdB第一个SGAM页面的内容：

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

这是结果的一个片段：

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

这让我得出结论，仍然使用混合范围。我以为SQL Server 2016 只使用统一范围（master、msdb 和 model 除外）。还是我的结论有误？

为什么我仍然看到 mixed_extent_allocation 事件弹出？

sql-server-2016 tempdb