使用内存中 OLTP 改进 Azure SQL 数据库和 Azure SQL 托管实例中的应用程序性能Use In-Memory OLTP to improve your application performance in Azure SQL Database and Azure SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

内存中 OLTP 可以用来改善高级和业务关键层数据库中事务处理、数据引入和暂时性数据方案的性能,而不需要提高定价层。In-Memory OLTP can be used to improve the performance of transaction processing, data ingestion, and transient data scenarios, in Premium and Business Critical tier databases without increasing the pricing tier.

请按照以下步骤在现有数据库中采用内存中 OLTP。Follow these steps to adopt In-Memory OLTP in your existing database.

步骤 1:确保使用的是高级和业务关键层数据库Step 1: Ensure you are using a Premium and Business Critical tier database

只有高级和业务关键层数据库才支持内存中 OLTP。In-Memory OLTP is supported only in Premium and Business Critical tier databases. 如果返回的结果为 1(不是 0),则支持内存中 OLTP:In-Memory is supported if the returned result is 1 (not 0):

SELECT DatabasePropertyEx(Db_Name(), 'IsXTPSupported');

XTP 代表极端事务处理XTP stands for Extreme Transaction Processing

步骤 2:标识要迁移到 In-Memory OLTP 的对象Step 2: Identify objects to migrate to In-Memory OLTP

SSMS 包含可以针对具有活动工作负荷的数据库运行的“事务性能分析概述”。SSMS includes a Transaction Performance Analysis Overview report that you can run against a database with an active workload. 该报告识别要迁移到内存中 OLTP 的候选表和存储过程。The report identifies tables and stored procedures that are candidates for migration to In-Memory OLTP.

若要在 SSMS 中生成报告,请执行以下操作:In SSMS, to generate the report:

  • 在“对象资源管理器”中,右键单击数据库节点。In the Object Explorer, right-click your database node.
  • 单击“报表” > “标准报表” > “事务性能分析概述”。Click Reports > Standard Reports > Transaction Performance Analysis Overview.

有关详细信息,请参阅确定是否应将某个表或存储过程移植到 In-Memory OLTPFor more information, see Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP.

步骤 3:创建可比较的测试数据库Step 3: Create a comparable test database

假设报告指出数据库的某个表在转换成内存优化的表后会带来好处。Suppose the report indicates your database has a table that would benefit from being converted to a memory-optimized table. 我们建议先进行测试,以确认这项指示。We recommend that you first test to confirm the indication by testing.

需要创建生产数据库的测试副本。You need a test copy of your production database. 测试数据库应当位于与生产数据库相同的服务层级级别。The test database should be at the same service tier level as your production database.

为了简化测试,请按以下方式调整测试数据库:To ease testing, tweak your test database as follows:

  1. 使用 SSMS 连接到测试数据库。Connect to the test database by using SSMS.

  2. 若要避免在查询中用到 WITH (SNAPSHOT) 选项,请按照以下 T-SQL 语句中所示设置数据库选项:To avoid needing the WITH (SNAPSHOT) option in queries, set the database option as shown in the following T-SQL statement:

    ALTER DATABASE CURRENT
     SET
         MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
    

步骤 4:迁移表Step 4: Migrate tables

必须创建并填充想要测试的表的内存优化副本。You must create and populate a memory-optimized copy of the table you want to test. 可以使用以下方式之一来创建该副本:You can create it by using either:

  • SSMS 中提供便利的内存优化向导。The handy Memory Optimization Wizard in SSMS.
  • 手动 T-SQL。Manual T-SQL.

SSMS 中提供的内存优化向导Memory Optimization Wizard in SSMS

若要使用此迁移选项,请执行以下操作:To use this migration option:

  1. 使用 SSMS 连接到测试数据库。Connect to the test database with SSMS.

  2. 在“对象资源管理器”中,右键单击该表,然后单击“内存优化顾问”。In the Object Explorer, right-click on the table, and then click Memory Optimization Advisor.

    此时将显示“表内存优化顾问”向导。The Table Memory Optimizer Advisor wizard is displayed.

  3. 在向导中,单击“迁移验证”(或“下一步”按钮),查看该表是否包含任何在内存优化表中不受支持的功能。In the wizard, click Migration validation (or the Next button) to see if the table has any unsupported features that are unsupported in memory-optimized tables. 有关详细信息,请参阅:For more information, see:

  4. 如果该表没有不受支持的功能,顾问可执行实际的架构和数据迁移。If the table has no unsupported features, the advisor can perform the actual schema and data migration for you.

手动 T-SQLManual T-SQL

若要使用此迁移选项,请执行以下操作:To use this migration option:

  1. 使用 SSMS(或类似的实用程序)连接到测试数据库。Connect to your test database by using SSMS (or a similar utility).

  2. 获取表及其索引的完整 T-SQL 脚本。Obtain the complete T-SQL script for your table and its indexes.

    • 在 SSMS 中,右键单击表节点。In SSMS, right-click your table node.
    • 单击“编写表脚本为” > “创建到” > “新建查询窗口”。Click Script Table As > CREATE To > New Query Window.
  3. 在脚本窗口中,将 WITH (MEMORY_OPTIMIZED = ON) 添加到 CREATE TABLE 语句。In the script window, add WITH (MEMORY_OPTIMIZED = ON) to the CREATE TABLE statement.

  4. 如果存在 CLUSTERED 索引,请将其更改为 NONCLUSTERED。If there is a CLUSTERED index, change it to NONCLUSTERED.

  5. 使用 SP_RENAME 重命名现有表。Rename the existing table by using SP_RENAME.

  6. 通过运行已编辑的 CREATE TABLE 脚本,创建新的内存优化表副本。Create the new memory-optimized copy of the table by running your edited CREATE TABLE script.

  7. 使用 INSERT...SELECT * INTO 将数据复制到内存优化表:Copy the data to your memory-optimized table by using INSERT...SELECT * INTO:

