Ronaldo's questions

我想创建一个警报，就像 Brent Ozar 的How to Configure SQL Server Agent Alerts中的警报一样，但是我找不到与我想在 sys.messages 上监视的错误相关的消息，正如sp_add_alert文档所说的那样，我想要的问题监控没有产生任何记录的错误。如果我只使用sp_addmessage，我相信我仍然缺少触发错误的验证。如果我创建一个作业来进行验证，我不需要警报，因为该作业可以自己发送电子邮件。

创建新警报的正确方法是什么？

背景

最近，我环境中的应用程序开始无法在 SQL Server 上连接，我们花了一些时间才注意到问题是由于实例达到了实例的 32k 限制（SQL Server 最多允许 32,767 个用户连接）。连接吞噬者是一个配置错误的应用程序，开发人员现在已经纠正了它，但我不想再次感到惊讶，因为其他一些应用程序导致了同样的情况，因此需要警报。当连接数达到 10K 之类的限制时发出警报会很好，但如果达到 32K 限制就发出警报会有所帮助。

我正在模拟从实例A备份并在实例B上恢复它的情况。我按照文档到Backup to Disk with Encryption。由于我只使用我的桌面来模拟整个过程，所以在备份数据库后，我对使用的证书进行了备份，如下所示：

BACKUP CERTIFICATE MyTestDBBackupEncryptCert
TO FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert';

然后我MyTestDBBackupEncryptCert从我的实例中删除了证书和数据库来模拟它现在是实例B。我使用从文件创建证书doc中的命令恢复了证书，并稍作修改，我在其中注释了与PRIVATE KEY和DECRYPTION选项相关的行，因为证书是由 加密的MASTER KEY（嗯，这就是我从第一个链接中理解的）：

CREATE CERTIFICATE MyTestDBBackupEncryptCert   
    FROM FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert'   
    --WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',   
    --DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');  
GO 

证书已创建，但当我尝试恢复数据库时，出现此错误：

消息 15507，级别 16，状态 30，行 33
此操作所需的密钥似乎已损坏。
消息 3013，级别 16，状态 1，第 33 行
RESTORE DATABASE 异常终止。

该文档没有说明如何获取正确恢复证书所需的私钥。如何从 SQL Server 获取它？

当插入 100 行时，尝试sp_stop_job停止在表上插入数据的简单作业，我很困惑地看到表每次都超过 100 行。在文档的备注部分下，sp_stop_job它说：

sp_stop_job向数据库发送停止信号。有些进程可以立即停止，有些进程必须到达稳定点（或代码路径的入口点）才能停止。

我不明白达到稳定点（或代码路径的入口点）意味着什么，我相信这项工作会一直在表上插入数据，因为它仍在达到那个稳定点。

编码：

CREATE TABLE [dbo].[myStopJobTable](
    [id] [int] NULL,
    [nome] [varchar](50) NULL
);

我创建了一个包含执行以下代码的步骤的作业：

WHILE (1=1)
BEGIN  

    INSERT INTO MyLab.dbo.myStopJobTable
    VALUES (1, 'Some Name');

    IF ((SELECT COUNT(*) FROM myStopJobTable) = 100)
    BEGIN
        EXEC msdb.dbo.sp_stop_job N'JobToBeStopped';
    END

END;

从该示例代码中，我希望该作业在插入第 101 行之前停止，但事实并非如此。BREAK通过在块内添加 a ，IF该过程在插入第 101 行之前停止，但在这种情况下它sp_stop_job不起作用。

我不想修复这项工作，我寻求的是理解那个稳定点（或代码路径的入口点）。这是什么意思？

我正在使用函数DATABASEPROPERTYEX来检查数据库的恢复属性，但是数据库的结果是错误的AdventureWorks（后来我注意到ReportServer和ReportServerTempDB数据库的同样奇怪的行为）。

这是我尝试过的简化版本：

USE master;
GO

CREATE PROCEDURE [dbo].[sp_isSimpleRecovery] 
AS
BEGIN
    PRINT DB_NAME() + CHAR(13);

    IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
        PRINT '--Database in SIMPLE recovery model' + CHAR(13);
    ELSE
        PRINT '--Database in ANOTHER recovery model' + CHAR(13);
END

我创建了master使用前缀 sp_的过程，以便可以从任何数据库运行它。但是当我从AdventureWorks结果中运行它时，与没有该过程运行的相同代码不同：

USE AdventureWorks2016;
GO

EXEC sp_isSimpleRecovery;

PRINT DB_NAME() + CHAR(13);

IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
    PRINT '--Database in SIMPLE recovery model' + CHAR(13);
