# 检查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,
cpu_count,
hyperthread_ratio,
socket_count
FROM sys.dm_os_sys_info;
GO

physical_memory_kb cpu_count hyperthread_ratio socket_count
—————— ——— —————– ————
65536 16 2 2

对于列存储索引环境，需要确保SQLServer版本为2012或更高，数据库兼容性级别为110或更高，并且服务器配置充足，建议至少32GB内存，8核CPU。from SQLServer视频:www.itpux.com。

2.2 SQLServer列存储索引配置建议

SQLServer列存储索引配置建议包括：

1. 数据量：对于大数据量（通常大于1000万行）的表，使用列存储索引效果最佳

2. 数据类型：适合存储大量重复值的列，如维度表、事实表

3. 查询模式：适合聚合查询、范围查询、分析查询等

4. 存储配置：为列存储索引配置高速存储，如SSD

5. 内存配置：确保有足够的内存用于列存储索引的缓存

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

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

3.1 SQLServer列存储索引高级配置实施

# 创建测试表
CREATE TABLE dbo.fgedu_sales (
sales_id INT IDENTITY(1,1) PRIMARY KEY,
product_id INT NOT NULL,
customer_id INT NOT NULL,
sale_date DATE NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
region NVARCHAR(50) NOT NULL,
salesperson NVARCHAR(100) NOT NULL
);
GO

# 插入测试数据
DECLARE @i INT = 1;
WHILE @i <= 1000000 BEGIN INSERT INTO dbo.fgedu_sales ( product_id, customer_id, sale_date, quantity, unit_price, total_amount, region, salesperson ) VALUES ( @i % 1000 + 1, @i % 500 + 1, DATEADD(day, @i % 365, '2023-01-01'), @i % 10 + 1, 10.00 + (@i % 100), (10.00 + (@i % 100)) * (@i % 10 + 1), CASE WHEN @i % 4 = 0 THEN 'North' WHEN @i % 4 = 1 THEN 'South' WHEN @i % 4 = 2 THEN 'East' ELSE 'West' END, 'Salesperson ' + CAST((@i % 10 + 1) AS NVARCHAR(10)) ); SET @i = @i + 1; END; GO # 创建聚集列存储索引 CREATE CLUSTERED COLUMNSTORE INDEX CCI_fgedu_sales ON dbo.fgedu_sales; GO # 查看列存储索引 SELECT i.name AS index_name, i.type_desc AS index_type, i.is_primary_key, i.is_unique FROM sys.indexes i WHERE i.object_id = OBJECT_ID('dbo.fgedu_sales'); GO index_name index_type is_primary_key is_unique -------------- --------------------- -------------- --------- CCI_fgedu_sales CLUSTERED COLUMNSTORE 0 0 # 创建非聚集列存储索引 CREATE TABLE dbo.fgedu_products ( product_id INT PRIMARY KEY, product_name NVARCHAR(100) NOT NULL, category NVARCHAR(50) NOT NULL, price DECIMAL(10, 2) NOT NULL, stock INT NOT NULL, supplier NVARCHAR(100) NOT NULL ); GO # 插入测试数据 INSERT INTO dbo.fgedu_products ( product_id, product_name, category, price, stock, supplier ) VALUES (1, 'Product 1', 'Category A', 100.00, 1000, 'Supplier 1'), (2, 'Product 2', 'Category A', 200.00, 500, 'Supplier 2'), (3, 'Product 3', 'Category B', 150.00, 800, 'Supplier 1'), (4, 'Product 4', 'Category B', 250.00, 300, 'Supplier 3'), (5, 'Product 5', 'Category C', 300.00, 200, 'Supplier 2'); GO # 创建非聚集列存储索引 CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_fgedu_products ON dbo.fgedu_products ( product_name, category, price, stock, supplier ); GO # 查看列存储索引 SELECT i.name AS index_name, i.type_desc AS index_type, i.is_primary_key, i.is_unique FROM sys.indexes i WHERE i.object_id = OBJECT_ID('dbo.fgedu_products'); GO index_name index_type is_primary_key is_unique ------------------ ----------------------- -------------- --------- PK__fgedu_pr__47027DF5A1234567 CLUSTERED 1 1 NCCI_fgedu_products NONCLUSTERED COLUMNSTORE 0 0