INSERT INTO <new_memory_optimized_table>
        SELECT * FROM <old_disk_based_table>;

步骤 5(可选):迁移存储过程Step 5 (optional): Migrate stored procedures

In-Memory 功能还可以修改存储过程,以改善性能。The In-Memory feature can also modify a stored procedure for improved performance.

本机编译存储过程的注意事项Considerations with natively compiled stored procedures

本机编译存储过程的 T-SQL WITH 子句必须包含以下选项:A natively compiled stored procedure must have the following options on its T-SQL WITH clause:

  • NATIVE_COMPILATIONNATIVE_COMPILATION
  • SCHEMABINDING:表示除非丢弃存储过程,否则无法由存储过程以任何影响到存储过程的方式更改其列定义的表。SCHEMABINDING: meaning tables that the stored procedure cannot have their column definitions changed in any way that would affect the stored procedure, unless you drop the stored procedure.

本机模块必须使用一个大型 ATOMIC 块进行事务管理。A native module must use one big ATOMIC blocks for transaction management. 显式 BEGIN TRANSACTION 或 ROLLBACK TRANSACTION 没有角色。There is no role for an explicit BEGIN TRANSACTION, or for ROLLBACK TRANSACTION. 如果你的代码检测到违反业务规则,它可以使用 THROW 语句终止 ATOMIC 块。If your code detects a violation of a business rule, it can terminate the atomic block with a THROW statement.

本机编译存储过程的典型 CREATE PROCEDURETypical CREATE PROCEDURE for natively compiled

创建本机编译存储过程的 T-SQL 通常类似于以下模板:Typically the T-SQL to create a natively compiled stored procedure is similar to the following template:

CREATE PROCEDURE schemaname.procedurename
    @param1 type1, …
    WITH NATIVE_COMPILATION, SCHEMABINDING
    AS
        BEGIN ATOMIC WITH
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'your_language__see_sys.languages'
            )
        …
        END;
  • 对于 TRANSACTION_ISOLATION_LEVEL,SNAPSHOT 是本机编译存储过程最常用的值。For the TRANSACTION_ISOLATION_LEVEL, SNAPSHOT is the most common value for the natively compiled stored procedure. 但是,也支持其他值的子集:However, a subset of the other values is also supported:

    • REPEATABLE READREPEATABLE READ
    • SERIALIZABLESERIALIZABLE
  • sys.languages 视图中必须存在 LANGUAGE 值。The LANGUAGE value must be present in the sys.languages view.

如何迁移存储过程How to migrate a stored procedure

迁移步骤如下:The migration steps are:

  1. 获取常规解释的存储过程的 CREATE PROCEDURE 脚本。Obtain the CREATE PROCEDURE script to the regular interpreted stored procedure.

  2. 重写其标头以符合前面的模板。Rewrite its header to match the previous template.

  3. 确认存储过程 T-SQL 代码是否使用了任何不支持本机编译存储过程的功能。Ascertain whether the stored procedure T-SQL code uses any features that are not supported for natively compiled stored procedures. 根据需要实施应对措施。Implement workarounds if necessary.

    有关详细信息,请参阅本机编译存储过程的迁移问题For details see Migration Issues for Natively Compiled Stored Procedures.

  4. 使用 SP_RENAME 重命名旧存储过程。Rename the old stored procedure by using SP_RENAME. 或直接将它删除。Or simply DROP it.

  5. 运行已编辑的 CREATE PROCEDURE T-SQL 脚本。Run your edited CREATE PROCEDURE T-SQL script.

步骤 6:在测试环境中运行工作负荷Step 6: Run your workload in test

在测试数据库中,运行与在生产数据库中运行的工作负荷类似的工作负荷。Run a workload in your test database that is similar to the workload that runs in your production database. 这样,将 In-Memory 功能用于表和存储过程所达到的性能改善应可体现出来。This should reveal the performance gain achieved by your use of the In-Memory feature for tables and stored procedures.

工作负荷的主要属性包括:Major attributes of the workload are:

  • 并发连接数。Number of concurrent connections.
  • 读/写比率。Read/write ratio.

若要修改并运行测试工作负载,请考虑使用便利的 ostress.exe 工具,这篇内存中文章对此工具进行了说明。To tailor and run the test workload, consider using the handy ostress.exe tool, which illustrated in this in-memory article.

为了尽可能减少网络延迟,请在数据库所在的同一 Azure 地理区域运行测试。To minimize network latency, run your test in the same Azure geographic region where the database exists.

步骤 7:实施后的监视Step 7: Post-implementation monitoring

建议监视在生产环境中实施 In-Memory 后的性能影响:Consider monitoring the performance effects of your In-Memory implementations in production: