SQL Q&A: Rebuilding Indexes, Disk Queue Length, and More

项目
08/17/2016

SQL 问题与解答重新生成索引、磁盘队列长度和更多内容

编辑：Nancy Michell

问：如何重新生成 SQL Server 索引？我需要了解为什么 DBCC DBREINDEX 占用了我所有的磁盘空间以及当命令失败时为什么没有释放磁盘空间。我的数据库大小为 90GB，而最大的表格是 70GB。

当我运行 DBCC DBREINDEX 时，留出了 10 % 的可用空间，但它却占用了所有可用磁盘空间并且命令失败。最初的数据库文件是 90GB，当 DBCC 命令失败时，数据库文件增长到 160GB，额外的 70GB 从未释放，我不得不几次手动缩小数据库来回收它。

命令奏效时，数据库文件的大小也是 160 GB，但是几小时后自动收缩会自动释放空间。使用 defrag 而非 DBREINDEX 以占用更少的空间，这是否可行？我没有群集索引，我使用的是简单恢复模式。

答：在最简单的情况下，索引通过构建一份新的索引副本来重新生成，然后删除旧索引。从本质而言，这意味着短期内会存在两份索引副本。构建新索引要求与原始索引一样大小的数据库文件空间，并且如果重新生成需要排序操作，那么还需要额外 20% 的索引大小用于排序。

因此，最坏的情况重新生成索引需要的空间是旧索引空间的 1.2 倍。如果数据库文件没有足够的可用空间，那么该文件将随着操作进行不得不增大。如果自动增长未启用或者磁盘卷上没有足够的空间，那么可能无法提供足够的可用空间，而重新生成操作将失败。

无论操作失败与否，在重新生成操作完成后，均不会释放分配给数据库文件的额外空间。系统假定该空间将用于日常的数据库操作。

基于算法工作的方式，运行收缩（无论手动还是自动）几乎肯定会创建索引碎片。有关详细信息，请参阅 SQL Server 存储引擎。如果数据库需要可用空间用于日常操作，那么自动收缩特别有损性能，因为您陷于自动增长 - 自动收缩 - 自动增长 - 自动收缩的循环中，这样的循环会对碎片和性能造成巨大破坏。

使用 DBCC INDEXDEFRAG（或者 SQL Server™ 2005 的 ALTER INDEX ... REORGANIZE）的优势在于，它几乎不使用额外的数据库文件空间，但它可能花费的时间更长并且会生成比索引重新生成更多的事务记录。无论使用何种恢复模式，DBCC INDEXDEFRAG 始终会完全记录，因此在简单恢复模式中，索引重新生成将成为大容量日志记录。每种方法各有利弊，在 SQL Server 索引碎片最佳实践白皮书中更详尽地进行了说明。

在做出如何修复碎片的决定之前,首先决定到底是否要修复碎片。根据使用索引的操作类型不同，碎片可能对性能没有任何影响，因此修复本身是一种资源浪费。白皮书中有详述。

底线：确保为您的环境选择最适当的碎片删除方法，并且删除碎片对查询性能有帮助。

问：我已经在两个 SQL Server 2005 实例之间成功配置数据库镜像。我的应用程序使用 SQL Server 登录连接到 SQL Server，并且使用 ADO 和 SQL Native Client 进行构建。我的连接字符串和连接设置指定了正确的信息，包括适当的故障转移伙伴。我还在镜像服务器中创建了与在主体服务器中存在的登录相同的所有登录。一旦测试数据库出现故障，镜像会成功地担当起主体角色，在 SQL Server 实例上看起来一切正常。（我甚至能使用我的 Windows® 登录连接到镜像。）然而，应用程序重新连接失败，出现下列错误：

Cannot open database "<db name>" requested by the login. The login failed.

它显示登录与新的主体（原来的镜像）数据库中的用户没有关联。我运行 sp_change_users_login 同步用户和数据库登录，得到一条消息说它修复了多个孤立的用户。然后，我的应用程序重新成功连接到新的主体服务器。我已经尝试过多次故障转移，并且每一次我都会看到相同的行为，即登录与用户之间的关联丢失。

是否存在一种配置镜像设置的方法可以解决此问题？

答：是的。该问题基于这样一种事实，即每台服务器的 SQL Server 登录安全标识符 (SID) 不匹配，尽管其登录名称相同。这不是 Windows/域用户/组登录的问题，因为这些登录的 SID 是基于用户/组的域 SID 创建的，所以无论用户/组添加到哪个 SQL Server 中，对于同一指定的用户/组情况都相同。

为了不必进行 sp_change_users_login 同步步骤，您不仅需要使用相同的名称在映像服务器中创建 SQL Server 登录，而且需要在主体服务器上使用相同的 SID 创建 SQL Server 登录。您可以通过在映像服务器上创建登录时，在 CREATE LOGIN 语句中使用 SID 规范来实现这一点，具体如下：

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

您可以通过查询 sys.sql_logins 目录视图从主体服务器中检索每个登录的 SID。关于查询将在指定的服务器上为每个 SQL Server/Windows 登录生成一个实际 CREATE LOGIN 语句的示例如图 1 所示。

