J. Mini's questions

我经常发现 的输出具有sp_spaceused误导性。数据库中所有数据的大小实际上是数据文件和日志文件的总大小。我最近发现，它在各方面都更胜一筹。它比我计划喝酒直到忘记该过程sys.database_files要好得多。sp_spacedused

这给了我一个问题：检查数据库大小时，是否有任何 提供的功能或返回值sp_spacedused无法从 获得sys.database_files？我已检查过文档，我很确定没有。

Pro SQL Server 关系数据库设计和实施：可扩展性和性能的最佳实践是优秀数据库设计理念的可靠来源。作为实体-属性-值的替代方案，它建议使用动态 SQL 来允许用户向表中添加新的稀疏列。

对我来说，这个想法很糟糕。添加新列需要架构修改锁。这是一个非常严重的锁，我宁愿我的用户无法获得它。

稀疏列是否有任何属性使得允许用户采取如此严重的锁定并不像其他类型的列那样可怕？

READ COMMITTED在（悲观类型，不是 RCSI）下运行时，我刚刚看到UPDATE的 IX 锁死锁，其中SELECT的共享锁位于PAGE-level。SELECT使用并行执行计划运行。两者都没有明确使用事务。我理解UPDATEs 隐式创建事务。这是否表明并行SELECTs 也会创建隐式事务？对于非并行SELECTs 来说，情况不是这样吗？

我发现这相当令人困惑，因为教科书中的死锁例子都没有显示两个无事务查询死锁。

我到处都使用备份压缩。最近，我发现我的备份有点太大了。我决定通过压缩数据库中的索引（通常是PAGE压缩）来解决这个问题。数据库变小了很多。但令我惊讶的是，完整备份并没有变小！

这是预期的行为吗？

我显然有一个误解，但无论多少咨询文档都无法解决这个问题。考虑以下论点：

1. 我们通常担心加入堆，因为对它们进行排序通常比拥有聚集索引更昂贵。
2. 哈希连接不需要排序。
3. 从上面我们得出结论，如果您始终可以获得（或强制）哈希连接，那么您就不必担心加入堆。

大众对堆的鄙视表明上述说法是错误的，但错在哪里呢？

假设我的服务器有一项任务，有时会根据我无法控制的因素（例如碎片）重建索引。还假设我确信它有一天会重建我想要更改的索引。有没有办法指示 SQL Server 我想WITH在下次重建时更改索引，而不是现在重建它？

例如，假设PK_HugeTable设置为填充因子 25。下次重建时，我想将其设置为 100。如果我不想现在重建索引，我该怎么办？

ALTER INDEX [...] SET适用于某些选项，但不适用于填充因子。

我有一个未压缩的聚集主键。它在聚集索引的主要部分中消耗了 63.6 GB，其中 LOB 为 17.9 GB。该表的唯一非聚集索引为 57.3 GB，其中 LOB 也为 17.9 GB。我希望在数据库不增长的情况下离线重建聚集索引。sp_spaceused不带参数运行会在其“未分配空间”列中报告 66 GB。

我想要做的事情可以实现吗？或者重建压缩索引是否会强制重建非聚集索引，从而占用至少 100 GB？

尽管我尽了最大努力，但我在文档或 Stack Overflow 中都没有找到答案。我对手边的一个小型数据库进行的实验表明，重建聚集索引不会强制重建非聚集索引。

微软的一篇旧论文ROW说，如果您有足够的 CPU 空间，请考虑默认使用压缩（重点是我的）。

如果行压缩可以节省空间，并且系统可以承受 10% 的 CPU 使用率增长，则所有数据都应进行行压缩

如今，我们的 CPU 空间比撰写本文时还要多。智者曾说过，PAGE除非有令人信服的理由不这样做，否则我们应该考虑在任何地方使用压缩。

这些都是很好的建议，我经常看到sp_estimate_data_compression_savings有人同意。但是，对于经常访问的微型表，应该怎么做呢？例如，我有一些非常小的维度表。最多 100 行，列很少。因为它们太小了，所以任何压缩带来的空间节省效益都很小。在有大量可用 CPU 空间的盒子上应用ROW或压缩这些微型表的最佳实践是什么？PAGE

就这个问题而言，请忽略列存储。我们只讨论基于磁盘的表上的老式行存储索引。

假设我有一张表，其中有许多我不关心的列，但有两列我很关心：Primary和Secondary。 上有一个聚集主键Primary。

CREATE TABLE [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary] INT PRIMARY KEY CLUSTERED,
    [Secondary] NVARCHAR(500) NOT NULL,
    [Evil1] NVARCHAR(MAX),
    [Evil2] NVARCHAR(MAX),
    [Evil3] NVARCHAR(MAX),
    [Evil4] NVARCHAR(MAX),
    [Evil5] NVARCHAR(MAX)
);

因为我不关心任何其他列，所以我决定我想要这个索引：

