Newbie-DBA's questions

我们正在尝试解决其中一个应用程序的连接问题，该应用程序试图从防火墙后面的非常安全的 sql 服务器中提取数据。我不是安全方面的专家，但需要有关如何继续为该应用程序打开端口以从安全域后面的 sql server 中提取数据的帮助。

我们已经打开了 sql server 正在侦听的端口，比如说 12345，但仍然没有运气。

我怎么知道可能需要打开哪些额外的端口，如 udp 1434 默认 1433 或镜像 5022？有没有办法找到这些信息？

有人可以帮我弄清楚我对以下内容的理解是否正确：

在我的可读辅助副本的 AG 仪表板上，我看到：

• 预计恢复时间（秒） - 4598
• REDO 队列 - 将近 24 GB

那么，如果我的辅助 AG 需要作为活动的一部分进行节点/故障转移或 SQL 重新启动，这到底意味着什么？

这是否意味着我的辅助将需要 4598 秒才能使用重做队列 24 GB 来启动这个数据库？

我很担心，因为我们的 prod sec 方面的大部分时间在白天重做大小为 400 GB 和从 AG 仪表板恢复时间近 10 小时。这是否意味着所谓的 DR 受到损害？

我刚刚进行了测试故障转移，正如我从错误日志消息中看到的那样，DB 进行了恢复，并看到它在 1235 秒内完成。只是好奇，因为估计恢复的数量还很遥远。这只是为了解释我的业务用户帮助他们我们正在谈论什么中断窗口。

我对网络重新计算数据库大小和可用空间的几个脚本感到困惑。

Web 上的大多数脚本都使用 sys.databases_files dmv 来计算可用空间，例如此处和此处

但是，如果我们通过 GUI 从 sp_spaceused 或数据库属性中看到一个可用空间量是不同的。我得到它是差异的原因，因为它在 (0,2,4) 中的类型上有过滤器，它不包括日志文件。

但我无法理解哪一个是正确的以及为什么日志文件被排除在 sp_spaceused 之外，还是 web 上的脚本不正确并显示其他内容？

由于我们有可读的辅助配置，我们正面临 REDO 队列波动的问题。

根据我对 SQL Server 较新版本的理解，重做线程是并行的。当前版本是在具有 64 个内核的 VM 上运行的 SQL Server 2017。

当在运行 AG 线程的同一个 cpu/调度程序上运行辅助选择查询时，我看到了问题。我假设 AG 线程产生它的调度程序并退居二线让其他查询运行。我相信这是问题所在，因为进来的查询似乎在 AG 线程正在执行重做工作的地方遇到相同的 cpu_id。

下面的示例：spid 91 是对 cpu_id 6 的选择查询，同时 spid 123 和 144 正在为 AG 运行线程，如从 sys.dm_exec_requests dmv 中找到的。当 91 出现时，REDO 队列开始建立。

我不知道是否有任何进程使您的查询转到不同的内核，而不是 AG 线程可能正在运行的内核。

1. 我们可以控制它以使查询命中其他调度程序而不是 AG 工作的调度程序吗？

2. 当 AG 线程挂起时在辅助节点上运行查询时，我看到 PARALLEL_REDO_TRAN_TURN 上有很多等待。可以跟踪此处提到的标志帮助

3. 可能还有许多其他可用的调度程序。我无法理解为什么某些进程卡在 Numa 的插槽中。我想较新的版本启用了所有软 numa，因此可以制作 8 个软 numa 桶。8*8 调度器，64 可用。在这里禁用软 numa 是个好主意吗？

数据库隔离是 RCSI，但我认为这并不重要，因为在内部它已根据可读辅助节点的设计更改为快照。

我们最近从 LogShippingstandby/read-only设置迁移到具有可读辅助节点的 Multi Subnet AG 设置。

通常，在旧设置中，我们会选择运行更长时间的查询，因为相关数据库超过 20 TB，并且在主数据库上混合了读写工作负载。

转移到 AG 的新设置后，我们开始看到我无法理解的阻塞。为什么辅助上的选择查询会阻止我可读辅助副本实例中的其他选择查询，即使正在查询的数据库有RCSI enabled？

下面是我捕捉到的

• Lead blocker 是一些长时间运行的SELECT查询，没有显示任何特定的等待类型，比如 SPID129

• SPID 129阻止会话 ID 45（我确定这不是用户 ID）将近 6 小时，这取决于 spid129 并且等待类型是 LCK_M_SCH_M

