SQLServer监控

  • Z时代
  • 2024-01-10
  • 分类：综合

database

 

 当数据库出现性能异常时，如何找出引起性能问题的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

 　　

　　系统视图中信息非常丰富，多抓取一些有用的字段便于后续的分析工作

各字段含义详见官方文档

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15

 

 

 具体实现方法：

一、 创建一张表用于存放抓取到的Running SQL及其相关信息

USE[dba_monitor]
GO
CREATETABLE[running_sql_monitor](
[id][int]IDENTITY(1,1) NOTNULLPRIMARYKEY,
[Insert_Time][datetime]NOTNULLDEFAULT (getdate()),
[Start_Time][datetime]NOTNULL,
[R_S][int]NULL,
[session_id][smallint]NOTNULL,
[status][nvarchar](30) NOTNULL,
[wait_type][nvarchar](60) NULL,
[wait_resource][nvarchar](256) NOTNULL,
[wait_time][int]NOTNULL,
[cpu_cnt][int]NULL,
[b_spid][smallint]NULL,
[dbname][nvarchar](128) NULL,
[t_level][smallint]NOTNULL,
[o_t_c][int]NOTNULL,
[row_count][bigint]NOTNULL,
[parent_query][nvarchar](max) NULL,
[individual_query][nvarchar](max) NULL,
[QueryPlan_XML][xml]NULL,
[login_name][nvarchar](128) NOTNULL,
[host_name][nvarchar](128) NULL,
[program_name][nvarchar](128) NULL,
[client_interface_name][nvarchar](32) NULL,
[cpu_time][int]NOTNULL,
[logical_reads][bigint]NOTNULL,
[reads][bigint]NOTNULL,
[writes][bigint]NOTNULL,
[memory_usage][int]NULL,
[tempdb_user_objects_mb][int]NULL,
[tempdb_internal_objects_mb][int]NULL,
[login_time][datetime]NOTNULL,
[percent_complete][real]NOTNULL
) 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 step1、 抓取Running SQL

INSERTINTO 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,((CASEWHEN r.statement_end_offset =-1THENLEN(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
INNERJOIN sys.dm_exec_sessions AS es WITH(NOLOCK) ON r.session_id = es.session_id
LEFTJOIN (SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM sys.sysprocesses WITH(NOLOCK) GROUPBY spid)  x ON x.spid=r.session_id
LEFTJOIN 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运行间隔时间，以及监控数据需要保留的时间周期，避免监控文件过大导致磁盘空间耗尽！！！）

deletetop(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/

 

以上是 SQLServer监控 的全部内容， 来源链接： utcz.com/z/534687.html

回到顶部