SQL Server 巡检：保障系统的健康运行

1. 前言

对于企业的信息系统来说，数据库是数据存储、管理和分析的核心。其中，SQL Server 是一个特别受欢迎的关系型数据库管理系统，在企业中得到广泛应用。然而，随着数据量的增长和系统复杂性的提高，SQL Server 对于系统性能和安全管理的要求也越来越高。因此，SQL Server 巡检成为了保证系统运行健康的一项重要工作。

2. SQL Server 巡检的目的

SQL Server 巡检是一项评估数据库实例性能、健康状况和安全性的过程。通过对数据库的配置、硬件资源使用、数据库对象健康状况和运行状态进行细致的检查和分析，可以及时发现问题，预防故障，保证系统的可靠性、可用性和安全性。

2.1 SQL Server 巡检的基本要求

在进行 SQL Server 巡检之前，我们需要明确以下要求：

对 SQL Server 的架构和功能有深入的了解，对关系型数据库原理和 SQL 语言有扎实的基础；

熟悉 SQL Server 的配置和管理方法，掌握 SQL Server Performance Monitor 的使用；

熟悉 Windows 的系统管理和网络管理知识。

3. SQL Server 巡检的内容

SQL Server 巡检的主要内容包括：

硬件资源：检查服务器的硬件资源使用情况，如 CPU、内存、硬盘和网络等，以及其对 SQL Server 的影响。

SQL Server 配置：检查 SQL Server 的配置参数，如最大内存、并发连接数、最大线程数等，以及与数据库性能和安全相关的配置。

数据库对象：检查数据库的表、索引、视图等对象的存储、使用、健康状况，以及与之相关的存储过程、触发器等对象的执行情况和效率。

数据库系统视图：使用 SQL Server 系统视图，对数据库的运行状态、性能指标进行监控和评估，如 sys.dm_exec_query_stats、sys.dm_io_virtual_file_stats 等。

安全管理：检查数据库和 SQL Server 实例的安全设置，确保其能够抵御各种外部和内部安全威胁。

备份和恢复：检查 SQL Server 的备份和恢复策略，确保数据的完整性和可靠性。

4. SQL Server 巡检的步骤

SQL Server 巡检的具体步骤如下：

4.1 收集基础信息

在进行 SQL Server 巡检之前，我们需要了解数据库的基础信息，包括 SQL Server 版本和补丁、服务器配置、数据库对象数量和大小、连接数、用户和权限等。这个过程可以使用 SQL Server Management Studio 或 SQL Server Profiler 完成。

SELECT @@VERSION;

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');
SELECT physical_memory_in_use_kb/1024 AS sql_memory_usage_mb from sys.dm_os_process_memory;

4.2 检查硬件资源和操作系统设置

SQL Server 对于硬件资源和操作系统设置的要求比较高，因此需要检查服务器的硬件配置和操作系统设置，包括 CPU、内存、硬盘和网络等。并根据实际情况修改相关设置，以优化 SQL Server 的运行。

EXEC xp_readerrorlog 0, 1, N'Memory';

4.3 检查 SQL Server 配置选项

SQL Server 配置选项对于数据库性能和安全至关重要，需要结合数据库的实际情况来进行设置。巡检时需要检查以下参数：

最大内存（Max Server Memory）

并发连接数（Max User Connections）

最大线程数（Max Worker Threads）

日志文件大小和数量（Log File Size、Number of Log Files）

最大错误日志数量（Error Log Maximum Files）

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;
EXEC sp_configure; -- 查看参数列表
EXEC sp_configure 'max server memory';
EXEC sp_configure 'max degree of parallelism';

4.4 检查数据库对象和存储过程的健康状况

数据库对象和存储过程等对于系统性能和安全也具有重要的作用。巡检时需要检查以下内容：

表、视图、索引、存储过程和触发器的定义和使用情况

存储过程和触发器的执行计划和效率

检查与存储过程相关的事件和错误日志。

SELECT * FROM sys.objects WHERE type_desc IN ('USER_TABLE','VIEW','SQL_TRIGGER','SQL_STORED_PROCEDURE') order by type_desc, name;

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id), cached_time, last_execution_time, execution_count, total_elapsed_time, total_worker_time, total_logical_reads, total_physical_reads, total_logical_writes, query_plan FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID();

4.5 监控 SQL Server 性能

SQL Server 通过系统视图和 Performance Monitor 等工具可以获得对系统性能的详细监控。巡检时需要关注以下指标：

CPU 使用率

内存使用情况

磁盘读取和写入速度

缓存命中率

网络带宽使用率

SELECT total_physical_memory_kb/1024 AS mem_size_mb, available_physical_memory_kb/1024 AS free_mem_mb, virtual_memory_reserve_kb/1024 AS reserved_mem_mb, Memory_utilization_percentage, memory_utilization_kb/1024 AS mem_util_mb, large_page_allocations_kb/1024 AS large_page_allocations_mb FROM sys.dm_os_sys_memory;

SELECT TOP (50) total_worker_time/execute_count AS AvgCPU, total_logical_reads+total_logical_writes AS TotIO, total_logical_reads/execute_count AS AvgReads, total_logical_writes/execute_count AS AvgWrites, execution_count, total_elapsed_time/1000000 AS ElapsedMS, qs.creation_time, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS QueryTxt, qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid=? AND qs.last_execution_time>=? ORDER BY total_worker_time/execute_count DESC;

4.6 检查 SQL Server 安全配置

SQL Server 安全配置对于数据库和系统安全具有重要作用，需要检查以下内容：

检查登录账户和密码的复杂度和强度

检查数据库角色和用户的权限和访问控制设置

检查 Windows 安全设置和防火墙设置

SELECT * FROM sys.sql_logins;

SELECT * FROM sys.database_principals WHERE type IN ('U', 'G', 'S');
SELECT * FROM sys.server_principals WHERE type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SERVER_ROLE'); 

4.7 备份和恢复策略

SQL Server 备份和恢复策略对于系统可靠性和数据恢复至关重要，需要检查以下内容：

数据库备份和恢复的策略和计划

备份使用的硬件和介质，如磁盘、磁带、网络等

恢复测试的过程和结果

SELECT DB.name AS 'Database_Name', MAX(bk.backup_finish_date) AS 'Last Backup Finish Date', DATEDIFF(day, MAX(bk.backup_finish_date), GETDATE()) AS 'Days Since Last Backup' FROM sys.databases AS DB LEFT JOIN msdb.dbo.backupset AS bk ON DB.name = bk.database_name WHERE DB.state = 0 AND DB.recovery_model_desc = 'FULL' GROUP BY DB.name, DB.recovery_model_desc;

5. 总结

SQL Server 巡检是保障企业信息系统运行健康和可靠性的一项重要工作，主要内容包括对 SQL Server 配置、硬件资源使用、数据库对象健康状况和运行状态进行检查和评估。通过巡检可以及时发现问题，预防故障，保证系统的可靠性、可用性和安全性。同时，SQL Server 巡检需要专业的技能和工具支持，需要企业建立完备的巡检机制。