内存中示例In-Memory sample

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

使用 Azure SQL 数据库的内存中技术可以提高应用程序的性能,并潜在地降低数据库的成本。In-Memory technologies in Azure SQL Database enable you to improve performance of your application, and potentially reduce cost of your database. 使用 Azure SQL 数据库的内存中技术可以在各种工作负荷上实现性能改进。By using In-Memory technologies in Azure SQL Database, you can achieve performance improvements with various workloads.

在本文中,你会看到两个示例,它们演示了如何使用 Azure SQL 数据库中的内存中 OLTP 和列存储索引。In this article you'll see two samples that illustrate the use of In-Memory OLTP, as well as columnstore indexes in Azure SQL Database.

有关详细信息,请参阅:For more information, see:

 

1.安装内存中 OLTP 示例1. Install the In-Memory OLTP sample

Azure 门户中按几下鼠标,即可创建 AdventureWorksLT 示例数据库。You can create the AdventureWorksLT sample database with a few clicks in the Azure portal. 本部分中的步骤说明如何使用内存中 OLTP 对象项目扩充 AdventureWorksLT 数据库,并演示性能优势。Then, the steps in this section explain how you can enrich your AdventureWorksLT database with In-Memory OLTP objects and demonstrate performance benefits.

以下资源提供了更简单、更直观的内存中 OLTP 性能演示:For a more simplistic, but more visually appealing performance demo for In-Memory OLTP, see:

安装步骤Installation steps

  1. 通过 Azure 门户,在服务器上创建一个高级或业务关键数据库。In the Azure portal, create a Premium or Business Critical database on a server. 将“源”设置为 AdventureWorksLT 示例数据库。Set the Source to the AdventureWorksLT sample database. 有关详细说明,请参阅在 Azure SQL 数据库中创建第一个数据库For detailed instructions, see Create your first database in Azure SQL Database.

  2. 使用 SQL Server Management Studio (SSMS.exe) 连接到该数据库。Connect to the database with SQL Server Management Studio (SSMS.exe).

  3. In-Memory OLTP Transact-SQL 脚本 复制到剪贴板。Copy the In-Memory OLTP Transact-SQL script to your clipboard. T-SQL 脚本在步骤 1 创建的 AdventureWorksLT 示例数据库中创建所需的内存中对象。The T-SQL script creates the necessary In-Memory objects in the AdventureWorksLT sample database that you created in step 1.

  4. 将 T-SQL 脚本粘贴到 SSMS,并执行该脚本。Paste the T-SQL script into SSMS, and then execute the script. MEMORY_OPTIMIZED = ON 子句 CREATE TABLE 语句至关重要。The MEMORY_OPTIMIZED = ON clause CREATE TABLE statements are crucial. 例如:For example:

CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

错误 40536Error 40536

如果运行 T-SQL 脚本时收到错误 40536,请运行以下 T-SQL 脚本来验证数据库是否支持内存中功能:If you get error 40536 when you run the T-SQL script, run the following T-SQL script to verify whether the database supports In-Memory:

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

结果“0”表示不支持内存中,结果“1”表示支持。A result of 0 means that In-Memory isn't supported, and 1 means that it is supported. 若要诊断问题,请确保数据库位于“高级”服务层级。To diagnose the problem, ensure that the database is at the Premium service tier.

关于创建的内存优化项About the created memory-optimized items

:此示例包含以下内存优化表:Tables: The sample contains the following memory-optimized tables:

  • SalesLT.Product_inmemSalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmemSalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmemSalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeedDemo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeedDemo.DemoSalesOrderDetailSeed

可以通过 SSMS 中的“对象资源管理器”检查内存优化表。You can inspect memory-optimized tables through the Object Explorer in SSMS. 右键单击“表” > “筛选器” > “筛选器设置” > “内存优化”。Right-click Tables > Filter > Filter Settings > Is Memory Optimized. 值等于 1。The value equals 1.

或者可以查询目录视图,例如:Or you can query the catalog views, such as:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

本机编译的存储过程:可以通过目录视图查询来检查 SalesLT.usp_InsertSalesOrder_inmem:Natively compiled stored procedure: You can inspect SalesLT.usp_InsertSalesOrder_inmem through a catalog view query:

SELECT uses_native_compilation, OBJECT_NAME(object_id), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

 

运行示例 OLTP 工作负荷Run the sample OLTP workload

以下两个 存储过程 的唯一差别在于,第一个过程使用内存优化表版本,而第二个过程使用普通磁盘表:The only difference between the following two stored procedures is that the first procedure uses memory-optimized versions of the tables, while the second procedure uses the regular on-disk tables:

  • SalesLT.usp_InsertSalesOrder_inmemSalesLT . usp_InsertSalesOrder _inmem
  • SalesLT.usp_InsertSalesOrder_ondiskSalesLT . usp_InsertSalesOrder _ondisk