# 优化列存储索引性能

# 1. 调整批处理大小
EXEC sp_configure ‘max server memory’, 61440;
RECONFIGURE;
GO

# 2. 优化列存储索引压缩
ALTER INDEX CCI_fgedu_sales ON dbo.fgedu_sales REBUILD WITH (
DATA_COMPRESSION = COLUMNSTORE
);
GO

# 3. 优化增量存储
ALTER INDEX CCI_fgedu_sales ON dbo.fgedu_sales REORGANIZE;
GO

# 4. 监控列存储索引状态
SELECT
object_name(object_id) AS table_name,
index_id,
index_type_desc,
avg_fragmentation_in_percent,
fragment_count,
page_count
FROM sys.dm_db_index_physical_stats(
DB_ID(‘fgedudb’),
OBJECT_ID(‘dbo.fgedu_sales’),
NULL,
NULL,
‘DETAILED’
);
GO

# 5. 优化查询性能
— 使用列存储索引的查询
SELECT
region,
SUM(total_amount) AS total_sales,
AVG(unit_price) AS avg_price,
COUNT(*) AS order_count
FROM dbo.fgedu_sales
GROUP BY region;
GO

# 6. 分区列存储索引
— 创建分区函数
CREATE PARTITION FUNCTION SalesDateRangePF (DATE)
AS RANGE RIGHT FOR VALUES (
‘2023-04-01’, ‘2023-07-01’, ‘2023-10-01’
);
GO

— 创建分区方案
CREATE PARTITION SCHEME SalesDateRangePS
AS PARTITION SalesDateRangePF
ALL TO ([PRIMARY]);
GO

— 创建分区表
CREATE TABLE dbo.fgedu_sales_partitioned (
sales_id INT IDENTITY(1,1),
product_id INT NOT NULL,
customer_id INT NOT NULL,
sale_date DATE NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
region NVARCHAR(50) NOT NULL,
salesperson NVARCHAR(100) NOT NULL
) ON SalesDateRangePS(sale_date);
GO

— 插入测试数据
DECLARE @i INT = 1;
WHILE @i <= 1000000 BEGIN INSERT INTO dbo.fgedu_sales_partitioned ( product_id, customer_id, sale_date, quantity, unit_price, total_amount, region, salesperson ) VALUES ( @i % 1000 + 1, @i % 500 + 1, DATEADD(day, @i % 365, '2023-01-01'), @i % 10 + 1, 10.00 + (@i % 100), (10.00 + (@i % 100)) * (@i % 10 + 1), CASE WHEN @i % 4 = 0 THEN 'North' WHEN @i % 4 = 1 THEN 'South' WHEN @i % 4 = 2 THEN 'East' ELSE 'West' END, 'Salesperson ' + CAST((@i % 10 + 1) AS NVARCHAR(10)) ); SET @i = @i + 1; END; GO -- 创建分区列存储索引 CREATE CLUSTERED COLUMNSTORE INDEX CCI_fgedu_sales_partitioned ON dbo.fgedu_sales_partitioned; GO -- 查看分区信息 SELECT OBJECT_NAME(p.object_id) AS table_name, p.partition_number, p.partition_id, i.name AS index_name, p.rows FROM sys.partitions p JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE p.object_id = OBJECT_ID('dbo.fgedu_sales_partitioned'); GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer列存储索引高级实战案例

# 案例1：列存储索引性能测试

# 测试行存储表查询性能
CREATE TABLE dbo.fgedu_sales_rowstore (
sales_id INT IDENTITY(1,1) PRIMARY KEY,
product_id INT NOT NULL,
customer_id INT NOT NULL,
sale_date DATE NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
region NVARCHAR(50) NOT NULL,
salesperson NVARCHAR(100) NOT NULL
);
GO

# 插入测试数据
INSERT INTO dbo.fgedu_sales_rowstore
SELECT * FROM dbo.fgedu_sales;
GO

