监控游标和CPU使用率的自动job并用excel建立折线图来分析
监控游标和CPU使用率的自动job并用excel建立折线图来分析
30秒监控一次
SELECT * FROM sys.[dm_os_performance_counters] WHERE [counter_name]='CPU usage %' AND [object_name]='SQLServer:Resource Pool Stats' AND [instance_name]='default' SELECT * FROM sys.[dm_os_performance_counters] WHERE [counter_name]='Active cursors' AND [object_name]='SQLServer:Cursor Manager by Type' AND [instance_name]='_Total' --建表 USE [msdb] GO CREATE TABLE ActiveCursors (cntr_value BIGINT,cntr_time DATETIME PRIMARY KEY) GO CREATE TABLE CPUUsage (cntr_value BIGINT,cntr_time DATETIME PRIMARY KEY) GO --建作业 DECLARE @DBName NVARCHAR(MAX) DECLARE @job_name sysname SET @DBName='Barefoot.Opinion.9095' --★Do SET @job_name='Monitor_CPUUsage_' + @DBName EXEC msdb.dbo.sp_add_job @job_name=@job_name, @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'监控CPU使用率', @category_name=N'Database Maintenance', @owner_login_name=N'sa' --添加监控步骤 DECLARE @job_name SYSNAME DECLARE @SQL NVARCHAR(MAX) DECLARE @DBName NVARCHAR(MAX) SET @DBName='Barefoot.Opinion.9095' --★Do SET @job_name='Monitor_CPUUsage_' + @DBName --★Do BEGIN SET @SQL = N' USE [msdb] GO INSERT INTO CPUUsage(cntr_value,cntr_time) SELECT cntr_value,GETDATE() FROM sys.[dm_os_performance_counters] WHERE [counter_name]=''CPU usage %'' AND [object_name]=''SQLServer:Resource Pool Stats'' AND [instance_name]=''default'' ' EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name, @step_name = N'Monitor', @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL', @command = @SQL, @database_name = @DBNAME, @flags = 0 END --创建Monitor作业的调度计划 DECLARE @job_name SYSNAME DECLARE @SQL NVARCHAR(MAX) DECLARE @DBName NVARCHAR(MAX) SET @DBName='Barefoot.Opinion.9095' --★Do SET @job_name='Monitor_CPUUsage_' + @DBName --★Do --修改作业的执行时间 EXEC msdb.dbo.sp_add_jobschedule @job_name = @job_name, @name=N'Plan', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=30, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20140105, @active_end_date=99991231, @active_start_time=2000, @active_end_time=235959 EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = N'(local)' ------------------------------------------------------------------------------ --建作业 DECLARE @DBName NVARCHAR(MAX) DECLARE @job_name sysname SET @DBName='Barefoot.Opinion.9095' --★Do SET @job_name='Monitor_ActiveCursors_' + @DBName EXEC msdb.dbo.sp_add_job @job_name=@job_name, @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'监控游标使用', @category_name=N'Database Maintenance', @owner_login_name=N'sa' --添加监控步骤 DECLARE @job_name SYSNAME DECLARE @SQL NVARCHAR(MAX) DECLARE @DBName NVARCHAR(MAX) SET @DBName='Barefoot.Opinion.9095' --★Do SET @job_name='Monitor_ActiveCursors_' + @DBName --★Do BEGIN SET @SQL = N' USE [msdb] GO INSERT INTO ActiveCursors(cntr_value,cntr_time) SELECT cntr_value,GETDATE() FROM sys.[dm_os_performance_counters] WHERE [counter_name]=''Active cursors'' AND [object_name]=''SQLServer:Cursor Manager by Type'' AND [instance_name]=''_Total'' ' EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name, @step_name = N'Monitor', @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL', @command = @SQL, @database_name = @DBNAME, @flags = 0 END --创建Monitor作业的调度计划 DECLARE @job_name SYSNAME DECLARE @SQL NVARCHAR(MAX) DECLARE @DBName NVARCHAR(MAX) SET @DBName='Barefoot.Opinion.9095' --★Do SET @job_name='Monitor_ActiveCursors_' + @DBName --★Do --修改作业的执行时间 EXEC msdb.dbo.sp_add_jobschedule @job_name = @job_name, @name=N'Plan', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=30, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20140105, @active_end_date=99991231, @active_start_time=2000, @active_end_time=235959 EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = N'(local)'
USE [msdb] GO SELECT * FROM [dbo].[ActiveCursors] SELECT * FROM [dbo].[CPUUsage]
折线图下载地址:
https://files.cnblogs.com/lyhabc/%E6%8A%98%E7%BA%BF%E5%9B%BE.rar
http://jingyan.baidu.com/article/37bce2be2f47c31003f3a251.html
工具/原料
-
office excel
折线图的示例
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战