SQL性能优化：常用监控与诊断命令集锦

本文档提供了一系列SQL命令语句，涵盖了监控和分析SQL Server性能的关键方面，包括查询数据库连接数、检查等待类型、监控CPU压力、识别执行效率低下的查询以及跟踪进程和连接。 1. 查询数据库连接数：通过执行`select count(*) from Master.dbo.SysProcesses where dbid=db_id()`，可以获取当前数据库的连接数量。 2. 查看最常见的其他等待类型：`SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC`列出等待时间最长的前10种等待类型。`SELECT * FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%' OR wait_type like 'LAZYWRITER_SLEEP%'`则重点关注与页面锁和懒惰写等待相关的等待事件。 3. 监控CPU压力：`SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255`帮助分析CPU调度器的当前任务和可运行任务，以了解CPU负载情况。 4. 找出执行效率最差的查询：`SELECT ... FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t ORDER BY s.max_physical_reads DESC`展示执行次数最多、物理读取最多的前10个查询，包括其执行统计信息，如执行计数、平均执行时间和资源消耗。 5. 分析等待时间：`SELECT ... FROM sys.dm_os_wait_stats`计算总信号等待时间和资源等待时间的百分比，以确定系统中是否存在CPU或资源瓶颈。 6. 查看执行的SQL语句：通过`SELECT text, ... FROM master.sys.sysprocesses a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)`，可以跟踪正在执行的SQL语句，以及特定SPID（进程ID）对应的SQL语句。 7. 检测阻塞和会话信息：`WITH ... SELECT ... FROM master.sys.dm_exec_requests a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)`结合`dbcc inputbuffer(53)`，用于检查阻塞会话、登录信息、资源使用情况及客户端连接信息。 8. 当前进程数 和 活动进程数：`select * from master.dbo.sysprocesses` 及 `sp_who 'active'` 分别显示所有进程和当前活动的进程状态。 9. 查找未释放的连接：通过`select * from master.dbo.sysprocesses WHERE spid > 50 AND waittype = 0x0000 AND waittime = 0 AND status = 'sleeping' AND last_batch < dateadd(minute, -10, getdate()) AND login_time < dateadd(minute, -10, getdate())`查找可能引起CPU过高的长时间未释放的连接。 10. 强制结束空连接：`select 'kill ' + rtrim(spid) from master.dbo.sysprocesses WHERE spid > 50 AND waittype = 0x0000 AND waittime = 0 AND status = 'sleeping' AND last_batch < dateadd(minute, -60, getdate()) AND login_time < dateadd(minute, -60, getdate())`可终止那些长时间未活动的空闲连接。 11. 高CPU使用率的会话和SQL语句：`select spid, cmd, cpu, physical_io, memusage, ...` 显示当前CPU资源消耗最大的会话及其执行的SQL语句。 12. 缓存中的低使用率、高内存占用查询：`SELECT ... FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql` 找出缓存中使用次数少但占用内存大的查询。 13. 重复计划查询分析：`SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt WHERE plan_generation_num >1 ORDER BY qs.plan_generation_num` 查找生成多次但执行效率低的查询计划。 14. CPU时间与执行次数的分析：`SELECT ... FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt` 用于统计SQL语句的总CPU时间、执行次数、平均CPU时间和查询数量，以识别潜在问题。 这些SQL语句对于SQL Server的性能监控和优化至关重要，可以帮助数据库管理员识别和解决性能瓶颈，提高系统效率。