# 测试行存储表查询性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
region,
SUM(total_amount) AS total_sales,
AVG(unit_price) AS avg_price,
COUNT(*) AS order_count
FROM dbo.fgedu_sales_rowstore
GROUP BY region;

DECLARE @end_time DATETIME = GETDATE();
SELECT DATEDIFF(ms, @start_time, @end_time) AS query_time_ms;
GO

query_time_ms
————-
1250

# 测试列存储表查询性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
region,
SUM(total_amount) AS total_sales,
AVG(unit_price) AS avg_price,
COUNT(*) AS order_count
FROM dbo.fgedu_sales
GROUP BY region;

DECLARE @end_time DATETIME = GETDATE();
SELECT DATEDIFF(ms, @start_time, @end_time) AS query_time_ms;
GO

query_time_ms
————-
150

# 性能对比
— 行存储表：1250ms
— 列存储表：150ms
— 列存储索引性能提升约8.3倍

# 案例2：增量更新测试

# 插入新数据到列存储表
DECLARE @i INT = 1;
WHILE @i <= 100000 BEGIN INSERT INTO dbo.fgedu_sales ( product_id, customer_id, sale_date, quantity, unit_price, total_amount, region, salesperson ) VALUES ( @i % 1000 + 1, @i % 500 + 1, DATEADD(day, @i % 365, '2024-01-01'), @i % 10 + 1, 10.00 + (@i % 100), (10.00 + (@i % 100)) * (@i % 10 + 1), CASE WHEN @i % 4 = 0 THEN 'North' WHEN @i % 4 = 1 THEN 'South' WHEN @i % 4 = 2 THEN 'East' ELSE 'West' END, 'Salesperson ' + CAST((@i % 10 + 1) AS NVARCHAR(10)) ); SET @i = @i + 1; END; GO # 查看增量存储状态 SELECT object_name(object_id) AS table_name, index_id, partition_number, row_group_id, total_rows, deleted_rows, state_desc, data_compression_desc FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID('dbo.fgedu_sales'); GO # 重组列存储索引 ALTER INDEX CCI_fgedu_sales ON dbo.fgedu_sales REORGANIZE; GO # 再次查看增量存储状态 SELECT object_name(object_id) AS table_name, index_id, partition_number, row_group_id, total_rows, deleted_rows, state_desc, data_compression_desc FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID('dbo.fgedu_sales'); GO # 案例3：分区列存储索引查询性能 # 测试分区列存储索引查询性能 DECLARE @start_time DATETIME = GETDATE(); SELECT region, SUM(total_amount) AS total_sales, AVG(unit_price) AS avg_price, COUNT(*) AS order_count FROM dbo.fgedu_sales_partitioned WHERE sale_date BETWEEN '2023-04-01' AND '2023-06-30' GROUP BY region; DECLARE @end_time DATETIME = GETDATE(); SELECT DATEDIFF(ms, @start_time, @end_time) AS query_time_ms; GO query_time_ms ------------- 80 # 测试非分区列存储索引查询性能 DECLARE @start_time DATETIME = GETDATE(); SELECT region, SUM(total_amount) AS total_sales, AVG(unit_price) AS avg_price, COUNT(*) AS order_count FROM dbo.fgedu_sales WHERE sale_date BETWEEN '2023-04-01' AND '2023-06-30' GROUP BY region; DECLARE @end_time DATETIME = GETDATE(); SELECT DATEDIFF(ms, @start_time, @end_time) AS query_time_ms; GO query_time_ms ------------- 120 # 性能对比 -- 分区列存储索引：80ms -- 非分区列存储索引：120ms -- 分区列存储索引性能提升约1.5倍

# 创建列存储索引自动化脚本
CREATE PROCEDURE dbo.usp_columnstore_automation
AS
BEGIN
— 检查列存储索引状态
SELECT
object_name(object_id) AS table_name,
index_id,
partition_number,
row_group_id,
total_rows,
deleted_rows,
state_desc,
data_compression_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id IN (
SELECT object_id FROM sys.tables WHERE name LIKE ‘fgedu%’
);

