广告
返回顶部
首页 > 资讯 > 数据库 >SQL Server - 监控 - Running SQL 抓取
  • 553
分享到

SQL Server - 监控 - Running SQL 抓取

摘要

    一、 创建一张表用于存放抓取到的Running sql USE [dba_monitor] Go CREATE TABLE [running_sql_monitor]( [id] [int] IDENTITY(1,1) N


	SQL Server - 监控 - Running SQL 抓取
[数据库教程]

 

 

一、 创建一张表用于存放抓取到的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=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

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, r.total_elapsed_time/1000 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天前抓取到的数据

delete top(100) from  dba_monitor..running_sql_monitor where InsertTime < DATEADD(DAY, -7, CAST(GETDATE() as DATE))

 

SQL Server - 监控 - Running SQL 抓取

原文地址:https://www.cnblogs.com/Sylaro0/p/13331742.html

您可能感兴趣的文档:

--结束END--

本文标题: SQL Server - 监控 - Running SQL 抓取

本文链接: https://www.lsjlt.com/news/7243.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑,方便收藏和打印~

下载Word文档
猜你喜欢
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作