大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说SQL Server – 监控[亲测有效],希望您对编程的造诣更进一步.
当数据库出现性能异常时，如何找出引起性能问题的SQL？
- SQL Server自带trace & event只能抓取已执行完成的SQL，且无法抓取SQL运行过程中的状态信息
- 通过SQL Server系统视图可抓取正在运行的SQL和丰富的相关信息，如执行计划，状态信息等。将抓取到的数据存放在本地数据库表中，方便故障分析。
执行相关系统视图：
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text
sys.dm_exec_query_plan
其他系统视图：
sys.sysprocesses
sys.dm_db_session_space_usage
系统视图中信息非常丰富，多抓取一些有用的字段便于后续的分析工作
各字段含义详见官方文档
具体实现方法：
一、 创建一张表用于存放抓取到的Running SQL及其相关信息
USE [dba_monitor] GO CREATE TABLE [running_sql_monitor]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Insert_Time] [datetime] NOT NULL DEFAULT (getdate()), [Start_Time] [datetime] NOT NULL, [R_S] [int] NULL, [session_id] [smallint] NOT NULL, [status] [nvarchar](30) NOT NULL, [wait_type] [nvarchar](60) NULL, [wait_resource] [nvarchar](256) NOT NULL, [wait_time] [int] NOT NULL, [cpu_cnt] [int] NULL, [b_spid] [smallint] NULL, [dbname] [nvarchar](128) NULL, [t_level] [smallint] NOT NULL, [o_t_c] [int] NOT NULL, [row_count] [bigint] NOT NULL, [parent_query] [nvarchar](max) NULL, [individual_query] [nvarchar](max) NULL, [QueryPlan_XML] [xml] NULL, [login_name] [nvarchar](128) NOT NULL, [host_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [client_interface_name] [nvarchar](32) NULL, [cpu_time] [int] NOT NULL, [logical_reads] [bigint] NOT NULL, [reads] [bigint] NOT NULL, [writes] [bigint] NOT NULL, [memory_usage] [int] NULL, [tempdb_user_objects_mb] [int] NULL, [tempdb_internal_objects_mb] [int] NULL, [login_time] [datetime] NOT NULL, [percent_complete] [real] NOT NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"自增列" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"id" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"记录插入时间" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"Insert_Time" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"SQL执行开始时间" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"Start_Time" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"SQL运行总时间(单位秒)" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"R_S" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"SQL使用的CPU核数" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"cpu_cnt" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"被哪个session_id阻塞" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"b_spid" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"完整的SQL语句" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"parent_query" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"正在执行的SQL语句" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"individual_query" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"SQL语句的执行计划" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"QueryPlan_XML" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"SQL中的用户对象占用tempdb大小(单位MB)" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"tempdb_user_objects_mb" GO EXEC sys.sp_addextendedproperty @name=N"MS_Description", @value=N"SQL中的内部对象占用tempdb大小(单位MB)" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"running_sql_monitor", @level2type=N"COLUMN",@level2name=N"tempdb_internal_objects_mb" GO
代码100分
二、创建SQL Server JOB抓取Running SQL
job step1、 抓取Running SQL
代码100分INSERT INTO dba_monitor..running_sql_monitor( Start_Time, R_S, session_id, [status], wait_type, wait_resource, wait_time, cpu_cnt, b_spid, DBNAME, t_level, o_t_c, row_count, parent_query, individual_query, QueryPlan_XML, login_name, [host_name], [program_name], client_interface_name, cpu_time, logical_reads, reads, writes, memory_usage, tempdb_user_objects_mb, tempdb_internal_objects_mb, login_time, percent_complete ) SELECT r.start_time, DATEDIFF(s, r.start_time, GETDATE()) AS R_S, r.session_id, r.[status], r.wait_type, r.wait_resource,r.wait_time, x.counts AS cpu_cnt ,r.blocking_session_id AS b_spid, DB_NAME(r.database_id) AS dbname, es.transaction_isolation_level AS t_level,r.open_transaction_count AS o_t_c, es.row_count, parent_query = qt.[text], individual_query = SUBSTRING(qt.[text], (r.statement_start_offset / 2) + 1,((CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2 ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1), QueryPlan_XML = (SELECT query_plan FROM sys.dm_exec_query_plan(r.plan_handle)), es.login_name, es.host_name, es.program_name, es.client_interface_name, r.cpu_time, r.logical_reads, r.reads, r.writes, memory_usage, (su.user_objects_alloc_page_count * 8 /1024) AS tempdb_user_objects_mb, (su.internal_objects_alloc_page_count * 8 /1024) AS tempdb_internal_objects_mb, es.login_time, r.percent_complete FROM sys.dm_exec_requests AS r WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt INNER JOIN sys.dm_exec_sessions AS es WITH(NOLOCK) ON r.session_id = es.session_id LEFT JOIN (SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM sys.sysprocesses WITH(NOLOCK) GROUP BY spid) x ON x.spid=r.session_id LEFT JOIN sys.dm_db_session_space_usage su on es.session_id=su.session_id WHERE es.is_user_process = 1 AND es.session_Id <> @@SPID
job step2、为防止监控表过大，删除7天前抓取到的数据（请根据实际情况设置JOB运行间隔时间，以及监控数据需要保留的时间周期，避免监控文件过大导致磁盘空间耗尽！！！）
delete top(100) from dba_monitor..running_sql_monitor where Insert_Time < DATEADD(DAY, -7, CAST(GETDATE() as DATE))
分析在出现性能问题时抓取到的SQL，通过执行时长，SQL运行状态，等待信息来确认哪些SQL是罪魁祸首（部分被抓取到SQL可能是受害者，由于其他SQL占用了的大量系统资源 或 长时间占用锁资源）
希望能帮助到有需要的同学
本文为原创，转载请注明：https://www.cnblogs.com/Sylaro0/
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7279.html
