SQL 问题与解答CPU 瓶颈、还原和移动数据库，及其他信息

编辑：Nancy Michell

问：我的 SQL Server™ CPU 使用率峰值突然过高 — 但之前并未做过任何更改。没有添加新用户，没有卸除硬件，也没有创建新表。这是怎么了？

提示：在创建索引期间进行访问

有时，您要对大型表创建索引（这可能会花费大量时间），但又希望数据在此过程中可用。怎样才能同时达到这两个目标呢？

每次创建、拖动或重建聚集索引时，SQL Server 都会将架构修改 (SCH-M) 锁置于表中，用于阻止所有用户在操作期间访问基础数据。这是对表创建聚集索引的情形。相反，当您对列创建非聚集索引时，SQL Server 会将共享 (S) 锁置于表中，虽然这也会阻止对基础表的数据更新，但至少允许您执行 SELECT 语句进而读取该数据。

如果创建聚集索引的过程中允许对该表进行读取至关重要，您可以对表创建一个索引并使其成为联机操作。该命令如下所示：

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

对表使用联机索引操作时，SQL Server 仍会将用于聚集索引的 SCH-M 锁或用于非聚集索引的共享 (S) 锁置于基础表中，但只适用于在索引操作从开始到结束这一段很短的时间。因此，此选项为在索引创建过程中查询和更新底层表提供了更好的访问方式。但要注意，联机索引创建/操作仅适用于 SQL Server 2005 Enterprise Edition。

答：在未对服务器进行任何更改或未增加任何负载的情况下，如果 CPU 瓶颈突然意外出现，其根本原因有很多，但有以下几种常见原因：查询计划不理想、SQL Server 配置差、应用程序/数据库设计不当和硬件资源不足。

出现此类情形时，首要任务是确定服务器是否为 CPU 受限的，如果是，请确定本地 SQL Server 系统上 CPU 占用率最大的是哪些语句。您可以使用性能监视器通过查看 PROCESSOR:% PROCESSOR TIME 计数器来确定服务器是否为 CPU 受限的。如果您发现每个 CPU 所用的时间值等于或大于 75%，就表示出现了 CPU 瓶颈。

您还应通过查询系统动态管理视图 (DMV)（称为 SYS.DM_OS_SCHEDULERS）监视 SQL Server 计划程序，以查看可运行任务的值。非零值表示任务必须等待到进入自己的相应时间段才能运行；此计数器的数值很高也是出现 CPU 瓶颈的表现。

您可以使用以下查询列出所有计划程序并查看可运行任务的数量：

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

要获取占用 CPU 最多的前 50 个 SQL 语句，请使用图 1 中的查询。

Figure 1 前 50 个 CPU 占用

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

问：我必须从磁带恢复 SQL Server 2005 数据库。还原后，用户失去了对数据库的大部分权限。您是否能告诉我在还原过程中哪些操作不正确？数据完好，但是权限很乱。

答：您很有可能没有及时地将 master 数据库还原到同一点，使用户数据库中的登录 ID 与当前的 master 数据库不匹配。您需要对它们进行同步。侧栏“移动数据库时从哪里获得帮助”列出了最有用的资源，可以彻底解决您在还原或移动数据库时遇到的问题。

问：我开发了一个使用存储过程的应用程序，该存储过程使用 SQL Server 2005 SP1 中的 OpenXML 将 XML 数据分割到关系表中。XML 文档最大为 5KB（平均是 2.5KB）。存储过程被多次并行调用（最多 50 次）。

我正面临着严重的锁争用问题，我认为可能是 OpenXML 的原因。您有什么看法吗？

答：尽管 OpenXML 在单线程上分割或转换数据可能比使用 nodes 方法快，但 nodes 方法通常具有较好的适应性，在并行使用时尤为明显。不过，如果您打算完全使用 OpenXML，则应按照以下指导来改进 OpenXML 的总体性能。

不要使用相同的行模式调用 5 次 OpenXML（像您提到的在解决方案中执行的操作一样），而应将使用相同行模式的所有数据提取到一个临时表，然后从临时表中执行您的选择。尽早尝试使用 sp_xml_removedocument 释放内存。另外，只要有可能，就尽量避免使用 * 和 // 等通配符；提供确切的途径可以更好地执行查询。

