当数据爆炸遇上SQL Server：优化策略全链路解析

SET SHOWPLAN_XML ON;GOSELECT * FROM Orders WHERE CustomerID = 'VINET';GO

EXECUTE dbo.IndexOptimize@Databases = 'USER_DATABASES',@FragmentationLow = NULL,@FragmentationMedium = 'INDEX_REORGANIZE',@FragmentationHigh = 'INDEX_REBUILD';

-- 创建分区函数CREATE PARTITION FUNCTION OrderDatePF (datetime)AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01');-- 创建分区方案CREATE PARTITION SCHEME OrderDatePSAS PARTITION OrderDatePF TO (fg_2022, fg_2023, fg_2024);-- 创建分区表CREATE TABLE Orders (    OrderID INT PRIMARY KEY,    OrderDate DATETIME,    CustomerID NVARCHAR(5)) ON OrderDatePS(OrderDate);

ALTER DATABASE Sales  ADD FILEGROUP hist_fg;ALTER DATABASE Sales  ADD FILE ( NAME = hist_data,      FILENAME = 'D:\SlowDisk\Sales_hist.ndf')  TO FILEGROUP hist_fg;

CREATE EVENT SESSION [HADR_Latency] ON SERVER  ADD EVENT sqlserver.hadr_apply_vfs_io_completion  ADD TARGET package0.event_file(...)

CREATE COLUMNSTORE INDEX CCSI_OrdersON Orders (OrderID, ProductID, Quantity);

CREATE TABLE SessionCache (    SessionID NVARCHAR(128) PRIMARY KEY NONCLUSTERED,    Data VARBINARY(MAX)) WITH (MEMORY_OPTIMIZED = ON);

New-AzSqlElasticPool -ResourceGroupName "Group01" -ServerName "Server01" -ElasticPoolName "ElasticPool01" -Dtu 200 -DatabaseDtuMin 10 -DatabaseDtuMax 100

CREATE EXTERNAL DATA SOURCE MongoDB WITH (    LOCATION = 'mongodb://mongoserver:27017',    CREDENTIAL = MongoCred);
SELECT * FROM OpenQuery(MongoDB, 'SalesDB.Orders.find()');

// 创建分片映射管理器var shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(    connectionString, ShardMapManagerLoadPolicy.Lazy);
// 添加分片var shard = shardMapManager.CreateListShardMapint>("CustomerShard")    .CreateShard(new ShardLocation("ServerA", "ShardDB1"));

SELECT o.OrderID, c.CompanyName FROM Sharded.Orders oINNER JOIN Sharded.Customers c ON o.CustomerID = c.CustomerID;