SQL Q&A: Best Practices Analyzer, Multi-Core Processors, and More

项目
08/17/2016

SQL 问题解答最佳实践分析工具、多核处理器和更多内容

编辑：Nancy Michell

问：有关涉及到限定架构的表/视图使用的最佳实践分析工具 (BPA) 规则，我有一些问题。根据 BPA 文档，此规则不检查临时表中的架构限定。BPA 报告对存储过程内创建的临时表的引用作出标记。它们是否应被限定？如果限定，那么具有何种架构？我认为临时表与其他表一样，应限于其所有者。

答：BPA 对表和视图引用的架构限定建议其实并不适用于 SQL Server™ 2005，因为用户与架构相分离的机制已解决了在早期版本的 SQL Server 上需要此实践的问题。在 SQL Server 2000 中，具有不同默认架构的不同用户需要通过架构限定才能重用查询计划。他们可以使用没有限定架构的 dbo 对象，但 SQL Server 必须首先检查这些对象的默认架构，防止重用查询计划。由于用户与架构相分离的机制允许不同用户共享一个默认架构，而且通常允许所有数据库用户都可以访问其默认架构中的对象，因此通常将具有未限定的表和视图名称的特别计划在用户中共享并进行重用。存储过程内未限定的表和视图的名称从一开始就不会遇到上述问题，这进一步削弱了此实践的理由。SQL Server 2005 中的 BPA 不包括此类规则，因为它没有 T-SQL 分析器。然而，它将检查不正确的配置和安全设置。

问：我在运行 SQL Server 2005 SP1 时遇到异常行为。如果我删除某个表中的记录并同时在同一表中执行插入操作，则在提交删除事务之前，会锁定插入。是否可以采用某种方法找到阻塞的相关内容？

答：可以。您可以运行图 1 中的动态管理视图 (DMV) 脚本来查找锁和阻塞程序。

Figure 1 查找阻塞和锁

-- script to show blocking and locks
SELECT 
  t1.request_session_id AS spid, 
  t1.resource_type AS type, 
  t1.resource_database_id AS dbid, 
  (case resource_type 
   WHEN ‘OBJECT’ THEN object_name(t1.resource_associated_entity_id) 
   WHEN ‘DATABASE’ THEN ‘ ‘ 
   ELSE (SELECT object_name(object_id) 
      FROM sys.partitions 
      WHERE hobt_id=resource_associated_entity_id) 
  END) AS objname, 
  t1.resource_description AS description, 
  t1.request_mode AS mode, 
  t1.request_status AS status,
   t2.blocking_session_id
FROM sys.dm_tran_locks AS t1 LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address 
GO

问：除了超线程和双核技术外，芯片供应商开始推出具有多核（四核、八核或更多）的处理器。我正在考虑购买具有多核处理器的新服务器以支持 SQL Server 2005 Standard Edition 的部署，我想知道在使用四核处理器时是否只能使用一个物理 CPU（因为 Standard Edition 限制为最多只能使用四个物理 CPU）？

答：对于许可和 CPU 版本支持方面，SQL Server 都只考虑物理接口/CPU 的数目，而不管处理器上核的数目。因此，举例来说，SQL Server 2005 Standard Edition 最多支持 4 个 CPU 即表示它将支持 4 个物理 CPU 接口，而不管每个接口上核的数目（如果有 4 个物理 CPU，每个 CPU 具有 4 个核，那么 Standard Edition 部署将可以使用 16 个逻辑 CPU）。而且，即使您拥有 16 个核/逻辑 CPU，也只需要为 4 个物理 CPU 购买许可，而不是所有 16 个核。有关 SQL Server 和多核的详细信息，请参阅 microsoft.com/sql/howtobuy/multicore.mspx。

问：我打算购买一些新的数据库服务器。我的组织应该选择 64 位版本还是依旧选择经过充分试验和测试的 32 位版本？

答：这要视具体情况而定。现在由于供应商将其硬件从 x86 转换到 x64，这已成为非常普遍的问题。您应该首先问一下您的数据库服务器上会有哪些内存压力。如果在整个硬件寿命（通常为三年）中，您的 SQL Server 实例占用的内存不到 3GB，则应选择 32 位 x86，假设这也是其他服务器（域控制器、DNS 服务器、应用程序服务器、Web 服务器和邮件服务器）的方向。如果 SQL Server 实例占用的内存超过 16GB 或者每个服务器（或群集）具有多个实例，则应认真考虑使用 64 位技术。如果存在八个以上的处理器，IA64 则为标准建议配置。但是，由于 x64 四核和八核 CPU 开始大量上市，因此选择 x64 可能比 IA64 更具性价比。

当迁移到 x64（或 IA64）时，在初始成本估计中可能不包括在开发、测试和性能环境中部署同一体系结构。但您还应考虑是否在硬件生命周期中受到旧技术的困扰。在 18 个月中，几乎不可能纯粹获得 x86 硬件。如果您现在选择这种解决方案，由于这是一种淘汰并更换的方案，则在今后一两年间升级的费用昂贵。现在选择 64 位硬件将在未来几年为您提供更多选择。

因此，在您打算购买新硬件时，在核心应用程序发布周期开始之际，或在您从 SQL Server 2000 升级到 SQL Server 2005 时，都是迅速转到 64 位的大好时机。

问：我在两个服务器间启用了日志传送功能。周末，主服务器上的硬件问题导致日志传送功能停止。当我返回检查日志传送功能时，我发现尽管停机几个小时，但 LS_backup_dbname 运行良好。

