Linux 上 SQL Server 的性能功能演练

2025-07-04

适用于：SQL Server - Linux

如果你是刚接触 SQL Server 的 Linux 用户，以下任务可帮助你了解某些性能功能。虽然这些并非 Linux 独有或特定的任务，但能有助于你了解需要深入了解的领域。每个示例中均提供了该领域的详细文档链接。

注意

下面的示例使用 AdventureWorks2022 示例数据库。有关如何获取并安装此示例数据库的说明，请参阅使用备份和还原功能将 SQL Server 数据库从 Windows 迁移到 Linux。

创建列存储索引

列存储索引是使用列式数据格式（称为列存储）存储和查询大量数据的技术。

通过执行以下 Transact-SQL 命令将列存储索引添加到 SalesOrderDetail 表：

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
    ON Sales.SalesOrderDetail(UnitPrice, OrderQty, ProductID);
GO

执行以下查询，使用列存储索引扫描表：

SELECT ProductID,
       SUM(UnitPrice) AS SumUnitPrice,
       AVG(UnitPrice) AS AvgUnitPrice,
       SUM(OrderQty) AS SumOrderQty,
       AVG(OrderQty) AS AvgOrderQty
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY ProductID;

查找列存储索引的 object_id，然后确认它出现在 SalesOrderDetail 表的使用情况统计信息中，以验证是否使用了列存储索引：

SELECT *
FROM sys.indexes
WHERE name = 'IX_SalesOrderDetail_ColumnStore';
GO

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('AdventureWorks2022')
      AND object_id = OBJECT_ID('AdventureWorks2022.Sales.SalesOrderDetail');

使用内存中 OLTP

SQL Server 提供的内存中 OLTP 功能可极大提升应用程序系统的性能。本节内容将介绍如何创建存储在内存中的内存优化表，以及创建无需编译或解释即可访问表的本机编译的存储过程。

配置内存中 OLTP 的数据库

应将数据库的兼容级别至少设置为 130，以使用内存中 OLTP。使用以下查询检查 AdventureWorks2022 的当前兼容级别：
```
USE AdventureWorks2022;
GO

SELECT d.compatibility_level
FROM sys.databases AS d
WHERE d.name = DB_NAME();
GO
```
如有必要，将级别更新为 130：
```
ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;
GO
```
当事务同时涉及基于磁盘的表和内存优化表时，事务的内存优化部分必须在名为“快照”的事务隔离级别运行。若要可靠地对跨容器事务中的内存优化表强制执行此级别，请执行以下脚本：
```
ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO
```

需要先创建内存优化文件组和数据文件的容器，然后才能创建内存优化表：

ALTER DATABASE AdventureWorks2022
    ADD FILEGROUP AdventureWorks_mod CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE AdventureWorks2022
    ADD FILE (NAME = 'AdventureWorks_mod', FILENAME = '/var/opt/mssql/data/AdventureWorks_mod') TO FILEGROUP AdventureWorks_mod;
GO

创建内存优化表

内存优化表的主存储是主内存，因此与基于磁盘的表不同，不必从磁盘将数据读取到内存缓冲区。若要创建内存优化表，请使用 MEMORY_OPTIMIZED = ON 子句。

执行以下查询创建内存优化表 dbo.ShoppingCart。默认情况下，数据保留在磁盘上以获得持续性（还可将 DURABILITY 设置为仅保留架构）。

CREATE TABLE dbo.ShoppingCart
(
    ShoppingCartId INT IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,
    UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    CreatedDate DATETIME2 NOT NULL,
    TotalPrice MONEY
)
WITH (MEMORY_OPTIMIZED = ON);
GO

在表中插入一些记录：

INSERT dbo.ShoppingCart
VALUES (8798, SYSDATETIME(), NULL);

INSERT dbo.ShoppingCart
VALUES (23, SYSDATETIME(), 45.4);

INSERT dbo.ShoppingCart
VALUES (80, SYSDATETIME(), NULL);

INSERT dbo.ShoppingCart
VALUES (342, SYSDATETIME(), 65.4);

本机编译的存储过程

SQL Server 支持访问内存优化表的本机编译的存储过程。 T-SQL 语句编译为机器代码并存储为本机 DLL，能够比传统 T-SQL 更快地访问数据，更高效地执行查询。对使用 NATIVE_COMPILATION 来标记的存储过程执行本机编译。

执行以下脚本创建本机编译的存储过程，该过程在 ShoppingCart 表中插入大量记录：

CREATE PROCEDURE dbo.usp_InsertSampleCarts
@InsertCount INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    DECLARE @i AS INT = 0;
    WHILE @i < @InsertCount
        BEGIN
            INSERT INTO dbo.ShoppingCart
            VALUES (1, SYSDATETIME(), NULL);
            SET @i += 1;
        END
END;

插入 1,000,000 行：
```
EXECUTE usp_InsertSampleCarts 1000000;
```
验证是否已插入这些行：
```
SELECT COUNT(*)
FROM dbo.ShoppingCart;
```

使用查询存储

查询存储区收集有关查询、执行计划和运行时统计信息的详细性能信息。

SQL Server 2022 (16.x) 之前，默认情况下不启用查询存储，可以使用 ALTER DATABASE 启用：

ALTER DATABASE AdventureWorks2022
    SET QUERY_STORE = ON;

运行以下查询，返回查询存储中有关查询和计划的信息：

SELECT Txt.query_text_id,
       Txt.query_sql_text,
       Pl.plan_id,
       Qry.*
FROM sys.query_store_plan AS Pl
     INNER JOIN sys.query_store_query AS Qry
         ON Pl.query_id = Qry.query_id
     INNER JOIN sys.query_store_query_text AS Txt
         ON Qry.query_text_id = Txt.query_text_id;

查询动态管理视图

动态管理视图返回可用于监视服务器实例的运行状况、诊断故障以及优化性能的服务器状态信息。

要查询 dm_os_wait 统计信息动态管理视图：

SELECT wait_type,
       wait_time_ms
FROM sys.dm_os_wait_stats;