65.9K
CodeProject 正在变化。 阅读更多。
Home

解决数据迁移到 SQL Server 的性能问题

Phani Krishna Kollapur Gandla
emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0投票)

2011年11月10日

CPOL

11分钟阅读

viewsIcon

32025

如何解决数据迁移到 SQL Server 时的性能问题。

引言

对加拿大一家领先的汽车保险公司进行了性能工程评估。根据客户降低运营和维护支出的决定，将存储在 DB2 和 Oracle 等不同数据存储中的数据迁移到了 SQL Server 2008 中维护的统一存储库。现有的与 DB2/Oracle 10g 通信的 Java 客户端应用程序已进行修改，以与 SQL Server 通信。

尽管修改后的环境满足了功能要求，但在应用程序的性能和可伸缩性方面存在一些不足。

据观察，本次演练中解决的大部分性能问题在任何迁移到 SQL Server 的数据迁移项目中都可能普遍存在。准备本文档的基本目的是解决这些重复出现的性能问题，从而节省潜在的风险和执行成本。

环境详情

现有环境 目标环境
操作系统 IBM AIX Windows 2008 R2 (64 位)
数据库版本 (DB2) DB2 9.1 Fix Pack 7 SQL Server 2008 (64 位)
数据库版本 (Oracle) Oracle 8i SQL Server 2008 (64 位)

使用的工具

工具名称 目的 特定要求
Perfmon 系统监控工具，用于记录和收集服务器上定义的计数器的性能统计信息。 N/A
SQL Profiler SQL Server Profiler 是一个诊断工具，可以捕获与服务器相关的 SQL 事件，并可用于分析有问题的查询。这些事件也可以在离线模式下记录/保存到跟踪文件中以供进一步分析。 兼容 SQL Server 2005/2008
i-SSMA Infosys 自研工具，用于将 DB2 数据库从大型机等旧平台迁移到 Microsoft SQL Server 2005/2008。 N/A
SSMA for Oracle Microsoft 提供的工具，用于将数据从 Oracle 迁移到 SQL Server 2005/2008。 N/A

迁移中的性能敏感区域

以下是迁移到 SQL Server 时需要监控的一些主要性能敏感区域：

  • 如果客户端与 Java 相关，客户端与 SQL Server 之间的通信
  • SQL Server 中的行级触发器
  • SQL Server 中的通过触发器强制执行的参照完整性
  • 多个 After 触发器和回滚
  • SQL Server 中的递归和嵌套触发器
  • 扩展存储过程
  • 嵌套游标
  • 大小写敏感性和排序规则

这些主题中的每一项都将在下面详细讨论。

Java 客户端与 SQL Server 之间的通信

从 Java 客户端到 SQL Server 的端到端事务响应时间超过 20 分钟。尽管 Java 客户端具有内联查询（动态 SQL），但相同事务在现有应用程序中大约需要 5 秒。

以下是观察到的现象：

  • 捕获的 SQL 跟踪显示，大多数查询的执行时间较长，并且磁盘读写次数很高。
  • 即使是从后端执行的查询，其执行时间也较长。
  • 大多数查询都出现了表扫描。

在手动修复 SQL Server 上的必要索引后，后端触发的表扫描和查询执行时间大大减少。这可以将事务时间从 20 分钟缩短到大约 8 分钟，但仍远远超出 SLA。

除了识别出的缺少索引问题外，从前端触发的事务仍然需要更长的时间来执行。当使用 JDBC 驱动程序执行耗时事务的查询时，在 SQL 跟踪中观察到了以下模式。

从这种模式可以看出，Java 应用程序和 SQL Server 之间的通信是使用 Unicode（粗体显示为 NVARCHAR）进行的。

declare @p1 int
set @p1=1073786063
declare @p2 int
set @p2=180238477
declare @p7 int
set @p7=-1
exec sp_cursorprepexec @p1 output,@p2 
output,N'@P0nvarchar(4000)',N'<<SEARCH/DML Queries>>',4112,8193,@p7 
output,N'F7248180-273'
select @p1, @p2, @p7

使用 Java 驱动程序通信的 Java 客户端和 SQL Server 之间可能存在性能问题。大多数 Java 驱动程序默认会将字符串参数作为 Unicode 传递给 SQL Server。问题在于，如果 Unicode 参数引用索引中的 VARCHAR 键列，SQL Server 引擎将不会使用适当的索引来解析查询，从而导致不必要的表扫描增加。