ELSE
    PRINT '--Database in ANOTHER recovery model' + CHAR(13);

它适用于许多其他数据库，但对于 AdventureWorks，输出为：

AdventureWorks2016

--另一种恢复模式中的数据库

AdventureWorks2016

--简单恢复模型中的数据库

是什么导致了这种行为？

我使用的AdventureWorks 示例数据库：

• AdventureWorks2014.bak
• AdventureWorks2016.bak

SQL Server 版本：

• Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64) 2019 年 12 月 31 日
• Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64) 2020 年 10 月 31 日

如何根据执行后报告的读取次数来衡量查询与可接受的阈值（关于性能）的距离？

示例：为了让自己清楚，如果我们谈论的是汽油里程为 40 mpg 的汽车的每加仑英里数，并且我们必须行驶 40 英里的距离，那么最佳性能的阈值是 1 加仑行驶那个距离。因为不是每个人都像Max Verstappen 或 Lewis Hamilton那样开车，如果你是一个糟糕的车手，你可以花 1.3 甚至 2 加仑的油来跑那个距离。但是如果你花 30 加仑去一个 40 英里外的地方，我可以说你肯定走错了路。

背景：有时我运行sp_whoisactive只是为了找到一个报告类似 1,248,909 次读取的查询（因为每页是 8 KB，这意味着从4.4 GB的数据库中处理了大约 9.7 GB 的数据）。这意味着一个查询读取整个数据库两次。当我看到类似的东西时，我的直觉告诉我有些地方出了问题，但开发人员有时会争辩说“这是一个复杂的查询，它应该表现得像那样”。然后我必须改进查询，以证明即使复杂的查询会消耗更多资源，但有一个阈值表明您偏离了轨道。

是否有一个公式可以根据所涉及的表数量、连接类型、函数的使用等来计算它？如果没有，是否有经验法则可用于进行合乎逻辑的论证？

在 2020 年 11 月 23 日配置 XE 以捕获一些错误后，我可以统计 2,163,665 次来自旧版应用程序请求的相同错误（可能不会很快得到纠正）。这些错误可以在处理 SQL 语句的第一步和第二步进行分类：

1. DBMS 首先解析 SQL 语句。它将语句分解成单独的单词，称为标记，确保语句具有有效的动词和有效的从句，等等。在此步骤中可以检测到语法错误和拼写错误。
2. DBMS 验证该语句。它根据系统目录检查语句。语句中命名的所有表是否都存在于数据库中？是否所有列都存在并且列名是否明确？用户是否具有执行语句所需的权限？在此步骤中可以检测到某些语义错误。

该文档还说，解析 SQL 语句不需要访问数据库并且可以非常快速地完成，但是在服务器上发出的请求数量让我觉得我应该验证一下。

我想衡量那些在这些步骤中被拒绝的错误请求对资源消耗的影响。有没有办法这样做？

查询优化器创建多个可能的执行计划。如何查看在选择执行的计划之前生成的所有计划？

微软说：

查询优化器必须分析可能的计划并选择估计成本最低的计划。

请注意，我不是在谈论执行计划缓存和重用，而是在谈论候选执行计划，即那些已生成但未选择执行的执行计划。

根据 Benjamin Nevarez 的文章The SQL Server Query Optimizer：

候选执行计划的生成是在查询优化器内部使用转换规则执行的，启发式方法的使用限制了考虑的选择数量，以保持优化时间合理。候选计划在优化期间存储在内存中的一个称为备忘录的组件中。

是否有可能以我们可以显示实际执行计划的方式可视化呈现的候选计划？

据我所知， SQL Server System Databases始终具有相同的 ID，并且我在 Internet 上看到许多维护脚本依赖谓词WHERE database_id > 4将它们从脚本的操作中排除。

另外，如果我SELECT name, schema_id FROM sys.schemas;在一个新的用户数据库上运行，我会得到：

name                 schema_id
dbo                  1
guest                2
INFORMATION_SCHEMA   3
sys                  4
db_owner             16384
db_accessadmin       16385
db_securityadmin     16386
db_ddladmin          16387
db_backupoperator    16389
db_datareader        16390
db_datawriter        16391
db_denydatareader    16392
db_denydatawriter    16393

我在两个不同的实例上运行该查询，一个是 SQL Server 2016，另一个是 SQL Server 2005，两者都返回了相同的结果。

问题：

1. 是否存在系统数据库 master、tempdb、model 和 msdb 的 database_id 分别不是 1、2、3、4 的情况（或 sql server 版本）？
2. 我真的可以相信我列出的架构在任何 SQL Server 实例上总是具有相同的 ID，以便我可以根据这些 ID 编写维护脚本吗？

