-- SQL Server深度巡检脚本
SET NOCOUNT ON;
-- 1. 系统信息
SELECT
SERVERPROPERTY('MachineName') AS [MachineName],
SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [InstanceName],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],
@@VERSION AS [VersionInfo]
-- 2. 配置参数（部分重要参数）
SELECT name, value_in_use, description
FROM sys.configurations
WHERE name IN (
'max server memory (MB)',
'min server memory (MB)',
'cost threshold for parallelism',
'max degree of parallelism',
'optimize for ad hoc workloads'
)
-- 3. 数据库基本信息
SELECT
name,
state_desc,
recovery_model_desc,
log_reuse_wait_desc,
user_access_desc,
compatibility_level
FROM sys.databases
-- 4. 磁盘空间使用情况（按数据库文件）
SELECT
DB_NAME(database_id) AS [DatabaseName],
name AS [FileName],
type_desc,
physical_name,
size * 8 / 1024 AS [SizeMB],
FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS [UsedMB],
(size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS [FreeMB]
FROM sys.master_files
-- 5. 性能计数器（部分关键计数器，通过动态管理视图）
-- 5.1 缓冲池使用情况
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;
-- 5.2 页面预期寿命（Page Life Expectancy）
SELECT [object_name], cntr_value AS [PageLifeExpectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' AND counter_name = 'Page life expectancy'
-- 6. 索引状态（碎片和缺失索引）
-- 6.1 索引碎片
SELECT
DB_NAME(ips.database_id) AS [Database],
OBJECT_NAME(ips.object_id) AS [Table],
si.name AS [Index],
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes si ON ips.object_id = si.object_id AND ips.index_id = si.index_id
WHERE ips.avg_fragmentation_in_percent > 30 -- 碎片大于30%的索引
-- 6.2 缺失索引
SELECT
mid.statement AS [Database.Table],
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS [Improvement_Measure],
'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(mid.equality_columns, ', ', '_'), '[', '') +
CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE '_' + REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', '') END +
'] ON ' + mid.statement + ' (' +
ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END +
ISNULL(mid.inequality_columns, '') + ')' +
ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS [CreateIndexScript]
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) > 10
ORDER BY [Improvement_Measure] DESC
-- 7. 等待事件（按等待类型统计）
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'WAITFOR', 'LOGMGR_QUEUE')
ORDER BY wait_time_ms DESC
-- 8. 慢查询（需要启用查询存储或开启跟踪，这里提供查询存储的示例，需SQL Server 2016+）
-- 假设数据库已启用查询存储
SELECT
qsrs.avg_duration,
qsrs.avg_logical_io_reads,
qsrs.avg_rowcount,
qst.query_sql_text
FROM sys.query_store_runtime_stats qsrs
JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qst ON qsq.query_text_id = qst.query_text_id
WHERE qsrs.last_execution_time > DATEADD(day, -1, GETDATE()) -- 最近一天
ORDER BY qsrs.avg_duration DESC
-- 如果没有启用查询存储，可以考虑使用sys.dm_exec_query_stats，但只能统计自上次重启以来的数据
-- 9. 死锁监控（需要开启死锁跟踪或使用扩展事件，这里查询系统健康会话中的死锁信息）
IF OBJECT_ID('tempdb..#deadlock_report') IS NOT NULL DROP TABLE #deadlock_report
CREATE TABLE #deadlock_report (EventXML XML)
INSERT INTO #deadlock_report
SELECT CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE event_data LIKE '%<event name="xml_deadlock_report"%'
SELECT
EventXML.value('(event/@timestamp)[1]', 'datetime') AS [Time],
EventXML.query('//event/data/value/deadlock') AS DeadlockGraph
FROM #deadlock_report
-- 10. 作业状态（最近24小时失败作业）
SELECT
sj.name AS [JobName],
sjh.run_status,
sjh.run_date,
sjh.run_time
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
WHERE sjh.run_status = 0 -- 失败
AND CONVERT(DATETIME, CONVERT(CHAR(8), sjh.run_date, 112) + ' ' +
STUFF(STUFF(REPLACE(STR(sjh.run_time,6,0),' ','0'),3,0,':'),6,0,':')) > DATEADD(day, -1, GETDATE())
-- 11. 备份信息（最近备份情况）
SELECT
database_name,
type,
backup_start_date,
backup_finish_date,
backup_size / 1024 / 1024 AS [BackupSizeMB]
FROM msdb.dbo.backupset
WHERE backup_start_date > DATEADD(day, -7, GETDATE()) -- 最近一周
ORDER BY backup_start_date DESC
-- 12. 安全审计（检查登录和用户）
-- 12.1 检查登录名
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') AND name NOT LIKE '##%'
-- 12.2 检查数据库用户
USE [master] -- 切换到具体数据库则需修改
GO
EXEC sp_MSforeachdb '
USE [?]
SELECT
DB_NAME() AS [Database],
name AS [UserName],
type_desc,
authentication_type_desc
FROM sys.database_principals
WHERE type IN (''S'', ''U'', ''G'') AND name NOT IN (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'')
'
给我这个脚本字段加上中文别名
最新发布
本文通过具体的SQL语句示例，探讨了在SQL事务中使用GETDATE()函数作为默认值时的时间戳行为。实验证明，记录到数据库中的时间戳取的是执行INSERT语句时的时间，而非提交事务时的时间。
扫一扫
825
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