• 当这SPID 45只是在 6 小时内阻止所有其他选择查询时，问题就来了。

我无法理解发生了什么。有人可以帮我排除故障或寻找正确的方向吗？

我们最近报告的磁盘写入延迟有所增加。虽然我们的监控团队没有确认来自哪个驱动器，但在进行一些 dmv 查询时，我看到tempdb平均等待写入时间约为 32 毫秒。同样在一周的tempdb使用情况下，我的意思是可用的可用空间已从 300 GB 下降到 50 GB 以下。

我怀疑这个尖峰可能来自tempdb. 该环境是 OLTP 和仓库人员在查找实时数据时查询数据以进行大型选择的混合，以防 LS 备用数据库不可用。

我的问题是如何检查tempdb是否会导致此峰值？

这些选择查询也会导致tempdb写入延迟吗？这些选择查询在超过 TB 数据的表上运行，用户可以根据我所看到的情况进行部分查询，例如 order by 和 group by。但我不确定如何证明它们是导致突然飙升的原因。

我知道这是在各种论坛上讨论最多的答案：我完全理解在大多数情况下答案是否定的。

但是想知道在任何情况下它是否都是好方法：

假设当前的产品数据库在我们的例子中超过 30 TB，并且数据保留了 1 年。表大多是压缩和分区的。

开发团队提出了他们不需要数据来保持长达一年的逻辑，并希望将保留期更改为 6 个月。因此，预计清除后数据将减少一半，并且只有 6 个月的数据。

这是我们被要求缩小数据文件的地方，以便可以返回那些具有 TB 空间的 LUN，或者如果我们迁移到新的 sql 版本，我们会要求让我们说少一些（大约 10）TB，这样可以节省一些钱，假设我们为未来的数据库增长保留了 5Tb 的额外空间。

在这种情况下，如果我们假设在低峰值时间阻塞可以被接受或者如果收缩运行更长的时间可以被取消，那么我们在小块中进行收缩会有所帮助吗？

上述收缩数据文件活动的价值是多少，还是有更好的方法？

编辑 --> 我们正在迁移到最新版本，可能是 SQL 2017 或 SQL2K19。这些表不是基于文件组进行分区的，因为对于这个数据库，所有 30 多个文件都在主数据库上。我知道，它只是一个供应商数据库设计，我手里没有太多东西。

我尝试浏览很多链接，但无法获得太多帮助，所以我发布了这个听起来有点相似的问题。

我们的应用程序团队正在计划一些表格，他们将在接下来的几个月内停止发送数据。因此，这些表将成为稍后清理的好竞争者。

但是，如果 SQL 服务器中有方法，我怎么能找到一些我们不知道的其他用户可能正在使用该表进行 XYZ 操作。

是否有本机可用的方法或者我需要 XE 来跟踪它？这是一个自始至终高度活跃的 OLTP 数据库服务器，因此运行任何审计都需要进行测试。

应该采取什么方法以及如何实际处理这种情况？请建议。

谢谢！

我有以下 SQL 查询，它在 avg 上运行。40 秒，需要一些指导，我可以在哪里改进或重写：

   Declare @status_D INT,
      Declare @StatusIP INT,
      Declare @Status_F INT,
    
    SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
    SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
    SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

 UPdate U
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

  COMMIT TRAN 

我收到来自 blitzcache 的警告，因为强制序列化、昂贵的排序、不可分割和合并加入 "Mant to Many" = True

我们唯一的索引是在 id 上，它是一个以 id 作为前导键列的 NC 索引。

如果可以的话，我会尝试更新我的问题，如果执行计划。截至目前，SELECT 中的表约为 1 亿。在 col1 上也有 PK/CI。

我已经问过用户为什么我们在那个拉近 50 多列但在更新中使用较少的 TOP 中做 *，可能 bi 想错了，但我想到了。

将索引添加到修改时间似乎也无济于事。

请建议还有什么可以检查的。谢谢

计划-索引搜索发生在 NC 索引 id 上

我遇到了一个运行速度非常慢的 proc 大约一个小时的问题：

请原谅，因为我必须从系统中写出整个代码才能粘贴到这里。当我复制该信息时，我将使用有关表架构和索引的更多详细信息来编辑问题：

SP 如下所示

CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
 SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2

bigtable 大约有 800 M 行，并在 id 上聚集为 PK，在 userid（第一个键）上聚集为 NC，tabletypeid（第二个键）