Figure 1 生成 CREATE LOGIN 语句

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

问：我的平均磁盘队列长度应该是多少？例如，如果我有 20 个单独的存储区域网络 (SAN)（具有 RAID 01 配置）物理心轴，我如何计算平均磁盘队列长度？它是平均磁盘队列长度/20 还是平均磁盘队列长度/2？

答：首先，您花费时间用于 SAN 环境中的平均磁盘队列长度之前，应该先看一看磁盘延迟时间。但是，这确实取决于您尝试解决的问题。您很快就会知道原因。

此计数器的解释（来自 Perfmon）是“平均磁盘队列长度是读和写请求的平均数，这些请求在采样间隔期间排队等候选择的磁盘”。这可以是物理磁盘或逻辑磁盘计数器，因此得到的数字取决于如何向操作系统提供基本储存。

让我们看看您的案例。您在 RAID 01 配置中有 20 个心轴，这意味着它们是分布且镜像（或者是镜像且分布，这取决于如何读取 01 或 10）。存储阵列的要点是条带集中有 10 个心轴。

但是我缺少必要的信息，例如条带大小是多少、您的写入多大以及您正在发出何种类型的 I/O 操作（读取、写入、顺序还是随机）。

我们暂时忽略缺少的信息。如果平均磁盘队列长度为 10，那么 OS 将使 10 个 I/O 排队到磁盘阵列。从理论上讲，那可能是一个 I/O 对应条带中 10 个镜像组中的每一个，也可能是所有 10 个 I/O 对应一个磁盘。您无法知道是哪一种。

这正是信息缺失的关键所在。让我们假定您的条带大小为 64 KB，您的写入大小为 8KB，并且您要进行一次完整的块顺序写入。这是 SQL Server 储存活动的典型情况。在此案例中，很可能 8 个 I/O 都到达第一个磁盘，而接下来的两个 I/O 到达下一个磁盘。因此，如果您尝试计算出此情况下每个磁盘的磁盘队列长度，那么第一个磁盘为 8，第二个磁盘为 2，阵列中其他 8 个磁盘为 0。

现在，让我们将情况更改为 8KB 的理论条带大小以及 64KB 的写入块大小，并且保持磁盘队列长度为 10。在此情况下，每个 64KB 块遍布在 8 个磁盘中，因此一次 I/O 会写入到 8 个磁盘，并且 10 个排队等候的 I/O 遍布到该阵列所有 10 个磁盘上的 80 个磁盘写入。如果您尝试计算该阵列中每个磁盘的磁盘队列长度，那么对阵列中的每个磁盘将是 8。

让我们更接近实际情况，在该描述中增加不同程度的不确定性。在大多数情况下，您的 SAN 存储将使用服务器中的一个或多个 HBA 连接到服务器，使用一些光纤将 HBA 连接到 SAN、SAN 前端的一些端口以及可能某种光纤交换机（位于服务器和 SAN 之间的结构中）。然后我们会研究 SAN 内的总线内部结构，以及磁盘如何连接到 SAN 前端的端口上。

您所看到的在 Perfmon 中报告的任何排队，都是在位于 OS 测量其磁盘队列长度地点和磁盘表面之间的任意位置上，延迟时间太长或者排队过长的现象。这就是为什么您应该看延迟时间和基于计数器而不是平均磁盘队列长度进行决策的原因。

问：我正在使用事务性复制，并且知道在订阅者表格中许多行已经手动更改过，因此当发布者尝试更改在订阅者中不再存在的行时，我会收到错误。

我需要知道通过复制是否有任何方法，可以只是重新初始化来自发布者的这一表格，而不必再次重新应用整个快照。我研究了 tablediff 函数，它看起来似乎可以实现我想做的事情，但是我想知道它如何与复制进行交互。

例如，tablediff 是否获取发布者表格的时间快照，并将其与订阅表格进行比较？我是否需要停止复制以使用 tablediff 实用程序来确保数据的一致性？还有什么我需要了解的吗？

答：首先，tablediff 不会获取发布或订阅表格的文字快照。关于您的特定情况，可以考虑以下几种选择。

第一个选择是暂时停止复制并运行该实用程序。如果您担心用户尝试修改数据，您可以使用 -sourcelocked 和 -destinationlocked 参数，这将在实用程序运行期间对两个表格执行独占锁定。如果不能接受这种方法，那么另外一个选择是考虑 -rc 和 -ri 参数，而保留复制运行。这将运行一次 tablediff，然后在其检测到的错误上依次运行它，从而消除由于复制传播延迟产生的错误。但要注意，使用此选项视复制延迟而定，您可能不必获取订阅者更改的所有行。

衷心感谢以下 Microsoft IT 专家，感谢他们与我们分享了技术与专业知识： Sunil Agarwal、Chad Boyd、David Browne、Gilles Comeau、Emmanuel Dreux、Amanda Foote、Matt Hollingsworth、Paul Mestemaker、Uttam Parui、Paul Randal、Dennis Tighe 和 Steven Wort。

其他资源