Steroids上的SQL Server Express Edition

 

介绍

醒醒……你一直觉得这个世界乱了套。一个奇怪的念头，却无法驱散——就像是脑子里的碎片。你一生都在无所不能的微软强加的限制和规则的地牢中度过，而你却没有意识到这一点。

但是，如果您想继续进入一个仙境，我将向您展示在 SQL Server Express Edition上成功开发的兔子洞有多深……是多么不可能。

时不时，怀着几分情意，回忆起我职业生涯的最初几年……重新粉刷后的草更绿了……当时公司的管理层不太关心各种许可条件……但时代在快速变化，如果您想成为大企业的一部分，就必须遵循市场规则。

这枚奖章的另一面是对资本主义主要真理的痛苦实现——整个企业逐渐被迫迁移到云或支付昂贵的许可证费用。但是，如果有另一种方式——当您不需要为许可证付费，但同时可以自由使用SQL Server 商业版的所有重要优势时会怎样。

现在我们甚至都没有谈论开发者版，微软在2014年完全免费提供了它，尽管它之前愿意以59.95美元的价格出售。更有趣的是生产服务器的成本优化，当危机时期的客户要求最大限度地降低其设备业务成本时。

毫无疑问，现在您已经可以收拾行李并将逻辑迁移到免费的类似物，如PostgreSQL或MariaDB。但是立即出现了一个反问句——在每个人都需要“昨天”完成所有事情的情况下，谁来重写和测试它？并且即使是通过尝试快速迁移企业项目的意志坚强的决定，结果也更有可能成功扮演库尔特·柯本最喜欢的射击游戏而不是发布。因此，我们只会考虑如何在当前的技术限制下充分利用Express Edition。

SQL Server Express Edition初步诊断，医生学院做出：患者在一个socket内最多可以使用4个逻辑核，为Buffer Pool分配1GB多一点的内存，一个数据库文件的大小不能超过 10GB……谢谢，患者至少能够以某种方式行走，其余的以某种方式治愈。

实现

矛盾的是，首先要找出我们的SQL Server的版本。问题是，当SQL Server 2016 SP1于 2018年发布时，微软展示了慷慨的奇迹，并作为其新计划的一部分在功能上部分均衡了所有版本——一致的可编程表面区域 (CPSA)。

如果之前您必须着眼于特定版本编写代码，那么随着升级到 2016 SP1（及更高版本），许多企业功能可供使用，包括Express Edition。在Express Edition的新特性中，可以挑出以下几点：支持分区表和索引、创建列存储索引和内存中表，以及压缩表的能力。这是值得安装SQL Server升级的少数情况之一。

将Express Edition用于生产工作负载是否足够？

为了回答这个问题，让我们试着考虑几个场景。

让我们测试不同类型表的单线程OLTP工作负载，用于插入/更新/删除200,000行：

USE [master]
GO

SET NOCOUNT ON
SET STATISTICS IO, TIME OFF

IF DB_ID('express') IS NOT NULL BEGIN
    ALTER DATABASE [express] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [express]
END
GO

CREATE DATABASE [express]
ON PRIMARY (NAME = N'express', _
   FILENAME = N'X:\express.mdf', SIZE = 200 MB, FILEGROWTH = 100 MB)
   LOG ON (NAME = N'express_log', FILENAME = N'X:\express_log.ldf', _
   SIZE = 200 MB, FILEGROWTH = 100 MB)

ALTER DATABASE [express] SET AUTO_CLOSE OFF
ALTER DATABASE [express] SET RECOVERY SIMPLE
ALTER DATABASE [express] SET MULTI_USER
ALTER DATABASE [express] SET DELAYED_DURABILITY = ALLOWED
ALTER DATABASE [express] ADD FILEGROUP [MEM] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [express] ADD FILE (NAME = 'MEM', FILENAME = 'X:\MEM') TO FILEGROUP [MEM]
ALTER DATABASE [express] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO

USE [express]
GO

CREATE TABLE [T1_CL] (A INT PRIMARY KEY, B DATETIME INDEX IX1 NONCLUSTERED)
GO

CREATE TABLE [T2_MEM] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX1 NONCLUSTERED)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

CREATE TABLE [T3_MEM_NC] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX1 NONCLUSTERED)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

CREATE TABLE [T4_CL_DD] (A INT PRIMARY KEY, B DATETIME INDEX IX1 NONCLUSTERED)
GO

CREATE TABLE [T5_MEM_DD] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX1 NONCLUSTERED)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

CREATE TABLE [T6_MEM_NC_DD] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX1 NONCLUSTERED)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

CREATE TABLE [T7_MEM_SO] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX1 NONCLUSTERED)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

CREATE TABLE [T8_MEM_SO_NC] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX1 NONCLUSTERED)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

CREATE PROCEDURE [T3_MEM_I] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT INTO [dbo].[T3_MEM_NC] VALUES (@i, GETDATE())
END
GO

CREATE PROCEDURE [T3_MEM_U] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE [dbo].[T3_MEM_NC] SET B = GETDATE() WHERE A = @i
END
GO

CREATE PROCEDURE [T3_MEM_D] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    DELETE [dbo].[T3_MEM_NC] WHERE A = @i
END
GO

CREATE PROCEDURE [T6_MEM_I] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT INTO [dbo].[T6_MEM_NC_DD] VALUES (@i, GETDATE())
END
GO

CREATE PROCEDURE [T6_MEM_U] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE [dbo].[T6_MEM_NC_DD] SET B = GETDATE() WHERE A = @i
END
GO

CREATE PROCEDURE [T6_MEM_D] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    DELETE [dbo].[T6_MEM_NC_DD] WHERE A = @i
END
GO

CREATE PROCEDURE [T8_MEM_I] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT INTO [dbo].[T8_MEM_SO_NC] VALUES (@i, GETDATE())
END
GO

CREATE PROCEDURE [T8_MEM_U] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE [dbo].[T8_MEM_SO_NC] SET B = GETDATE() WHERE A = @i
END
GO

CREATE PROCEDURE [T8_MEM_D] (@i INT)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    DELETE [dbo].[T8_MEM_SO_NC] WHERE A = @i
END
GO

DECLARE @i INT
      , @s DATETIME
      , @runs INT = 200000

DROP TABLE IF EXISTS #stats
CREATE TABLE #stats (obj VARCHAR(100), op VARCHAR(100), time_ms BIGINT)

SELECT @i = 1, @s = GETDATE()
WHILE @i