anotherbigtable 大约是。300 M 行

@parameter2大多数时候接收为 NULL 而@parameter1 变化

缓存计划如下所示

UPDATE需要一些帮助来理解以下语句之一的缓慢性：-

UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK)
on xyz.id=abc.id
WHERE xyz.flag = 0

表1有大约。50 万行，表 2 大约有 50 万行。500 万行

慢计划

Hash Match distinct flow operator 显示黄色警报，消息为：

操作员使用 Tempdb 溢出数据以执行溢出级别 4 和 1 个溢出线程"

构建残差：

database.dbo.table2.id as abc.id = database.dbo.table2.id as abc.id

我截图了。不幸的是，由于安全原因，我无法提供更多信息，甚至没有匿名计划。从我的工作站我无法访问互联网，所以我无法让计划资源管理器在那里运行。

通常对于较小的行子集，它低于 sec，就像我们刚刚匹配 10K 行或其他东西一样。但是随着数据量的增加，这似乎是一个临界点，应用程序无法承受 1 分钟的运行时间。从 SSMS 我得到 30 秒，但从应用程序我们有 avg。约 50 秒 RCSI 处于测试阶段。

我的好计划没有显示 Hash Match Flow Distinct 运算符，如我的屏幕截图所示，而其余计划保持不变。好的一个在 3 秒左右完成。正如所见，该运算符花费了近 16 秒。我们可以通过适当的索引或查询重写来消除它吗？

表架构

CREATE TABLE dbo.table1
(
    Recid  VARCHAR(128) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    Cdate DATETIME NULL,
    flag BIT   NULL DEFAULT (0),
    Rcode INT NULL,
    EDR VARCHAR(255) COLLATE SQL_Latin1_general_CP1_CI_AS NULL,
    id BIGINT NULL
);

CREATE TABLE dbo.table2
(
    ENID BIGINT IDENTITY(1,1) NOT NULL,
    EID VARCHAR(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CID VARCHAR(350) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CDate DATETIME NOT NULL DEFAULT(getdate()),
    id BIGINT NOT NULL,

    CONSTRAINT PK_ENID PRIMARY KEY (ENID ASC, EID ASC),
);

-- table1
CREATE INDEX ix_Cdate on dbo.table1 (Cdate) WITH (FILLFACTOR=100);
CREATE CLUSTERED INDEX ix_Recid on dbo.table1 (Recid) WITH (FILLFACTOR=80);

-- table2
CREATE INDEX ix_ENID_id on dbo.table2 (ENID,id) WITH (FILLFACTOR=100);

变化

我所做的更改和一些数字：

• 添加提示OPTION (QUERYTRACEON 4138)- 平均。执行比原来的 50 秒减少了 7 秒，但应用程序团队似乎无权在代码中执行此操作。需要进一步检查这一点。

• OPTION (ORDER GROUP)给出了相同的平均结果。50秒，所以那里没有改善。

• 按照建议添加索引：

CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);

那里没有太大的改进。平均 45 秒，计划与此问题中所附的类似（顶部计划）。

在每次测试之前和之后，我确保计划不是从以前的缓存计划中生成的。

快速计划

对于两个表中相同数量的行，附加更快且数据或查询没有任何更改的计划仍然很快。应用程序团队全天不断提交上述查询，以通过完成前 100 名来完成批次。有一个基于一些小费数字的计划更改，以下是好的计划的外观：

编辑： - 一切都没有改变，没有代码更改或添加任何索引，正如我尝试添加提示（FORCESEEK）时所建议的那样，它给了我以下错误

由于此查询中定义的提示，查询处理器无法生成查询计划。在不指定任何提示且不使用 SET FORCEPLAN 的情况下重新提交查询。

我正在尝试使用 MAXDOP 设置了解 DBCC CHECKDB 的使用情况-

如果我在我的 MAXDOP 设置为 8 的 SQL 实例上运行 MAXDOP = 0 的 DBCC CHECKDB，实际上发生了什么？我所有可用的说 98 调度程序都会被使用吗？还是只有 8 个？还是只有少数？

如果我在没有 MAXDOP 的情况下运行 DBCC CHECKDB 说服务器级别 MAXDOP 8 的默认值，我应该看到所有 8 个调度程序都使用了吗？或者可以小于 8