CREATE NONCLUSTERED INDEX [IX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

鉴于此索引的键列表包含主键，因此毫无疑问该键列表是唯一的。因此，我很想这样做UNIQUE。

CREATE UNIQUE NONCLUSTERED INDEX [UIX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

但这有什么好处吗？如果情况完全相同，但以 作为Secondary前导索引键，后面跟着 ，情况又如何呢Primary？

下列行为是违法的。

SELECT
  schema_id + 2 AS evil_column
FROM
  sys.tables
ORDER BY
  evil_column + 3;

如果删除+ 3，那么它是合法的。

假设我想对结果进行算术运算evil_column，ORDER BY但不想在evil_column任何地方重复定义，我有什么选择？

我一直被告知，它REORGANIZE无法解决页面填充率低的问题，因为它所做的只是重新排序磁盘上的页面。为了调查这个问题，我阅读了文档。然而，我发现它自相矛盾。优化索引维护以提高查询性能并减少资源 消耗

重新组织还会压缩索引页，以使页密度等于索引的填充因子。

以及支持此的文档ALTER INDEX

重组还会压缩索引页。压缩基于现有的填充因子值。

但这与填充因子的文档相矛盾

仅当首次创建或重建索引时才应用显式FILLFACTOR设置。数据库引擎不会动态保留页面中指定的空白空间百分比。

那么，到底是怎么回事？REORGANIZE压缩页面并尊重填充因子吗？还是只是移动叶子页面而不进行压缩？

我的实验表明，Query Store 不会跟踪扩展存储过程。但是，它们各自都有各自的行为，我甚至不是扩展存储过程方面的初学者，而且我只尝试过几个。我寻求一种确定的方法来了解 Query Store 是否可以跟踪任何扩展存储过程。

下列任何一项是否存在？

• 查询存储根本无法跟踪任何扩展存储过程的架构原因（例如，我发现我尝试过的那些没有查询计划，但我不知道所有扩展存储过程是否都是如此）。
• 文档明确指出查询存储不跟踪任何扩展存储过程。我找不到任何文档。
• 证明查询存储可以跟踪至少一个扩展存储过程的代码。

我有一个包含不合理大全文索引的数据库。DBCC CHECKDB WITH PHYSICAL_ONLY在这个数据库上大约需要 25 分钟，但DBCC CHECKDB WITH DATA_PURITY, EXTENDED_LOGICAL_CHECKS多花了大约 2 个小时。这建议我PHYSICAL_ONLY跳过全文索引。

我说得对吗？需要传递哪些参数才能CHECKDB让其检查全文索引？

观察结果

文档说

BACKUP要在(Transact-SQL) 语句中启用备份校验和，请指定该WITH CHECKSUM选项。要禁用备份校验和，请指定该WITH NO_CHECKSUM选项。这是默认行为，压缩备份除外。

换句话说，启用压缩即可启用校验和。

然而，在我的维护计划中（不幸的是，它们是 GUI 计划，而不是 Ola 的酷计划），我可以看到：

• 主菜单中的“备份压缩（开启）”
• 在备份数据库任务的属性菜单中，“BackupCompressionOption”设置为“On”，“Checksum”设置为“False”。
• 在修改菜单中勾选“验证备份完整性”，选择“压缩备份”，并取消勾选“执行校验和”。

问题

这是否意味着即使我明确要求备份压缩，校验和仍被禁用？或者如果启用了压缩，校验和的复选框/属性是否是多余的？

我尝试过的方法

• 每次尝试“查看 T-SQL”都会导致 SSMS 崩溃。
• 尽管我已在每个数据库上启用了它（我搜索sys.query_store_query_text了like '%backup database%'），但在查询存储中查找 T-SQL 失败。
• msdb.dbo.backupset0在其所有列中都有has_backup_checksums，但我不知道压缩是否会使该列变得无用。

DBCC CHECKDB使许多其他完整性检查变得多余，但我在文档中看不到任何内容说明它是否DBCC CHECKFILEGROUP多余。如果您已经在所有数据库上DBCC CHECKFILEGROUP运行，它是否毫无价值？DBCC CHECKDB

我有一张表，如下所示。我们将其命名为Fools。

业务规则是，对于每个FoolID，我们只能在一行上IsActiveFoolValue设置1。我需要某种键或约束来强制执行这一点。但是，据我所知，键和约束无法做到这一点。唯一的工具是唯一索引，例如

CREATE UNIQUE NONCLUSTERED INDEX OnlyOneActiveFoolValuePerFoolId
ON Fools(FoolID)
WHERE IsActiveFoolValue = 1 

这算是反模式吗？索引不应该是约束。它们应该是性能工具。约束应该由键、约束和触发器来强制执行。如果它被认为是反模式，它会带来什么缺点，还有什么替代方案？

我假设使用 SQL Server 2019，因为唯一筛选索引的某些功能在非常旧的版本中并不存在，并且我想保留任何现代替代方案的选项。

我喜欢使用Export-DbaScript导出到名为 的文件夹C:\TargetFolder\Database\Schema。通常，我还没有Database\Schema文件夹，这将导致Export-DbaScript无法写入该文件夹。Export-DbaScript如果文件夹尚不存在，有什么方法可以创建该文件夹？我期待类似-Force参数的东西，但我在文档中没有找到它。

我正在做

Export-DbaScript `
  -InputObject $_ `
  -NoPrefix `
  -NoClobber `
  -FileName "$($MyLocation)\$($_.Database)\$($_.Name).sql

当路径不存在时，我会得到System.IO.DirectonaryNotFoundExceptions。我使用的是 2.1.18 版本

问题

我使用的是标准版 SQL Server 2019 机箱。考虑到版本，兼容级别应该不相关（标准版上不允许使用与级别相关的批处理模式功能）。在此机箱上，我一直在尝试在临时表连接到其他表之前向其添加一个空的非聚集筛选列存储索引，从而允许具有所述连接的查询使用批处理模式，尽管不使用任何列存储索引。当我这样做时，我经常在以前未使用批处理模式的查询中发现以下行为：

• 现在使用批处理模式的执行计划部分运行速度更快。特别是，与没有批处理模式部分的计划相比，窗口函数变得令人难以置信。
• 具有批处理模式部分的计划的成本总是比没有批处理模式部分的查询低得多，即使它们运行的​​时间更长。
• 尽管每个操作员在具有批处理模式步骤的计划中的两个线程上均匀运行，但 Gather Streams 有时会变得更慢。
• 之前，为了节省扫描成本，在一个表上构建了一个位图，然后通过在扫描中传递位图的哈希探测来哈希连接另一个表，这样的查询会变得慢得多。与索引扫描中的位图不同，它们在扫描后有一个非常昂贵的 Filter 运算符。

对我列表中的最后一个观察结果有什么解释吗？更重要的是，我如何防止引入批处理模式删除优秀的位图？这个粗体点就是我想要在这里回答的。如果我可以在查询的其他地方（例如在窗口函数中）同时拥有位图和批处理模式的优秀优势，那么我的查询就会快得多。我承认我可能可以更好地索引表，但我对原始哈希连接已经很满意了。

可重现的例子

Erik Darling 非常正确地要求我粘贴该计划，但我认为我应该做得更好并给出一个可重现的示例。以下使用StackOverflow2010 数据库。如果您愿意，可以将其加载到 docker 容器中。

SET STATISTICS XML OFF;

USE StackOverflow2010;

DROP TABLE IF EXISTS #IDs;

SELECT TOP (10000)
  Id
INTO
  #IDs
FROM
  Comments;

ALTER TABLE #IDs ADD CONSTRAINT RowStorePK
PRIMARY KEY CLUSTERED (Id);

SET STATISTICS XML ON;
  
;WITH [NumberedPosts] AS
(
  SELECT
    PostID
    ,ROW_NUMBER() OVER (PARTITION BY PostID ORDER BY Score) RN
  FROM
    Comments
  WHERE
    PostID IN (SELECT Id FROM #IDs)
)
SELECT
  PostID
FROM
  NumberedPosts
WHERE
  RN = 1;

/*
  Hack to get batch mode for these queries despite 
  my real machine being on Standard Edition.
*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ColStoreNonClust ON #IDs (ID)
WHERE ID = 1 AND ID = 2;

-- Same query again.

;WITH [NumberedPosts] AS
(
  SELECT
    PostID
    ,ROW_NUMBER() OVER (PARTITION BY PostID ORDER BY Score) RN
  FROM
    Comments
  WHERE
    PostID IN (SELECT Id FROM #IDs)
)
SELECT
  PostID
FROM
  NumberedPosts
WHERE
  RN = 1;

此计划是在创建列存储索引之前执行的。它使用位图。此计划是在创建列存储索引之后执行的。它不使用位图，并且运行速度比使用位图的查询慢得多。

我已经多次见过这种情况，我确信这是一种必须有一些一般解释或预防步骤的模式。

假设您的 SQL Server 2019 计算机有一个查询，该查询命中一个基于磁盘的表，该表既长（假设有 3,000,000 行），又宽（假设有 150 列）。假设您检索了大多数行和大多数列。最后，假设该表很少更改。将其视为一个典型的报告服务器表，每天仅更新一次。

是否存在上述条件适用的情况，并且传统的行存储索引比列存储索引（聚集索引或其他）更可取？

我担心自己太容易被列存储索引所吸引，并且会开始在我所有的大而宽的表上使用它们。

根据sys.dm_os_memory_clerks，我的服务器的总 RAM 使用量为 30 GB。使用此信息，我将其设置max server memory (MB)为 31,000。然后我震惊地看到sys.dm_os_memory_clerks报告说我的服务器的总 RAM 使用量为 20 GB。这更加令人困惑，因为文档说这max server memory (MB)仅适用于缓冲池

最大服务器内存选项仅限制 SQL Server 缓冲池的大小。最大服务器内存选项不会限制 SQL Server 为其他组件（例如扩展存储过程、COM 对象、非共享 DLL 和 EXE）分配而留下的剩余未保留内存区域。

sys.dm_os_memory_clerks这些决定的信息来源是否不好，还是我只需要等待我的缓冲池赶上来？