[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
二、创建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 抓取
标签：handle cti not cpu 单位 使用 monitor ace where
