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