然而，在辅助服务器上，复制作业看上去工作正常，但还原作业似乎要跳过文件，但最后总是失败。它虽然运行，可是却要跳过每个文件，但都失败了。上次还原到现在已有 849 分钟。过去有过类似情况，我所做的只是重新启动传送配置，即，关闭该功能然后再次将其打开，这样我就停止获取新的备份并将其还原到辅助服务器，然后再重新开始日志传送功能。是否有更好的办法处理此类情况？

答：您看到的可能是丢失备份文件的结果。正如您所知道的，日志传送功能的备份、复制和还原作业是独立运行的。复制位置中的旧备份文件也会按您指定的计划进行删除。如果一段时期内还原作业没有运行，该时间段长度可能超过间隔，这将导致丢失备份文件。日志传送还原作业具有的逻辑会尝试从不同操作环境产生的不同类型错误中恢复。基本上，它在发生错误时扫描各个文件，尝试找出正确的备份文件。如果没有找到正确的备份文件，我猜想可能某种操作删除了所需的文件，也可能是您重新配置了日志传送的备份部分。

一种解决办法是，通过其他备份作业无意中创建的事务日志备份文件进行手动还原。只需将这些文件复制到日志传送复制位置，问题在于日志传送识别的命名约定。但一旦通过手动还原解决这一问题后，常规还原即可识别并继续。

问：我想知道如何使用 T-SQL 查看总物理内存和可用物理内存的大小。是否有简便的方法获取此信息？

答：只需运行图 2 中的查询，即可获取所需数据。

Figure 2 获取内存信息

With VASummary(Size,Reserved,Free) AS 
(SELECT 
  Size = VaDump.Size, 
  Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
  WHEN 0 THEN 0 ELSE 1 END), 
  Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
  WHEN 0 THEN 1 ELSE 0 END) 
FROM 
( 
  SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) 
    AS Size, 
    region_allocation_base_address AS Base 
  FROM sys.dm_os_virtual_address_dump 
  WHERE region_allocation_base_address <> 0x0 
  GROUP BY region_allocation_base_address 
  UNION 
  SELECT CONVERT(VARBINARY, region_size_in_bytes), 
    region_allocation_base_address 
  FROM sys.dm_os_virtual_address_dump 
  WHERE region_allocation_base_address = 0x0 
) 
AS VaDump 
GROUP BY Size)
 
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS INT)/1024 
    AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0

问：为了更方便地从应用程序创建数据库，我想将 sa（系统管理员）密码存储在使用对称密钥加密的文件中。我应该怎么做？

答：首先，您应确保决不从应用程序使用 sa。此外，您应使用 Windows 身份验证而不是 SQL Server 身份验证。

如果您需要执行更高权限的活动，应执行以下操作：定义执行该操作所必需的最低权限。定义一个包含这些权限的数据库角色。将某个用户分配到该角色。创建一个具有执行这些功能的 EXECUTE AS 子句的存储过程。有关 Execute As 子句的说明，请访问 msdn2.microsoft.com/ms188354.aspx。

当然，不管涉及的帐户是否为 sa 帐户，应谨记 Windows 身份验证并不始终是一个可选的方法，更基本的问题是：您如何安全地存储 SQL Server 登录帐户的密码？通过这种方式，那些需要使用该密码的应用程序才可以访问它，但未授权用户或应用程序却不能访问。

对密码进行加密是正确的一步，但它只是将问题从保护密码转向保护加密密钥而已。

如果是基于 Microsoft® .NET Framework 的应用程序，请查阅企业程序库（网址是 msdn2.microsoft.com/aa480453.aspx）和 MSDN® 的模式与实践部分的建议（网址是 msdn.microsoft.com/practices）。企业程序库包含若干应用程序块（这将对您非常有用），其中包含配置块、加密块和数据访问块；它们不但有助于您（至少）安全地存储密码，而且还有助于您管理整个数据访问。

如果企业程序库不是一个可选的方法（可能由于您没有使用 .NET Framework），则您应熟悉 CryptoAPI，请参阅以下网址：msdn2.microsoft.com/aa380255.aspx。您可以在此了解随操作系统一起发布的核心加密功能，该功能可以帮助您将加密密钥与用户主体或计算机联系起来，这将简化密钥管理（在您对密码进行加密后，这将成为需要关注的问题）。

问：我希望将我的 SQL Server 2000 两节点群集服务器（主动/主动群集，在 64 位硬件上）从域 A 移至同一林内的域 B。SQL Server 数据库位于 SAN 中。Active Directory® 体系结构调用具有两个子域（域 A 和域 B）的空的根域。域 B 将成为放置所有基础结构组件的域。它是一个 Windows Server® 2003 Enterprise 环境，包含运行 Windows 2000 的其他域控制器。

是否可以将 SQL Server 群集服务器的域成员资格从域 A 更改为域 B？

答：对于初学者，请参阅“如何更改 SQL Server 2000 故障转移群集的域”以及“如何将 Windows 群集服务器从一个域移至另一个域”。该过程也适用于多实例群集。唯一的不同之处在于您必须根据情况为每个实例重复以上步骤。此处决不是“建议”您更改群集的域，特别是如果涉及的问题比较复杂，而且已有过多次更改的情况。另一种方法是像您所说的那样，同时重建新的实例并迁移数据。

每个项目都是不同的，它们会使用各种各样的工具来执行实际的域级更改（包括安全注意事项、帐户迁移、SID 映射等等）。这常常是该过程最困难的部分，而非实际的更改本身。

衷心感谢以下 Microsoft IT 专家，感谢他们与我们分享了技术与专业知识： Sunil Agarwal、Laurent Banon、Steve Bloom、Chad Boyd、Matt Burr、Shaun Cox、Cindy Gross、Bobby Gulati、Matt Hollingsworth、Arnost Kobylka、Mikhail Shir、Fernando Pessoa Sousa、Stephen Strong 和 Ramu Veeraraghavan。

其他资源