# 检查SQLServer版本
SELECT @@VERSION;
GO

# 检查数据库兼容性级别
SELECT name, compatibility_level FROM sys.databases;
GO

name compatibility_level
———– ——————-
fgedudb 150
master 150
model 150
tempdb 150
msdb 150

# 检查服务器内存
SELECT
physical_memory_kb / 1024 AS physical_memory_mb,
virtual_memory_kb / 1024 AS virtual_memory_mb,
committed_kb / 1024 AS committed_mb,
committed_target_kb / 1024 AS committed_target_mb
FROM sys.dm_os_sys_memory;
GO

physical_memory_mb virtual_memory_mb committed_mb committed_target_mb
—————— —————– ———— ——————-
65536 131072 16384 61440

对于内存中OLTP环境，需要确保SQLServer版本为2014或更高，数据库兼容性级别为120或更高，并且服务器内存充足，建议至少32GB内存。from SQLServer视频:www.itpux.com。

2.2 SQLServer内存中OLTP配置建议

SQLServer内存中OLTP配置建议包括：

1. 内存规划：根据内存优化表的大小和并发访问量，合理规划内存容量

2. 存储规划：为内存优化文件组配置高速存储，如SSD

3. 索引设计：为内存优化表选择合适的索引类型

4. 存储过程优化：将频繁执行的存储过程编译为原生编译存储过程

5. 监控配置：设置适当的监控，跟踪内存使用情况和性能指标

更多视频教程www.fgedu.net.cn。

Part03-生产环境项目实施方案

3.1 SQLServer内存中OLTP高级配置实施

# 创建内存优化文件组
ALTER DATABASE fgedudb ADD FILEGROUP fgedu_inmemory CONTAINS MEMORY_OPTIMIZED_DATA;
GO

# 添加内存优化文件
ALTER DATABASE fgedudb ADD FILE (
NAME = ‘fgedu_inmemory_file’,
FILENAME = ‘/sqlserver/fgdata/fgedu_inmemory_file’
) TO FILEGROUP fgedu_inmemory;
GO

# 创建内存优化表
CREATE TABLE dbo.fgedu_orders (
order_id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
status NVARCHAR(50) NOT NULL,
payment_method NVARCHAR(50) NOT NULL,
create_time DATETIME DEFAULT GETDATE(),
INDEX IX_customer_id NONCLUSTERED (customer_id),
INDEX IX_order_date NONCLUSTERED (order_date)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

# 创建原生编译存储过程
CREATE PROCEDURE dbo.usp_insert_order
@customer_id INT,
@order_date DATETIME,
@total_amount DECIMAL(10, 2),
@status NVARCHAR(50),
@payment_method NVARCHAR(50)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = ‘English’
)
INSERT INTO dbo.fgedu_orders (
customer_id, order_date, total_amount, status, payment_method
) VALUES (
@customer_id, @order_date, @total_amount, @status, @payment_method
);
END;
GO

# 查看内存优化表
SELECT
t.name AS table_name,
t.is_memory_optimized,
t.durability_desc
FROM sys.tables t
WHERE t.is_memory_optimized = 1;
GO

table_name is_memory_optimized durability_desc
———– ——————- —————-
fgedu_orders 1 SCHEMA_AND_DATA

# 优化内存中OLTP性能

# 1. 调整内存配置
EXEC sp_configure ‘max server memory’, 61440;
RECONFIGURE;
GO

# 2. 优化内存优化表设计
ALTER TABLE dbo.fgedu_orders
ADD INDEX IX_status NONCLUSTERED (status);
GO

# 3. 优化原生编译存储过程
CREATE PROCEDURE dbo.usp_get_orders_by_customer
@customer_id INT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = ‘English’
)
SELECT
order_id, customer_id, order_date, total_amount, status, payment_method, create_time
FROM dbo.fgedu_orders
WHERE customer_id = @customer_id
ORDER BY order_date DESC;
END;
GO

# 4. 监控内存使用情况
SELECT
object_name(object_id) AS table_name,
memory_used_by_table_kb,
memory_used_by_indexes_kb,
memory_used_total_kb
FROM sys.dm_db_xtp_table_memory_stats;
GO

table_name memory_used_by_table_kb memory_used_by_indexes_kb memory_used_total_kb
———– ———————– ————————– ———————-
fgedu_orders 128 64 192

# 5. 优化事务隔离级别
ALTER DATABASE fgedudb SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer内存中OLTP高级实战案例

# 案例1：内存优化表性能测试

# 插入测试数据
DECLARE @start_time DATETIME = GETDATE();