问：DBCC SHRINKFILE 在我的服务器上运行得相当缓慢。我能在多处理器计算机上获得更好的性能吗？我该如何改善这种状况？

答：DBCC SHRINKFILE 是单线程操作，这表示它不会使用多个 CPU。它将页面从文件后端移到文件前端，每次移动一个页面。该操作通常称为缩减，并不执行碎片整理；事实上，在大多数情况下，缩减还会增加逻辑碎片。

一些改进缩减性能的方法包括将页面移动到聚集索引中。如果您拥有堆，并且这些堆具有很多非聚集索引，则会显著降低速度（与聚集索引情形相比）。

还要注意，移动大型对象 blob (LOB) 数据的页面时速度很慢，这是由于必须要读取行内数据以查找 LOB 数据的根。

如果索引/表的绝大部分内容位于文件末尾，您可以重建索引将其移动到文件前端。重建索引会用到多个 CPU，并可在 bulk_logged 模式中使用较少的日志空间。这样，当您运行缩减时，运行速度将更快。

有关缩减操作的详细信息，请从 blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx 和 blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx 处的条目开始。

移动数据库时从哪里获得帮助

• 如何在运行 SQL Server 的计算机之间移动数据库
• 如何通过使用 SQL Server 中的 Detach 和 Attach 函数将 SQL Server 数据库移到新位置
• INF：协同使用 WITH MOVE 选项与 RESTORE 语句
• 如何解决在运行 SQL Server 的服务器之间移动数据库时的权限问题
• INF：如何在 SQL Server 实例之间传输登录和密码
• INF：了解 SQL Server 2000 中的“复制数据库向导”并排除故障
• 数据库还原后，对数据库的用户登录和权限可能不正确
• 不能将系统数据库备份还原到不同版本的 SQL Server
• 如何从 SQL Server 中的数据库备份或事务日志备份中检索特定的表或行

提示：更改端口

熟悉 SQL Server 的人都知道 SQL Server 所侦听的默认端口号是 1433。尽管 SQL Server 的命名实例被配置为使用动态端口（表示无论 SQL Server 实例何时开始，它们都可以选择任意可用端口），但默认的 SQL Server 实例始终侦听端口 1433。因此如果您的服务器正在侦听该默认端口，并且该端口受到弱保护，那么就存在风险。不过，您可以通过更改默认端口防止攻击。方法如下。

打开 SQL Server 配置管理器，展开 SQL Server 2005 网络配置，然后展开协议。接下来双击 TCP/IP。TCP/IP 属性列表及其功能就会显示在下面的表中，请进行相应设置。

请注意，SQL Server 数据库引擎可以侦听同一 IP 地址的多个端口，因此要使用的端口将以 1433,1500,1501 形式列出，中间使用逗号分隔。如果您希望配置单个 IP 地址以侦听多个端口，还必须将 Listen All 参数设置为“no”，该参数位于“TCP/IP 属性”对话框的“协议”选项卡上。

现在，右键单击每个地址，然后单击“属性”，确定要配置的 IP 地址。如果“TCP 动态端口”对话框包含 0，则表示数据库引擎正在侦听动态端口，请删除 0。在“IP 属性”区域的“TCP 端口”框中，键入希望该 IP 地址侦听的端口号，然后单击“确定”。在控制台窗格中，单击“SQL Server 2005 Services”，然后在详细信息窗格中右键单击“SQL Server”（<实例名称>），然后单击“重新启动”停止并重新启动 SQL Server。

将 SQL Server 配置为侦听特定端口后，有 3 种方式可以将客户端连接到该端口。您可以运行服务器上的 SQL Server Browser 服务按名称连接到数据库引擎实例；您可以在客户端创建一个别名，指定该端口号；或者给客户端编制一个程序，使用自定义的连接字符串连接客户端。

**衷心感谢以下 Microsoft IT 专家为我们回答了本月的问题：**Chad Boyd、Cindy Gross、John Hadden、Saleem Hakani、Stephen Jiang、Mahesh Nayak、Paul Randal 以及 Wayne Yu。

© 2008 Microsoft Corporation 与 CMP Media, LLC.保留所有权利；不得对全文或部分内容进行复制.