可以通过重置 Java 驱动程序中的一个默认参数来纠正此问题。参数名称和值可能因驱动程序供应商而异。

供应商 参数
JSQLConnect asciiStringParameters
JTDS sendStringParametersAsUnicode
DataDirectConnect sendStringParametersAsUnicode
Microsoft JDBC sendStringParametersAsUnicode
WebLogic Type 4 JDBC sendStringParametersAsUnicode

以下统计数据是通过运行事务使用的其中一个查询，在“sendStringParametersAsUnicode”设置为 true 和 false 时捕获的。

sendStringParametersAsUnicode CPU 读取 写入 持续时间 (毫秒)
True (默认设置) 6718 220916 0 1866
假 0 36 0 0

通过此更改，原本需要大约 8 分钟的事务大幅缩短到 5 秒。

SQL Server 中的行级触发器

Oracle 和 SQL Server 触发器之间的一个主要区别在于，最常见的 Oracle 触发器是行级触发器（FOR EACH ROW），它针对源语句的每一行触发。SQL Server 只支持语句级触发器，它每条语句只触发一次，而不管受影响的总行数。SSMA for Oracle 用于触发器转换的基本规则是（根据 SSMA for Oracle 转换指南）：

  1. 表的所有 BEFORE 触发器都将转换为一个 INSTEAD OF 触发器。
  2. AFTER 触发器在 SQL Server 中仍然是 AFTER 触发器。
  3. Oracle 视图上的 INSTEAD OF 触发器保持 INSTEAD OF 触发器。
  4. 行级触发器通过游标循环实现。

每次触发都会获取表锁，并在操作完成后释放。即使使用资源消耗最低的游标配置（FAST FORWARD、READ ONLY），触发器的性能也可能受到以下因素的影响：

  • 通过游标迭代的行数
  • 针对除插入或删除表之外的其他表的 SQL 语句。

在事务流程中，对于带有游标的行级触发器，已在许多情况下观察到锁定问题或批处理执行挂起。

Microsoft 还建议尽可能减少游标的使用，因为它们消耗的资源很多。最佳实践是保持触发器逻辑简单。如果业务需求是在触发器中循环遍历修改后的行，那么使用表变量（临时表）或行集逻辑优于游标。

SQL Server 中的通过触发器强制执行的参照完整性

参照完整性是 RDBMS 提供的用于防止输入不一致数据的特性。在 SQL 中可以通过两种机制强制执行参照完整性：

  1. 通过外键强制执行的数据参照完整性 (DRI) 约束
  2. 触发器

通过触发器强制执行 DRI 可能会导致触发器中的操作时间过长和锁定问题。在发生任何需要回滚的完整性冲突时，事务的生命周期可能会延长。以下客户-订单关系示例对此进行了说明：

--CREATE CUSTOMER TABLE
CREATE TABLE DBO.CUSTOMER_TABLE
(
CUSTOMER_ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CUSTOMER_NAME VARCHAR (50) NOT NULL,
CONTACT_NUMBER INT NOT NULL UNIQUE
)

--CREATE ORDER TBALE
CREATE TABLE DBO.ORDER_TABLE
(
ORDER_ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CUSTOMER_ID INT,
ORDER_QTY INT NOT NULL,
ORDER_DATE DATETIME NOT NULL
)

--CREATE A TRIGGER TO IMPOSE CUSTOMER-ORDER RELATIONSHIP ON CUSTOMER RECORD DELETION
CREATE TRIGGER DBO.tr_CUSTOMER_TABLE_DELETE ON DBO.CUSTOMER_TABLE
FOR DELETE
AS

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM DBO.ORDER_TABLE OT JOIN DELETED D ON D.CUSTOMER_ID = OT.CUSTOMER_ID)
BEGIN
 RAISERROR ('Customer has orders listed. Record will not be deleted.', 16,10)
 WAITFOR DELAY '00:00:15' -- delay to show the held locks in a separate connection
    ROLLBACK
END
GO

--INSERT ROWS INTO PARENT TABLE
INSERT INTO DBO.CUSTOMER_TABLE (CUSTOMER_NAME,CONTACT_NUMBER) SELECT 'CUST 1',123456789

--INSERT ROWS INTO CHILD TABLE
INSERT INTO DBO.ORDER_TABLE (CUSTOMER_ID,ORDER_DATE, ORDER_QTY) SELECT 1,'12/10/2010',20
INSERT INTO DBO.ORDER_TABLE (CUSTOMER_ID,ORDER_DATE, ORDER_QTY) SELECT 1,'12/12/2010',40