DECLARE @i INT = 1;
WHILE @i <= 100000 BEGIN EXEC dbo.usp_insert_order @customer_id = @i % 1000, @order_date = GETDATE(), @total_amount = 100.00 + (@i % 1000), @status = 'Pending', @payment_method = 'Credit Card'; SET @i = @i + 1; END; DECLARE @end_time DATETIME = GETDATE(); SELECT DATEDIFF(ms, @start_time, @end_time) AS insert_time_ms; GO insert_time_ms -------------- 2543 # 测试查询性能 DECLARE @start_time DATETIME = GETDATE(); EXEC dbo.usp_get_orders_by_customer @customer_id = 1; DECLARE @end_time DATETIME = GETDATE(); SELECT DATEDIFF(ms, @start_time, @end_time) AS query_time_ms; GO query_time_ms ------------- 15 # 案例2：混合事务处理 # 创建传统表 CREATE TABLE dbo.fgedu_customer ( customer_id INT PRIMARY KEY IDENTITY, customer_name NVARCHAR(100) NOT NULL, email NVARCHAR(100) NOT NULL, phone NVARCHAR(20) NOT NULL, create_time DATETIME DEFAULT GETDATE() ); GO # 插入客户数据 INSERT INTO dbo.fgedu_customer (customer_name, email, phone) VALUES ('Customer 1', 'customer1@example.com', '13800138001'), ('Customer 2', 'customer2@example.com', '13900139002'), ('Customer 3', 'customer3@example.com', '13700137003'); GO # 创建混合事务存储过程 CREATE PROCEDURE dbo.usp_process_order @customer_id INT, @total_amount DECIMAL(10, 2), @status NVARCHAR(50), @payment_method NVARCHAR(50) AS BEGIN BEGIN TRANSACTION; -- 检查客户是否存在 IF NOT EXISTS (SELECT 1 FROM dbo.fgedu_customer WHERE customer_id = @customer_id) BEGIN RAISERROR('Customer not found', 16, 1); ROLLBACK TRANSACTION; RETURN; END; -- 插入订单 EXEC dbo.usp_insert_order @customer_id = @customer_id, @order_date = GETDATE(), @total_amount = @total_amount, @status = @status, @payment_method = @payment_method; COMMIT TRANSACTION; END; GO # 测试混合事务 EXEC dbo.usp_process_order @customer_id = 1, @total_amount = 500.00, @status = 'Processing', @payment_method = 'Credit Card'; GO # 验证数据 SELECT * FROM dbo.fgedu_orders WHERE customer_id = 1; GO order_id customer_id order_date total_amount status payment_method create_time --------- ------------ ----------------------- ------------- ---------- --------------- ----------------------- 1 1 2024-01-01 10:00:00.000 500.00 Processing Credit Card 2024-01-01 10:00:00.000 # 案例3：内存优化表与传统表性能对比 # 创建传统表 CREATE TABLE dbo.fgedu_orders_traditional ( order_id INT IDENTITY(1,1) PRIMARY KEY, customer_id INT NOT NULL, order_date DATETIME NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, status NVARCHAR(50) NOT NULL, payment_method NVARCHAR(50) NOT NULL, create_time DATETIME DEFAULT GETDATE() ); GO # 创建传统存储过程 CREATE PROCEDURE dbo.usp_insert_order_traditional @customer_id INT, @order_date DATETIME, @total_amount DECIMAL(10, 2), @status NVARCHAR(50), @payment_method NVARCHAR(50) AS BEGIN INSERT INTO dbo.fgedu_orders_traditional ( customer_id, order_date, total_amount, status, payment_method ) VALUES ( @customer_id, @order_date, @total_amount, @status, @payment_method ); END; GO # 测试传统表插入性能 DECLARE @start_time DATETIME = GETDATE(); DECLARE @i INT = 1; WHILE @i <= 100000 BEGIN EXEC dbo.usp_insert_order_traditional @customer_id = @i % 1000, @order_date = GETDATE(), @total_amount = 100.00 + (@i % 1000), @status = 'Pending', @payment_method = 'Credit Card'; SET @i = @i + 1; END; DECLARE @end_time DATETIME = GETDATE(); SELECT DATEDIFF(ms, @start_time, @end_time) AS insert_time_ms; GO insert_time_ms -------------- 8765 # 性能对比 -- 内存优化表：2543ms -- 传统表：8765ms -- 内存优化表性能提升约3.4倍

# 创建内存中OLTP自动化脚本
CREATE PROCEDURE dbo.usp_inmemory_oltp_automation
AS
BEGIN
— 检查内存优化表状态
SELECT
t.name AS table_name,
t.is_memory_optimized,
t.durability_desc,
m.memory_used_by_table_kb,
m.memory_used_by_indexes_kb,
m.memory_used_total_kb
FROM sys.tables t
JOIN sys.dm_db_xtp_table_memory_stats m ON t.object_id = m.object_id
WHERE t.is_memory_optimized = 1;