本部分介绍如何使用便利的 ostress.exe 实用程序在压力级别执行两个存储过程。In this section, you see how to use the handy ostress.exe utility to execute the two stored procedures at stressful levels. 可以比较完成两个压力回合所需的时间。You can compare how long it takes for the two stress runs to finish.

运行 ostress.exe 时,建议将参数值传递到以下两个存储过程:When you run ostress.exe, we recommend that you pass parameter values designed for both of the following:

  • 使用 -n100 运行大量的并发连接。Run a large number of concurrent connections, by using -n100.
  • 使用 -r500,让每个连接循环数百次。Have each connection loop hundreds of times, by using -r500.

但是,建议从较小的值(-n10 和 -r50)开始,确保一切运行正常。However, you might want to start with much smaller values like -n10 and -r50 to ensure that everything is working.

Ostress.exe 的脚本Script for ostress.exe

本部分显示 ostress.exe 命令行中内嵌的 T-SQL 脚本。This section displays the T-SQL script that is embedded in our ostress.exe command line. 此脚本使用前面安装的 T-SQL 脚本所创建的项。The script uses items that were created by the T-SQL script that you installed earlier.

以下脚本在以下内存优化中插入包含 5 个细目的示例销售订单:The following script inserts a sample sales order with five line items into the following memory-optimized tables:

  • SalesLT.SalesOrderHeader_inmemSalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmemSalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
begin;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
end

若要创建上述适用于 ostress.exe 的 T-SQL 脚本的 _ondisk 版本,请将两处出现的 _inmem 子字符串替换为 _ondiskTo make the _ondisk version of the preceding T-SQL script for ostress.exe, you would replace both occurrences of the _inmem substring with _ondisk. 这种替换将影响表和存储过程的名称。These replacements affect the names of tables and stored procedures.

安装 RML 实用工具和 ostressInstall RML utilities and ostress

最好规划在 Azure 虚拟机 (VM) 上运行 ostress.exe。Ideally, you would plan to run ostress.exe on an Azure virtual machine (VM). 在 AdventureWorksLT 数据库所在的同一 Azure 地理区域中创建 Azure VMYou would create an Azure VM in the same Azure geographic region where your AdventureWorksLT database resides. 也可以改为在便携式计算机上运行 ostress.exe。But you can run ostress.exe on your laptop instead.

在 VM 或选择的任何主机上,安装重放标记语言 (RML) 实用工具。On the VM, or on whatever host you choose, install the Replay Markup Language (RML) utilities. 这些实用工具包括 ostress.exe。The utilities include ostress.exe.

有关详细信息,请参阅:For more information, see:

首先运行 _inmem 压力工作负荷Run the _inmem stress workload first

可以使用 RML 命令提示符 窗口来运行 ostress.exe 命令行。You can use an RML Cmd Prompt window to run our ostress.exe command line. 命令行参数指示 ostressThe command-line parameters direct ostress to:

  • 同时运行 100 个连接 (-n100)。Run 100 connections concurrently (-n100).
  • 每个连接运行 T-SQL 脚本 50 次 (-r50)。Have each connection run the T-SQL script 50 times (-r50).
ostress.exe -n100 -r50 -S<servername>.database.chinacloudapi.cn -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

若要运行上述 ostress.exe 命令行:To run the preceding ostress.exe command line:

  1. 请在 SSMS 中运行以下命令重置数据库数据内容,以删除前面运行的命令所插入的所有数据:Reset the database data content by running the following command in SSMS, to delete all the data that was inserted by any previous runs:

    EXECUTE Demo.usp_DemoReset;
    
  2. 将上述 ostress.exe 命令行的文本复制到剪贴板。Copy the text of the preceding ostress.exe command line to your clipboard.

  3. 将参数 -S -U -P -d 的 <placeholders> 替换为正确的实数值。Replace the <placeholders> for the parameters -S -U -P -d with the correct real values.

  4. 在 RML Cmd 窗口中运行编辑后的命令行。Run your edited command line in an RML Cmd window.

结果是一个持续时间Result is a duration

ostress.exe 完成时,它会将运行持续时间写入为 RML 命令窗口中的输出的最后一行。When ostress.exe finishes, it writes the run duration as its final line of output in the RML Cmd window. 例如,一个较短的测试回合持续大约 1.5 分钟:For example, a shorter test run lasted about 1.5 minutes:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

重置,编辑 _ondisk,然后重新运行Reset, edit for _ondisk, then rerun

在获得 _inmem 运行结果之后,请针对 _ondisk 运行执行以下步骤:After you have the result from the _inmem run, perform the following steps for the _ondisk run:

  1. 在 SSMS 中运行以下命令重置数据库,删除前面运行的命令所插入的所有数据:Reset the database by running the following command in SSMS to delete all the data that was inserted by the previous run:

    EXECUTE Demo.usp_DemoReset;
    
  2. 编辑 ostress.exe 命令行,将所有的 _inmem 替换为 _ondisk。Edit the ostress.exe command line to replace all _inmem with _ondisk.

  3. 再次重新运行 ostress.exe,并捕获持续时间结果。Rerun ostress.exe for the second time, and capture the duration result.

  4. 再次重置数据库(可靠地删除大量测试数据)。Again, reset the database (for responsibly deleting what can be a large amount of test data).