— 重组列存储索引
DECLARE @table_name NVARCHAR(128);
DECLARE @index_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

DECLARE columnstore_cursor CURSOR FOR
SELECT
t.name AS table_name,
i.name AS index_name
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
WHERE i.type_desc LIKE ‘%COLUMNSTORE%’ AND t.name LIKE ‘fgedu%’;

OPEN columnstore_cursor;
FETCH NEXT FROM columnstore_cursor INTO @table_name, @index_name;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ‘ + QUOTENAME(@index_name) + ‘ ON ‘ + QUOTENAME(@table_name) + ‘ REORGANIZE;’;
EXEC sp_executesql @sql;
FETCH NEXT FROM columnstore_cursor INTO @table_name, @index_name;
END;

CLOSE columnstore_cursor;
DEALLOCATE columnstore_cursor;

— 检查列存储索引大小
SELECT
object_name(object_id) AS table_name,
index_id,
partition_number,
SUM(in_row_data_page_count) * 8 / 1024 AS in_row_data_mb,
SUM(blob_data_page_count) * 8 / 1024 AS blob_data_mb,
SUM(row_overflow_used_page_count) * 8 / 1024 AS row_overflow_mb
FROM sys.dm_db_partition_stats
WHERE object_id IN (
SELECT object_id FROM sys.tables WHERE name LIKE ‘fgedu%’
)
GROUP BY object_id, index_id, partition_number;
END;
GO

# 创建自动化作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Columnstore Index Automation’,
@enabled = 1,
@description = N’Automate columnstore index tasks’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Columnstore Index Automation’,
@step_name = N’Run automation’,
@subsystem = N’TSQL’,
@command = N’USE fgedudb;
EXEC dbo.usp_columnstore_automation;’,
@retry_attempts = 3,
@retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Columnstore Index Automation Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 020000;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Columnstore Index Automation’,
@schedule_name = N’Columnstore Index Automation Schedule’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Columnstore Index Automation’,
@server_name = N'(local)’;
GO

# 查看列存储索引状态
SELECT
object_name(object_id) AS table_name,
index_id,
partition_number,
row_group_id,
total_rows,
deleted_rows,
state_desc,
data_compression_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(‘dbo.fgedu_sales’);
GO

风哥提示：定期重组列存储索引，优化增量存储，提高查询性能。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer列存储索引最佳实践

1. 为大数据量的表（通常大于1000万行）创建列存储索引

2. 选择合适的列存储索引类型：聚集列存储索引适用于数据仓库表，非聚集列存储索引适用于需要保留行存储索引的表

3. 定期重组列存储索引，优化增量存储

4. 结合分区表使用列存储索引，提高管理和查询性能

5. 为列存储索引配置足够的内存，提高查询性能

6. 监控列存储索引的状态，及时发现并解决问题

7. 合理设计数据模型，优化列存储索引的使用效果

5.2 SQLServer列存储索引常见问题与解决方案

# 问题1：列存储索引查询性能下降
# 解决方案：重组列存储索引，优化增量存储，检查内存使用情况

# 问题2：列存储索引存储空间不足
# 解决方案：清理过期数据，增加存储容量，优化数据压缩

# 问题3：列存储索引更新性能问题
# 解决方案：使用增量更新功能，避免频繁大规模更新

# 问题4：列存储索引维护成本高
# 解决方案：自动化维护作业，定期重组索引

# 问题5：列存储索引与其他索引冲突
# 解决方案：合理设计索引策略，避免不必要的索引

# 检查列存储索引状态
SELECT
object_name(object_id) AS table_name,
index_id,
partition_number,
row_group_id,
total_rows,
deleted_rows,
state_desc,
data_compression_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(‘dbo.fgedu_sales’);
GO

# 重组列存储索引
ALTER INDEX CCI_fgedu_sales ON dbo.fgedu_sales REORGANIZE;
GO

# 重建列存储索引
ALTER INDEX CCI_fgedu_sales ON dbo.fgedu_sales REBUILD;
GO

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