— 检查内存使用情况
SELECT
physical_memory_kb / 1024 AS physical_memory_mb,
virtual_memory_kb / 1024 AS virtual_memory_mb,
committed_kb / 1024 AS committed_mb,
committed_target_kb / 1024 AS committed_target_mb
FROM sys.dm_os_sys_memory;

— 检查内存优化文件组
SELECT
fg.name AS filegroup_name,
f.name AS file_name,
f.physical_name,
f.size * 8 / 1024 AS size_mb
FROM sys.filegroups fg
JOIN sys.database_files f ON fg.data_space_id = f.data_space_id
WHERE fg.type = ‘FX’;

— 清理过期数据
DELETE FROM dbo.fgedu_orders
WHERE order_date < DATEADD(month, -1, GETDATE()); END; GO # 创建自动化作业 USE msdb; GO EXEC dbo.sp_add_job @job_name = N'In-Memory OLTP Automation', @enabled = 1, @description = N'Automate In-Memory OLTP tasks'; GO EXEC dbo.sp_add_jobstep @job_name = N'In-Memory OLTP Automation', @step_name = N'Run automation', @subsystem = N'TSQL', @command = N'USE fgedudb; EXEC dbo.usp_inmemory_oltp_automation;', @retry_attempts = 3, @retry_interval = 5; GO EXEC dbo.sp_add_schedule @schedule_name = N'In-Memory OLTP Automation Schedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 60; GO EXEC dbo.sp_attach_schedule @job_name = N'In-Memory OLTP Automation', @schedule_name = N'In-Memory OLTP Automation Schedule'; GO EXEC dbo.sp_add_jobserver @job_name = N'In-Memory OLTP Automation', @server_name = N'(local)'; GO # 查看内存优化表状态 SELECT t.name AS table_name, t.is_memory_optimized, t.durability_desc, m.memory_used_by_table_kb, m.memory_used_by_indexes_kb, m.memory_used_total_kb FROM sys.tables t JOIN sys.dm_db_xtp_table_memory_stats m ON t.object_id = m.object_id WHERE t.is_memory_optimized = 1; GO table_name is_memory_optimized durability_desc memory_used_by_table_kb memory_used_by_indexes_kb memory_used_total_kb ----------- ------------------- ---------------- ----------------------- -------------------------- ---------------------- fgedu_orders 1 SCHEMA_AND_DATA 128 64 192

风哥提示：定期监控内存中OLTP的内存使用情况，确保系统稳定运行。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer内存中OLTP最佳实践

1. 只为高并发、高频访问的表使用内存优化表

2. 合理规划内存容量，确保有足够的内存用于内存优化表

3. 使用原生编译存储过程处理频繁执行的操作

4. 为内存优化表选择合适的索引类型

5. 监控内存使用情况，避免内存不足

6. 合理设置内存优化文件组的存储配置

7. 结合传统表和内存优化表，充分发挥各自的优势

5.2 SQLServer内存中OLTP常见问题与解决方案

# 问题1：内存不足
# 解决方案：增加服务器内存，或减少内存优化表的大小

# 问题2：内存优化表性能下降
# 解决方案：检查内存使用情况，优化索引设计，重新编译存储过程

# 问题3：内存优化文件组空间不足
# 解决方案：增加内存优化文件的大小，或清理过期数据

# 问题4：原生编译存储过程编译失败
# 解决方案：检查存储过程语法，确保符合原生编译要求

# 问题5：混合事务性能问题
# 解决方案：优化事务设计，减少事务范围，避免长时间锁定

# 检查内存使用情况
SELECT
physical_memory_kb / 1024 AS physical_memory_mb,
virtual_memory_kb / 1024 AS virtual_memory_mb,
committed_kb / 1024 AS committed_mb,
committed_target_kb / 1024 AS committed_target_mb
FROM sys.dm_os_sys_memory;
GO

# 检查内存优化表内存使用
SELECT
object_name(object_id) AS table_name,
memory_used_by_table_kb,
memory_used_by_indexes_kb,
memory_used_total_kb
FROM sys.dm_db_xtp_table_memory_stats;
GO

# 检查内存优化文件组空间
SELECT
fg.name AS filegroup_name,
f.name AS file_name,
f.physical_name,
f.size * 8 / 1024 AS size_mb,
f.max_size * 8 / 1024 AS max_size_mb
FROM sys.filegroups fg
JOIN sys.database_files f ON fg.data_space_id = f.data_space_id
WHERE fg.type = ‘FX’;
GO

更多学习教程公众号风哥教程itpux_com。