十、性能监控与诊断

10.1 动态管理视图

-- 当前活动连接
SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    last_request_start_time,
    last_request_end_time,
    cpu_time,
    memory_usage
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- 当前执行的查询
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text AS SQLText,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50;

-- 阻塞信息
SELECT 
    blocking.session_id AS BlockingSession,
    blocked.session_id AS BlockedSession,
    blocking_text.text AS BlockingSQL,
    blocked_text.text AS BlockedSQL,
    blocking.wait_time AS WaitTime
FROM sys.dm_exec_requests blocking
INNER JOIN sys.dm_exec_requests blocked 
    ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text;

-- 缓存执行计划
SELECT 
    qs.execution_count,
    qs.total_worker_time / 1000 AS TotalCPUTime,
    qs.total_elapsed_time / 1000 AS TotalDuration,
    qs.total_logical_reads,
    qs.total_physical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

-- I/O 统计
SELECT 
    database_id,
    file_id,
    sample_ms,
    num_of_reads,
    num_of_writes,
    num_of_bytes_read,
    num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

10.2 性能监视器

-- 创建性能数据收集器
USE msdb;
GO

DECLARE @collection_set_id INT;

EXEC dbo.sp_syscollector_create_collection_set
    @name = 'Server Performance',
    @collection_mode = 0,
    @description = '收集服务器性能数据',
    @logging_level = 1,
    @days_until_expiration = 7,
    @schedule_name = 'CollectorSchedule_Every_15min',
    @collection_set_id = @collection_set_id OUTPUT;

-- 添加收集项
EXEC dbo.sp_syscollector_create_collection_item
    @collection_set_id = @collection_set_id,
    @collector_type_uid = 'performance_counter_collector',
    @name = 'Performance Counters',
    @parameters = N'
        <PerformanceCounters>
            <PerformanceCounter 
                category="Processor" 
                counter="% Processor Time" 
                instance="_Total" 
                name="CPU Usage"/>
            <PerformanceCounter 
                category="Memory" 
                counter="Available MBytes" 
                name="Available Memory"/>
            <PerformanceCounter 
                category="SQLServer:Buffer Manager" 
                counter="Buffer cache hit ratio" 
                name="Buffer Cache Hit Ratio"/>
        </PerformanceCounters>';

10.3 扩展事件

-- 创建扩展事件会话
CREATE EVENT SESSION LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION (sqlserver.sql_text, sqlserver.session_id)
    WHERE (duration > 5000000)  -- 5秒
)
ADD TARGET package0.event_file(
    SET filename = N'D:\XEvents\LongRunningQueries.xel',
    max_file_size = 100,
    max_rollover_files = 10
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

-- 启动会话
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;

-- 查询事件数据
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS EventName,
    event_data.value('(event/data[@name="duration"])[1]', 'BIGINT') / 1000 AS Duration_ms,
    event_data.value('(event/action[@name="sql_text"])[1]', 'VARCHAR(MAX)') AS SQLText,
    event_data.value('(event/action[@name="session_id"])[1]', 'INT') AS SessionID
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('D:\XEvents\LongRunningQueries*.xel', NULL, NULL, NULL)
) AS events
ORDER BY Duration_ms DESC;

十一、SQL Server 工具

11.1 SQL Server Management Studio (SSMS)

-- 使用 SSMS 功能
-- 1. 活动监视器（右键数据库 -> 活动监视器）
-- 2. 生成脚本（右键数据库 -> 任务 -> 生成脚本）
-- 3. 导入导出向导（右键数据库 -> 任务 -> 导入/导出数据）
-- 4. 数据库关系图（数据库 -> 数据库关系图）
-- 5. SQL Profiler（工具 -> SQL Server Profiler）

-- 生成数据库脚本示例
EXEC sp_helpdb 'SalesDB';  -- 查看数据库信息
EXEC sp_help 'Employees';  -- 查看表信息
EXEC sp_helpindex 'Employees';  -- 查看索引

11.2 SQLCMD 命令行工具

# 连接到 SQL Server
sqlcmd -S localhost -U sa -P password

# 执行查询
sqlcmd -S localhost -U sa -P password -Q "SELECT @@VERSION"

# 执行脚本文件
sqlcmd -S localhost -U sa -P password -i "D:\Scripts\CreateDB.sql"

# 导出查询结果
sqlcmd -S localhost -U sa -P password -Q "SELECT * FROM Employees" -o "D:\Output\Employees.csv" -s "," -W

# 使用 Windows 身份验证
sqlcmd -S localhost -E -Q "SELECT DB_NAME()"

# 使用变量
sqlcmd -S localhost -U sa -P password -v DatabaseName="SalesDB" -i "D:\Scripts\Backup.sql"

11.3 SQL Server PowerShell

# 加载 SQL Server 模块
Import-Module SqlServer

# 连接到 SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("localhost")

# 获取数据库列表
$server.Databases | Select-Object Name, Size, CreateDate

# 备份数据库
Backup-SqlDatabase -ServerInstance "localhost" -Database "SalesDB" -BackupFile "D:\Backups\SalesDB.bak"

# 执行查询
Invoke-Sqlcmd -ServerInstance "localhost" -Database "SalesDB" -Query "SELECT * FROM Employees"

# 使用 SQL Server Agent
$job = $server.JobServer.Jobs["Daily Database Maintenance"]
$job.Start()

十二、SQL Server 2019/2022 新特性

12.1 智能查询处理

-- 自适应连接（SQL Server 2019+）
ALTER DATABASE SalesDB SET QUERY_OPTIMIZER_HOTFIXES = ON;

-- 行模式内存授予反馈
ALTER DATABASE SalesDB SET MEMORY_GRANT_FEEDBACK = ON;

-- 表变量延迟编译
-- SQL Server 2019 自动启用

-- 标量函数内联
-- 使用 WITH INLINE = ON 选项
CREATE FUNCTION dbo.GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
WITH INLINE = ON
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

-- 近似查询处理
SELECT APPROX_COUNT_DISTINCT(EmployeeID) AS ApproxCount
FROM Employees;

12.2 大数据群集

-- 外部数据源（数据虚拟化）
CREATE EXTERNAL DATA SOURCE HdfsSource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://namenode:8020',
    CREDENTIAL = HdfsCredential
);

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

CREATE EXTERNAL TABLE SalesExternal (
    SaleID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
)
WITH (
    LOCATION = '/data/sales/',
    DATA_SOURCE = HdfsSource,
    FILE_FORMAT = ParquetFormat
);

12.3 查询存储

-- 启用查询存储
ALTER DATABASE SalesDB SET QUERY_STORE = ON;

-- 配置查询存储
ALTER DATABASE SalesDB SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO
);

-- 查询查询存储数据
SELECT 
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    rs.count_executions
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

-- 强制使用特定计划
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;

SQL Server 作为企业级数据库平台，提供了从数据存储、查询优化到高可用、商业智能的全方位解决方案。本文系统性地梳理了 SQL Server 的核心知识点，从基础概念到高级特性，从日常管理到性能优化，帮助读者建立完整的知识体系。
来源：
https://app-adtysnu98v0h.appmiaoda.com