预期比较结果Expected comparison results

在与数据库处于同一 Azure 区域的 Azure VM 上运行 ostress 时,内存中测试已显示此简化工作负荷大约有 9 倍的性能改善。Our In-Memory tests have shown that performance improved by nine times for this simplistic workload, with ostress running on an Azure VM in the same Azure region as the database.

 

2.安装内存中分析示例2. Install the In-Memory Analytics sample

本部分比较使用列存储索引与传统 b 树索引时的 IO 和统计信息结果。In this section, you compare the IO and statistics results when you're using a columnstore index versus a traditional b-tree index.

通常,在对 OLTP 工作负荷进行实时分析时,最好是使用非群集列存储索引。For real-time analytics on an OLTP workload, it's often best to use a nonclustered columnstore index. 有关详细信息,请参阅列存储索引介绍For details, see Columnstore Indexes Described.

准备列存储分析测试Prepare the columnstore analytics test

  1. 使用 Azure 门户基于示例创建全新的 AdventureWorksLT 数据库。Use the Azure portal to create a fresh AdventureWorksLT database from the sample.

    • 使用相同的名称。Use that exact name.
    • 选择任一“高级”服务层级。Choose any Premium service tier.
  2. sql_in-memory_analytics_sample 复制到剪贴板。Copy the sql_in-memory_analytics_sample to your clipboard.

    • T-SQL 脚本在步骤 1 创建的 AdventureWorksLT 示例数据库中创建所需的内存中对象。The T-SQL script creates the necessary In-Memory objects in the AdventureWorksLT sample database that you created in step 1.
    • 该脚本将创建维度表和两个事实表。The script creates the Dimension table and two fact tables. 每个事实表中填充了 350 万行。The fact tables are populated with 3.5 million rows each.
    • 该脚本可能需要 15 分钟才能完成。The script might take 15 minutes to complete.
  3. 将 T-SQL 脚本粘贴到 SSMS,并执行该脚本。Paste the T-SQL script into SSMS, and then execute the script. CREATE INDEX 语句中的 COLUMNSTORE 关键字至关重要,如下所示:The COLUMNSTORE keyword in the CREATE INDEX statement is crucial, as in:
    CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. 将 AdventureWorksLT 设置为兼容级别 130:Set AdventureWorksLT to compatibility level 130:
    ALTER DATABASE AdventureworksLT SET compatibility_level = 130;

    级别 130 并不与内存中功能直接相关。Level 130 is not directly related to In-Memory features. 但级别 130 通常提供比级别 120 更快的查询性能。But level 130 generally provides faster query performance than 120.

关键表和列存储索引Key tables and columnstore indexes

  • dbo.FactResellerSalesXL_CCI 是具有群集列存储索引的表,已在数据级别进一步压缩。dbo.FactResellerSalesXL_CCI is a table that has a clustered columnstore index, which has advanced compression at the data level.

  • dbo.FactResellerSalesXL_PageCompressed 是具有等效常规群集索引的表,只在页面级别压缩。dbo.FactResellerSalesXL_PageCompressed is a table that has an equivalent regular clustered index, which is compressed only at the page level.

用于比较列存储索引的关键查询Key queries to compare the columnstore index

可以运行多种 T-SQL 查询类型来查看性能改进情况。There are several T-SQL query types that you can run to see performance improvements. 在 T-SQL 脚本的步骤 2 中,请注意这一对查询。In step 2 in the T-SQL script, pay attention to this pair of queries. 这一对查询只是在一行上有所不同:They differ only on one line:

  • FROM FactResellerSalesXL_PageCompressed a
  • FROM FactResellerSalesXL_CCI a

群集列存储索引位于 FactResellerSalesXL_CCI 表中。A clustered columnstore index is in the FactResellerSalesXL_CCI table.

以下 T-SQL 脚本摘录列出了每个表查询的 IO 和 TIME 统计信息。The following T-SQL script excerpt prints statistics for IO and TIME for the query of each table.

/*********************************************************************
Step 2 -- Overview
-- Page Compressed BTree table v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
ALTER DATABASE AdventureworksLT SET compatibility_level = 130
GO

-- Execute a typical query that joins the Fact Table with dimension tables
-- Note this query will run on the Page Compressed table, Note down the time
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed a
INNER JOIN DimProduct b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI a
INNER JOIN DimProduct b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

在采用 P2 定价层的数据库中,与传统索引相比,使用聚集列存储索引时,此查询预期可获得约 9 倍的性能提升。In a database with the P2 pricing tier, you can expect about nine times the performance gain for this query by using the clustered columnstore index compared with the traditional index. 对于 P15,使用列存储索引有望获得约 57 倍的性能提升。With P15, you can expect about 57 times the performance gain by using the columnstore index.

后续步骤Next steps

其他资源Additional resources

深入信息Deeper information

应用程序设计Application design

工具Tools