删除客户记录时，会检测到如下所示的锁定问题：

SELECT RESOURCE_TYPE AS [RESOURCE NAME], 
  RESOURCE_ASSOCIATED_ENTITY_ID AS [ENTITY ID],
  REQUEST_MODE AS [REQUEST MODE],
  REQUEST_TYPE AS [REQUEST TYPE]
FROM sys.dm_tran_locks

Locks.jpg

嵌套触发器中的递归执行

SQL Server 中有两种类型的递归：

直接递归：当触发器执行某个操作导致同一触发器再次启动时，就会发生这种类型的递归。

间接递归：当触发器执行某个操作导致同一类型（AFTER 或 INSTEAD OF）的触发器启动时，就会发生这种递归。第二个触发器执行的操作会导致原始触发器再次激活。

recursiontriggers.jpg

（图示为直接和间接递归的一种场景）

除非设置了 RECURSIVE_TRIGGERS 数据库选项，否则 AFTER 触发器不会递归调用自身。当 RECURSIVE_TRIGGERS 数据库选项设置为 OFF 时，只阻止 AFTER 触发器的直接递归。

事务范围内的多个并发请求和触发器运行可能会导致锁定问题。

可以通过以下方式防止 AFTER 触发器的间接递归执行：

  • 要禁用“嵌套触发器”，应将服务器选项设置为 0。默认情况下，该选项设置为 1。可以通过运行以下查询来检查：
    • SELECT * FROM SYS.CONFIGURATONS WHERE CONFIGURATION_ID = 115
  • 使用“IF UPDATE ()”：返回一个布尔值，指示是否尝试对表或视图的指定列执行 INSERT 或 UPDATE。UPDATE () 可以在 Transact-SQL INSERT 或 UPDATE 触发器主体内的任何位置使用，以测试触发器是否应执行某些操作。

扩展存储过程

SQL Server 中的用户定义函数不能包含 DML 语句，也不能调用存储过程。而 Oracle 函数可以执行 SQL Server 中过程基本上相同的功能。SSMA for Oracle 使用的解决方法是将函数体实现为存储过程（<<函数名>>$IMPL），并通过扩展过程（调用 $IMPL 存储过程的包装器）在函数内调用它。

转换示例如下（根据 Microsoft 关于从 Oracle 迁移到 SQL Server 2008 的指南，http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx）：

CREATE FUNCTION [schema.] <function_name>
(
<parameters list>
) 
RETURNS <return_type>
AS
BEGIN

DECLARE @spid INT, @login_time DATETIME
SELECT @spid = sysdb.ssma_ora.get_active_spid(),@login_time = sysdb.ssma_ora.get_active_login_time()

DECLARE @return_value_variable <function_return_type>

--CALLING THE $IMPL STORED PROCEDURE THROUGH EXTENDED PROCEDURE 'master.dbo.xp_ora2ms_exec2_ex'
EXEC master.dbo.xp_ora2ms_exec2_ex @@spid,
@login_time, 
<database_name>, <schema_name>, <function_implementation_as_procedure_name>, 
bind_to_transaction_flag, [parameter1, parameter2, ... ,] @return_value_variable OUTPUT

RETURN @return_value_variable

END

扩展存储过程提供了一种在动态链接库 (DLL) 中动态加载和执行函数的方法，其方式类似于存储过程，从而无缝扩展 SQL Server 功能。可以轻松触发 SQL Server 外部的操作，并将外部信息返回到 SQL Server。

在可能的情况下，SSMA 会尝试直接控制这些函数的调用（而是直接调用相应的 ...$IMPL SP），但某些情况不支持。这些情况将导致直接调用生成的包装器函数（该函数又调用扩展存储过程），这些函数速度很慢，并可能导致死锁。

建议的方法之一是直接调用 SQL Server 代码来使用 func_name$IMPL 存储过程（仅使用常规 EXECs，而不调用包装器函数）。

嵌套游标

如“行级触发器”部分所述，Microsoft 建议尽量减少游标的使用。游标强制数据库引擎反复获取行、处理阻塞、管理锁和传输结果。因此，可能会比需要使用更多的锁，并且会对 tempdb 数据库产生影响。

影响根据使用的游标类型而异。嵌套游标使用的游标级别也会影响批处理性能。嵌套存储过程的示例代码如下：

DECLARE @ObjectId Varchar(16) 
DECLARE @childId Varchar(16) 
DECLARE @ParentId Varchar(16) 
DECLARE @CurrentStatus Int 
 
DECLARE MajorCursor CURSOR FOR 
SELECT ObjectId, ParentId FROM MainTable 
 
OPEN MajorCursor 
FETCH NEXT FROM MajorCursor INTO @ObjectId, @ParentId
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
        DECLARE innerCursor CURSOR FOR 
        SELECT CHILD_ID FROM ChildTable WHERE ENTY_ID = @ParentId 
                    
        OPEN innerCursor 
        FETCH NEXT FROM innerCursor INTO @childId
        SET @CurrentStatus = @@FETCH_STATUS 
        
        WHILE @CurrentStatus = 0 
        BEGIN 
              --LOGIC TO BE IMPLEMENTED
        
              FETCH NEXT FROM innerCursor INTO @childId
              SET @CurrentStatus = @@FETCH_STATUS
        END 
        
        CLOSE innerCursor 
        DEALLOCATE innerCursor --deallocate inner cursor
                    
        FETCH NEXT FROM MajorCursor INTO @ObjectId , @ParentId
END 
 
CLOSE MajorCursor 
DEALLOCATE MajorCursor -- deallocate major cursor

代码中存在多个嵌套游标的实例，甚至还有包含游标实现的触发器。通过将游标代码更改为临时变量代码，可以减少包流程中的大部分锁定问题。

大小写敏感性和排序规则

SQL Server 中数据类型和排序规则类型的可能组合如下所示：

列数据类型 排序 SQL Server 引擎应用的排序规则 对性能的影响
非 Unicode（CHAR、VARCHAR、TEXT） SQL 非 Unicode 速度更快，但对于某些排序规则不可靠
非 Unicode（CHAR、VARCHAR、TEXT） Windows Unicode 比 SQL 排序规则稍慢，但可靠
Unicode（NCHAR、NVARCHAR、NTEXT） SQL/Windows Unicode 对于两种排序规则都较慢

SQL Server 引擎应用的 Unicode 排序规则比非 Unicode SQL 排序顺序应用的规则复杂得多。当 SQL Server 关联 Unicode 数据时，字符会被赋予一个权重，该权重会根据排序规则的区域设置动态修改。Unicode 排序规则适用于所有 Unicode 数据类型，这些数据类型通过 SQL 排序规则或 Windows 排序规则定义。

  1. 当 **使用 SQL 排序规则定义非 Unicode 数据类型时**，SQL Server 使用非 Unicode 排序规则。尽管使用此排序规则的排序和扫描通常比 Unicode 规则更快，但对于某些排序规则它们不可靠。
  2. SQL Server **通过 Unicode 排序规则执行使用 Windows 排序规则定义的非 Unicode 数据类型的字符串比较**。由于这些规则比非 Unicode 排序规则复杂得多，因此它们需要更多的资源。因此，尽管 Unicode 排序规则通常更昂贵，但在 Unicode 数据和使用 Windows 排序规则定义的非 Unicode 数据之间，性能方面通常只有很小的差异。
  3. **Unicode 数据排序可能比非 Unicode 数据慢**，因为双字节存储，并且还取决于要排序的数据量。此外，在特定代码页中排序亚洲双字节字符集 (DBCS) 数据比排序亚洲字符慢得多，因为 DBCS 数据实际上是单字节和双字节宽度字符的混合，而 Unicode 字符是固定宽度的。
  4. 可能存在其他性能问题，主要取决于在 SQL Server 的客户端实例之间转换加密机制的问题。排序、转换以及客户与数据交互过程中可能发生的数据损坏量将决定用于排序的数据类型。频繁使用 Unicode 存储机制对大量数据进行排序可能会严重影响性能。

Windows 排序规则为非 Unicode 和 Unicode 数据类型提供了一致的字符串比较，并且对于 SQL Server 中的非 Unicode 文本，它们也与 Windows 操作系统中的字符串比较一致。由于这些因素，通常首选 Windows 排序规则，除非存在与向后兼容性或需要 SQL 排序规则的精确性能问题。

参考文献

  1. 提高 SQL 性能 (http://msdn.microsoft.com/en-us/library/ff647793.aspx)
  2. 探索 SQL 触发器 (http://msdn.microsoft.com/en-us/magazine/cc164047.aspx)
  3. 排查 SQL Server 中的性能问题 (http://technet.microsoft.com/en-us/library/cc966540.aspx)
  4. 比较 SQL 排序规则与 Windows 排序规则 (http://support.microsoft.com/kb/322112)
© . All rights reserved.