检查SQL Server 2014 构建版本以确定适合我的服务器的更新，我对使用什么更新有点困惑。做了一些研究，发现我应该应用最新的服务包和累积更新。好的，但还有一个问题：

我目前安装了 SP2，并且有一个用于 SP2 的 CU，发布日期为July 29, 2019，但 SP3 已发布October 30, 2018。

在我看来，SP2 的 CU15-CU18 不应该存在。（我知道我错了，但我不知道为什么）
服务包中有什么可能不想安装但会安装在最新 SP 之后发布的其他 CU？

愿意解决与数据库属性错误值相关的这个问题，我is_media_read_only做了一些研究和测试，但最后我无法弄清楚究竟是什么触发is_media_read_only了sys.database_files.

根据sys.database_files文档，该列is_media_read_only应具有以下两个可能值之一：

1 = 文件在只读媒体上。

0 = 文件在读写媒体上。

有了这些信息，我对两个不同版本的 SQL Server 进行了以下实验：

Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1 (X64)
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

在我的笔记本（Drive E:）中插入一个笔式驱动器并创建一个数据库，如下所示：

CREATE DATABASE [MyDB]
 ON  PRIMARY 
( NAME = N'MyDB_01', FILENAME = N'D:\DataBases\MyDB_01.mdf'), 
 FILEGROUP [SECONDARY] 
( NAME = N'MyDB_02', FILENAME = N'E:\DataBasesPendrive\MyDB_02.ndf')
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'D:\DataBases\MyDB_log.ldf')
GO

查询 sys.database_files：

USE MyDB;
GO
SELECT file_id, name, physical_name, is_media_read_only, is_read_only, state_desc  
FROM sys.database_files;
GO

结果是这样的：

创建数据库后，我打开 CMD 提示符并运行diskpart. 在该实用程序上，我发出了以下命令：

list disk
select disk 2
attributes disk set readonly
attributes disk

磁盘 2（我的笔式驱动器）现在是只读媒体，所以我预计is_media_read_only值会改变，但是当我sys.database_files再次查询时没有任何变化：is_media_read_only 仍然是 0。所以我开始对数据库进行随机程序，看看是否任何事情都会让 SQL Server 注意到数据库现在位于只读媒体上并将值更新为 1。我可以在两种情况下实现这一点：

第一个：将数据库更改为 READ_ONLY 模式并返回READ_WRITE. （它必须是两个动作，只有其中一个不会起作用）：

USE master;
GO
ALTER DATABASE MyDB SET READ_ONLY;
ALTER DATABASE MyDB SET READ_WRITE;
GO

2nd：分离并重新附加数据库。

正如您在图片中看到的那样，这是我可以is_media_read_only更新的两种情况：1

现在是时候将笔式驱动器再次恢复为 read_write 了，所以我回到diskpart并运行：

select disk 2
attributes disk clear readonly

is_media_read_only当我再次将磁盘 2 更改为读写模式时，只会0在分离和附加过程中更新。我什至尝试重新启动 SQL Server 实例，希望它能更新值，但没有运气。

直到数据库的分离和附加 SQL Server 会给我留下价值1，is_media_read_only即使磁盘不再是READ_ONLY.

因此我的问题是：究竟是什么触发了UPDATE该列is_media_read_only的sys.database_files？此实验显示的行为是否可能是 SQL Server 的错误？

我的问题与以下两个实例的实验有关：

SQL Server 2017 Express 实例 (Microsoft SQL Server 2017 (RTM-CU16))
SQL Server 2014 Express 实例 (Microsoft SQL Server 2014 (SP2-CU18))

我使用函数ENCRYPTBYPASSPHRASE加密文本并将结果用作 DECRYPTBYPASSPHRASE 的@ciphertext。我的测试结果如下：

根据这个微软修复，

[...] SQL Server 2017 使用 SHA2 散列算法来散列密码。SQL Server 2016 和更早版本的 SQL Server 使用不再被视为安全的 SHA1 算法。

但是，如果函数 DECRYPTBYPASSPHRASE 上没有与该算法相关的参数，它怎么知道用于加密数据的算法是什么？它是加密数据的一部分吗？

根据我的测试结果，我猜 SQL Server 总是使用实例上可用的较新版本的算法来加密数据，但会尝试所有算法来解密数据，直到找到适合的算法或在找不到相应算法时返回 NULL . 这只是一个猜测，因为我找不到任何方法来检查 SQL Server 用来解密加密数据的散列算法。