当前位置:Gxlcms > 数据库问题 > SQL Server - 监控 - Running SQL 抓取

SQL Server - 监控 - Running SQL 抓取

时间:2021-07-01 10:21:17 帮助过:5人阅读

[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=NMS_Description, @value=N自增列 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Nid GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N记录插入时间 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=NInsert_Time GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NSQL执行开始时间 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=NStart_Time GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NSQL运行总时间(单位秒) , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=NR_S GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NSQL使用的CPU核数 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Ncpu_cnt GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N被哪个session_id阻塞 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Nb_spid GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N完整的SQL语句 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Nparent_query GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N正在执行的SQL语句 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Nindividual_query GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NSQL语句的执行计划 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=NQueryPlan_XML GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NSQL中的用户对象占用tempdb大小(单位MB) , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Ntempdb_user_objects_mb GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NSQL中的内部对象占用tempdb大小(单位MB) , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nrunning_sql_monitor, @level2type=NCOLUMN,@level2name=Ntempdb_internal_objects_mb GO

 

二、创建SQL Server JOB抓取Running SQL

JOB 每分钟运行一次

 

job step1、 抓取Running SQL

  1. <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> dba_monitor..running_sql_monitor(
  2. Start_Time, R_S, session_id, </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">status</span><span style="color: #ff0000;">]</span><span style="color: #000000;">, wait_type, wait_resource, wait_time, cpu_cnt, b_spid, DBNAME, t_level, o_t_c, row_count,
  3. parent_query, individual_query, QueryPlan_XML, login_name, </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">]</span>, <span style="color: #ff0000;">[</span><span style="color: #ff0000;">program_name</span><span style="color: #ff0000;">]</span><span style="color: #000000;">, client_interface_name, cpu_time, logical_reads, reads, writes,
  4. memory_usage, tempdb_user_objects_mb, tempdb_internal_objects_mb, login_time, percent_complete
  5. )
  6. </span><span style="color: #0000ff;">SELECT</span> r.start_time, r.total_elapsed_time<span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">1000</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> R_S, r.session_id,
  7. r.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">status</span><span style="color: #ff0000;">]</span><span style="color: #000000;">, r.wait_type, r.wait_resource,r.wait_time,
  8. x.counts </span><span style="color: #0000ff;">AS</span> cpu_cnt ,r.blocking_session_id <span style="color: #0000ff;">AS</span><span style="color: #000000;"> b_spid,
  9. </span><span style="color: #ff00ff;">DB_NAME</span>(r.database_id) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> dbname,
  10. es.transaction_isolation_level </span><span style="color: #0000ff;">AS</span> t_level,r.open_transaction_count <span style="color: #0000ff;">AS</span><span style="color: #000000;"> o_t_c, es.row_count,
  11. parent_query </span><span style="color: #808080;">=</span> qt.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">text</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  12. individual_query </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">SUBSTRING</span>(qt.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">text</span><span style="color: #ff0000;">]</span>, (r.statement_start_offset <span style="color: #808080;">/</span> <span style="color: #800000; font-weight: bold;">2</span>) <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span>,((<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> r.statement_end_offset <span style="color: #808080;">=</span> <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #ff00ff;">MAX</span>), qt.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">text</span><span style="color: #ff0000;">]</span>)) <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">2</span>
  13. <span style="color: #0000ff;">ELSE</span> r.statement_end_offset <span style="color: #0000ff;">END</span> <span style="color: #808080;">-</span> r.statement_start_offset) <span style="color: #808080;">/</span> <span style="color: #800000; font-weight: bold;">2</span>) <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">),
  14. QueryPlan_XML </span><span style="color: #808080;">=</span> (<span style="color: #0000ff;">SELECT</span> query_plan <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> sys.dm_exec_query_plan(r.plan_handle)),
  15. es.login_name, es.</span><span style="color: #ff00ff;">host_name</span><span style="color: #000000;">, es.program_name, es.client_interface_name,
  16. r.cpu_time, r.logical_reads, r.reads, r.writes, memory_usage,
  17. (su.user_objects_alloc_page_count </span><span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">8</span> <span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">1024</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> tempdb_user_objects_mb,
  18. (su.internal_objects_alloc_page_count </span><span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">8</span> <span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">1024</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> tempdb_internal_objects_mb,
  19. es.login_time, r.percent_complete
  20. </span><span style="color: #0000ff;">FROM</span> sys.dm_exec_requests <span style="color: #0000ff;">AS</span> r <span style="color: #0000ff;">WITH</span><span style="color: #000000;">(NOLOCK)
  21. </span><span style="color: #0000ff;">CROSS</span> APPLY sys.dm_exec_sql_text(sql_handle) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> qt
  22. </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sys.dm_exec_sessions <span style="color: #0000ff;">AS</span> es <span style="color: #0000ff;">WITH</span>(NOLOCK) <span style="color: #0000ff;">ON</span> r.session_id <span style="color: #808080;">=</span><span style="color: #000000;"> es.session_id
  23. </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> (<span style="color: #0000ff;">SELECT</span> spid,<span style="color: #ff00ff;">MAX</span>(loginame)<span style="color: #0000ff;">AS</span> loginame,<span style="color: #ff00ff;">COUNT</span>(<span style="color: #800000; font-weight: bold;">0</span>)<span style="color: #0000ff;">AS</span> counts <span style="color: #0000ff;">FROM</span> sys.sysprocesses <span style="color: #0000ff;">WITH</span>(NOLOCK) <span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> spid) x <span style="color: #0000ff;">ON</span> x.spid<span style="color: #808080;">=</span><span style="color: #000000;">r.session_id
  24. </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> sys.dm_db_session_space_usage su <span style="color: #0000ff;">on</span> es.session_id<span style="color: #808080;">=</span><span style="color: #000000;">su.session_id
  25. </span><span style="color: #0000ff;">WHERE</span> es.is_user_process <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>
  26. <span style="color: #808080;">AND</span> es.session_Id <span style="color: #808080;"><></span> <span style="color: #008000; font-weight: bold;">@@SPID</span>

 

job step2、为防止监控表过大,删除7天前抓取到的数据

  1. <span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">top</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #0000ff;">from</span> dba_monitor..running_sql_monitor <span style="color: #0000ff;">where</span> InsertTime <span style="color: #808080;"><</span> <span style="color: #ff00ff;">DATEADD</span>(<span style="color: #ff00ff;">DAY</span>, <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">7</span>, <span style="color: #ff00ff;">CAST</span>(<span style="color: #ff00ff;">GETDATE</span>() <span style="color: #0000ff;">as</span> DATE))

 

SQL Server - 监控 - Running SQL 抓取

标签:handle   cti   not   cpu   单位   使用   monitor   ace   where   

人气教程排行