为什么我提出上述问题是针对某些服务器的相同 CHECKDB 运行它似乎使用较少的调度程序和一些具有相同运行所有调度程序的服务器导致 100% CPU 峰值

谢谢

下面是更多关于如何解释来自我的性能计数器的数据的更多信息，用于 2 个具有不同应用程序结构但工作量相似的数据库服务器

服务器 A 共有 1 TB RAM，数据库约为 10 TB，通过 4 个 NUMA 节点配置具有以下指标

服务器 B 共有 1 TB RAM，数据库约为 16 TB，通过 4 个 NUMA 节点配置具有以下指标

由于数据库的大小很大，服务器 B 分配了更多的 MAX 内存。是的，我知道 MAX 内存设置可以改进一点点，但如果我能更好地理解上述数字来了解被盗和外来内存，我会计划稍后。

这些数字告诉我什么，因为我看到一台服务器的外来内存似乎约为 50 GB，而在另一台服务器上被盗为 150 GB，而外来似乎只有 5 GB。

对不起，我在这里缺乏知识，但有人可以指导更好的方法来解释这些数据以及人们可以想到在这里调整哪些可能的变化？

我是第一次设置 AG，所以经验很少或没有经验，请指导我了解以下内容：

如果我在以下情况下设置 4 节点 AOAG：

Node1 -NY 数据中心 - 主副本

Node2 -NY 数据中心 - 辅助副本

Node1---Node2同步模式

节点 3 - 密歇根数据中心辅助副本充当具有不同子网的 DR 站点，采用 ASYNC 模式

节点4——新泽西州数据中心的可读辅助节点，用于使用异步模式进行报告

疑问/问题：

1. 在上面的多子网配置中，我是否仍然可以跨 4 个节点使用不同的存储，或者我是否需要使用 SAN 复制，因为我已经阅读了一些博客并且混淆了这是否是多站点环境的要求

2. 什么应该是理想的法定人数设置？-- 我可以在 NJ 网站上将其作为文件共享见证吗？

另外，如果有比上面更好的设计，请提出建议，因为我以前从未实施过或在 AOAG 上工作过。

关键应用程序的 RPO/RTO 在 2 到 4 小时之间变化

而对于中期关键，它在一天到两天之间

我可以根据需要添加信息。

谢谢

只是检查一下是否有人目睹了相同的情况并尝试修复或已修复：

问题：

stackoverflow2013我在数据库中运行了以下查询

select
   ParentId as [Post Link],
   count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;

当检查 GrantedQueryMemory 的 SSMS 仪表板报告时，该值显示大约 62 GB，这在从 dmv 收集时似乎不正确

从 dmv 显示 62 MB

在我看来它像一个错误所以报告在这里

我在这里寻找专家关于如何管理大约 18 TB 的超大型数据库的更新统计信息的建议。

我们最近开始面临性能问题，并认为这是由于旧的统计数据造成的。

实际上，我们有一个运行 exec sp_update stats 并以默认采样率更新的作业，在我们的例子中是 1.2%。所以我们必须手动更新统计数据并看到一些改进。

我确信安排 FULL SCAN 将是一个挑战。据我所知，我正在将行与采样的行进行比较。例如，在一张大小为 400 GB 且行数超过 100M 的表上，我可以看到采样行约为 2 到 4M。大表是分区的。

我们使用的是 SQL Server 2012 企业版。未启用跟踪标志 2371。

请建议我如何以更好的方式为如此大的数据库利用更新统计信息以及如何使用该采样率？

我会尽力描述情况，而我们一直在努力解决这个问题：

我们有这个 SQL 登录 [LA]，它在 sql 错误日志 [从各种应用程序建立的连接] 中显示主体服务器上的成功连接。此登录在其镜像服务器上也显示成功连接。

现在，这是另一个登录名 [LB]，它在主服务器上成功连接，但在镜像服务器上连接失败。[同上登录LA连接的应用]

故障排除完成：

1. 在两台服务器上运行 sp_help_revlogin 并匹配登录 [LB] 的散列密码。因此，就主体和镜像的 SQL 登录而言，确认此登录的密码相同。

2. 两台服务器的登录 SID 相同，因此这也不成问题。

3. 应用程序使用相同的凭据在主体上连接成功，但在镜像服务器上显示登录失败。

由于某些原因导致登录尝试失败，因此登录一直被锁定。

在将此作为应用程序问题传递给应用程序所有者之前，我作为 DBA 还可以检查什么？

请帮我排查问题的根源，谢谢。