Linux 上 SQL Server 的性能功能演练
03/17/2017
本文内容
适用于:
SQL Server(所有支持的版本) - Linux
如果你是刚接触 SQL Server 的 Linux 用户,以下任务可帮助你了解某些性能功能。 虽然这些并非 Linux 独有或特定的任务,但能有助于你了解需要深入了解的领域。 每个示例中均提供了该领域的详细文档链接。
备注
以下示例使用 AdventureWorks 示例数据库。 有关如何获取并安装此示例数据库的说明,请参阅将 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) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) 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('AdventureWorks')
AND object_id = OBJECT_ID('AdventureWorks.Sales.SalesOrderDetail');
使用内存中 OLTP
SQL Server 提供的内存中 OLTP 功能可极大提升应用程序系统的性能。 《评估指南》中的本节内容将介绍如何创建存储在内存中的内存优化表,以及创建无需编译或解释即可访问表的本机编译的存储过程。
配置内存中 OLTP 的数据库
建议将数据库的兼容级别至少设置为 130,以使用内存中 OLTP。 使用以下查询检查 AdventureWorks 的当前兼容级别:
USE AdventureWorks
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
需要先创建 Memory Optimized FILEGROUP 和数据文件的容器,然后才能创建内存优化表:
ALTER DATABASE AdventureWorks ADD FILEGROUP AdventureWorks_mod CONTAINS memory_optimized_data
GO
ALTER DATABASE AdventureWorks 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 int = 0
WHILE @i < @InsertCount
BEGIN
INSERT INTO dbo.ShoppingCart VALUES (1, SYSDATETIME() , NULL)
SET @i += 1
END
END
插入 1,000,000 行:
EXEC usp_InsertSampleCarts 1000000
验证是否已插入这些行:
SELECT COUNT(*) FROM dbo.ShoppingCart
了解有关内存中 OLTP 的详细信息
有关内存中 OLTP 的详细信息,请参阅以下主题:
使用查询存储
查询存储区收集有关查询、执行计划和运行时统计信息的详细性能信息。
默认情况下,查询存储不处于活动状态,可通过 ALTER DATABASE 启用:
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
运行以下查询,返回查询存储中有关查询和计划的信息:
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
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;