# 1. 配置内存参数

— 查看当前内存配置
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO

EXEC sp_configure ‘max server memory’;
EXEC sp_configure ‘min server memory’;
GO

— 配置最大服务器内存
EXEC sp_configure ‘max server memory’, 65536; — 64GB
RECONFIGURE;
GO

— 配置最小服务器内存
EXEC sp_configure ‘min server memory’, 16384; — 16GB
RECONFIGURE;
GO

— 启用针对临时工作负载的优化
EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;
GO

# 2. 内存使用监控

— 监控内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_mb,
large_page_allocations_kb / 1024 AS large_page_allocations_mb,
locked_page_allocations_kb / 1024 AS locked_page_allocations_mb,
virtual_address_space_committed_kb / 1024 AS virtual_address_space_committed_mb,
memory_utilization_percentage,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
GO

— 监控缓冲池使用情况
SELECT
type,
name,
pages_kb / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks
WHERE type LIKE ‘%buffer pool%’
ORDER BY pages_kb DESC;
GO

— 监控内存压力
SELECT
session_id,
request_id,
start_time,
status,
command,
memory_usage,
granted_query_memory,
total_elapsed_time,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE memory_usage > 0
ORDER BY memory_usage DESC;
GO

# 3. 内存优化建议

— 识别内存密集型查询
SELECT
qs.query_hash,
qs.total_worker_time / qs.execution_count AS avg_cpu_time_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.execution_count,
st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_logical_reads DESC
TOP 10;
GO

— 优化内存密集型查询
— 例如，添加适当的索引
CREATE INDEX IX_fgedu_sales_region_date ON dbo.fgedu_sales(region, sale_date) INCLUDE (total_amount);
GO

# 1. 配置CPU参数

— 查看当前CPU配置
EXEC sp_configure ‘max degree of parallelism’;
EXEC sp_configure ‘cost threshold for parallelism’;
GO

— 配置最大并行度
EXEC sp_configure ‘max degree of parallelism’, 4; — 根据CPU核心数设置
RECONFIGURE;
GO

— 配置并行度阈值
EXEC sp_configure ‘cost threshold for parallelism’, 50;
RECONFIGURE;
GO

# 2. CPU使用监控

— 监控CPU使用情况
SELECT
TOP 10
session_id,
request_id,
start_time,
status,
command,
cpu_time,
total_elapsed_time,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE cpu_time > 0
ORDER BY cpu_time DESC;
GO

— 监控等待统计信息
SELECT
wait_type,
wait_time_ms,
signal_wait_time_ms,
wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC
TOP 10;
GO

# I/O优化

# 1. 存储配置

— 查看数据库文件配置
SELECT
name,
physical_name,
size * 8 / 1024 AS size_mb,
max_size * 8 / 1024 AS max_size_mb,
growth * 8 / 1024 AS growth_mb
FROM sys.database_files;
GO

— 优化tempdb配置
— 创建多个tempdb数据文件
ALTER DATABASE tempdb ADD FILE (
NAME = ‘tempdb_data2’,
FILENAME = ‘/sqlserver/fgdata/tempdb_data2.ndf’,
SIZE = 1024MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 512MB
);
GO

# 2. I/O使用监控

— 监控I/O统计信息
SELECT
database_id,
file_id,
io_stall_read_ms,
num_of_reads,
io_stall_write_ms,
num_of_writes,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_stall_ms,
io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms + io_stall_write_ms DESC;
GO

— 监控磁盘使用情况
SELECT
volume_mount_point,
total_bytes / 1024 / 1024 / 1024 AS total_gb,
available_bytes / 1024 / 1024 / 1024 AS available_gb,
(available_bytes * 100.0) / total_bytes AS available_percent
FROM sys.dm_os_volume_stats(NULL, NULL);
GO

# 1. 系统配置

— 硬件配置
— CPU: 16核心
— 内存: 128GB
— 存储: SSD存储，RAID 10

— 数据库配置
— 数据文件: 多文件组，按功能分区
— 日志文件: 独立存储
— tempdb: 8个数据文件

# 2. 内存优化

— 配置最大服务器内存
EXEC sp_configure ‘max server memory’, 104857; — 102GB
RECONFIGURE;
GO

— 启用针对临时工作负载的优化
EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;
GO

# 3. CPU优化

— 配置最大并行度
EXEC sp_configure ‘max degree of parallelism’, 8;
RECONFIGURE;
GO

— 配置并行度阈值
EXEC sp_configure ‘cost threshold for parallelism’, 50;
RECONFIGURE;
GO

# 4. I/O优化

— 优化数据文件布局
ALTER DATABASE fgedudb ADD FILEGROUP [fg_data_1];
GO

ALTER DATABASE fgedudb ADD FILE (
NAME = ‘fgedudb_data_1’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_data_1.ndf’,
SIZE = 100GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10GB
) TO FILEGROUP [fg_data_1];
GO

— 优化tempdb配置
ALTER DATABASE tempdb MODIFY FILE (
NAME = ‘tempdev’,
SIZE = 2GB,
FILEGROWTH = 512MB
);
GO

# 5. 查询优化

— 创建适当的索引
CREATE INDEX IX_fgedu_orders_customer_date ON dbo.fgedu_orders(customer_id, order_date) INCLUDE (total_amount, status);
GO

— 更新统计信息
UPDATE STATISTICS dbo.fgedu_orders;
GO

# 6. 监控与维护

— 创建定期维护作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’FGEDU Maintenance’,
@enabled = 1,
@description = N’Regular maintenance tasks’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’FGEDU Maintenance’,
@step_name = N’Update Statistics’,
@subsystem = N’TSQL’,
@command = N’EXEC sp_updatestats;’,
@database_name = N’fgedudb’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’FGEDU Maintenance’,
@step_name = N’Reorganize Indexes’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.usp_reorganize_indexes;’,
@database_name = N’fgedudb’;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Weekly Maintenance’,
@freq_type = 8,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 20000;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’FGEDU Maintenance’,
@schedule_name = N’Weekly Maintenance’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’FGEDU Maintenance’;
GO