监控游标和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

方法/步骤

1.  

   以下面这个年份和销售量的数据为例操作

   
2.  

   首先选中销售量一列的数据，选择插入，点击折线图。

   
3.  

   主图做好了，我们要修改的就是下面的横坐标

   
4.  

   在图上右键单击，在菜单中点击选择数据

   
5.  

   出现选择数据源以后，点击图中所示位置的编辑。

   
6.  

   跳出轴标签界面，此时选中年份列中的年份数据，点击确定

   
7.  

   接着在如图所示的位置点击确定。

   
8.  

   大功告成，折线图的横坐标就设置完成了。

   

9.  

 折线图的示例