/* Benchmarking Techniques Using T-SQL - System Statistical Functions */ use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_sp_monitor_History]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Get_sp_monitor_History] GO create procedure dbo.Get_sp_monitor_History as /******************************************************************************************************* * dbo.Get_sp_monitor_History.PRC * Creator: Bill Wunder * * Description: execute system procedure sp_monitor and capture the result to a logging table * * Usage: EXECUTE admin.dbo.Get_sp_monitor_History * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ if object_id('admin.dbo.spt_monitor_log','U') is null create table admin.dbo.spt_monitor_log (id int identity(1,1), lastrun datetime not null, cpu_busy int not null, io_busy int not null, idle int not null, pack_received int not null, pack_sent int not null, connections int not null, pack_errors int not null, total_read int not null, total_write int not null, total_errors int not null constraint pkc_spt_monitor_log__id primary key (id)) -- if someone else ran sp_monitor since last time I did, add a row to the log for their data if not exists (select lastrun from admin.dbo.spt_monitor_log where id = (select max(id) from admin.dbo.spt_monitor_log) and lastrun = (select lastrun from master.dbo.spt_monitor)) insert admin.dbo.spt_monitor_log (lastrun, cpu_busy, io_busy, idle, pack_received, pack_sent, connections, pack_errors, total_read, total_write, total_errors) select lastrun, cpu_busy, io_busy, idle, pack_received, pack_sent, connections, pack_errors, total_read, total_write, total_errors from master.dbo.spt_monitor exec master.dbo.sp_monitor insert admin.dbo.spt_monitor_log (lastrun, cpu_busy, io_busy, idle, pack_received, pack_sent, connections, pack_errors, total_read, total_write, total_errors) select lastrun, cpu_busy, io_busy, idle, pack_received, pack_sent, connections, pack_errors, total_read, total_write, total_errors from master.dbo.spt_monitor GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MonitorLogTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[MonitorLogTrend] GO create procedure dbo.MonitorLogTrend @StartDate datetime = '1970-01-01', @EndDate datetime = null as /******************************************************************************************************* * dbo.MonitorLogTrend.PRC * Creator: Bill Wunder * * Description: produce a result set of daily sp_monitor results for a specified time period * * Usage: EXECUTE admin.dbo.MonitorLogTrend -- all EXECUTE admin.dbo.MonitorLogTrend '2003.07.06', '2003.07.13'-- one week * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ declare @msPerTick int set nocount on print 'Column name - Description Date - Time sp_monitor was executed. Seconds - Number of elapsed seconds since sp_monitor was run. CPU Busy - Number of seconds that the server computer''s CPU has been doing SQL Server work. I/O Busy - Number of seconds that SQL Server has spent doing input and output operations. Idle - Number of seconds that SQL Server has been idle. Packets Received - Number of input packets read by SQL Server. Packets Sent - Number of output packets written by SQL Server. Packet Errors - Number of errors encountered by SQL Server while reading and writing packets. Total Reads - Number of reads by SQL Server. Total Write - Number of writes by SQL Server. Total Errors - Number of errors encountered by SQL Server while reading and writing. Connections - Number of logins or attempted logins to SQL Server.' /* ** Set @mspertick. This is just used to make the numbers easier to handle ** and avoid overflow. */ select @mspertick = convert(int, @@timeticks / 1000.0) select [Date] = convert(varchar(11),s1.lastrun), [Seconds] = datediff(ss, s2.lastrun, s1.lastrun), [CPU Busy] = substring(convert(varchar(11), convert(bigint, ((s1.cpu_busy * @mspertick) / 1000))) + '(' + convert(varchar(11), convert(int, (((s1.cpu_busy - s2.cpu_busy) * @mspertick) / 1000))) + ')' + '-' + convert(varchar(11), convert(int, ((((s1.cpu_busy - coalesce(s2.cpu_busy,0)) * @mspertick) / 1000) * 100) / datediff(ss, s2.lastrun, s1.lastrun))) + '%', 1, 25), [I/O Busy] = substring(convert(varchar(11), convert(bigint, ((s1.io_busy * @mspertick) / 1000))) + '(' + convert(varchar(11), convert(int, (((s1.io_busy - s2.io_busy) * @mspertick) / 1000))) + ')' + '-' + convert(varchar(11), convert(int, ((((s1.io_busy - s2.io_busy) * @mspertick) / 1000) * 100) / datediff(ss, s2.lastrun, s1.lastrun))) + '%', 1, 25), [Idle] = substring(convert(varchar(11), convert(int, ((convert(bigint,s1.idle) * @mspertick) / 1000))) + '(' + convert(varchar(11), convert(int, (((convert(bigint,s1.idle) - convert(bigint,s2.idle)) * @mspertick) / 1000)))+ ')' + '-' + convert(varchar(11), convert(int, ((((convert(bigint,s1.idle) - convert(bigint,s2.idle)) * @mspertick) / 1000) * 100) / datediff(ss, s2.lastrun, s1.lastrun))) + '%', 1, 25), [Packets Received] = substring(convert(varchar(11), s1.pack_received) + '(' + convert(varchar(11), s1.pack_received - s2.pack_received) + ')', 1, 25), [Packets Sent] = substring(convert(varchar(11), s1.pack_sent) + '(' + convert(varchar(11), s1.pack_sent - s2.pack_sent) + ')', 1, 25), [Packet Errors] = substring(convert(varchar(11), s1.pack_errors) + '(' + convert(varchar(11), s1.pack_errors - s2.pack_errors) + ')', 1, 25), [Total Reads] = substring(convert(varchar(11), s1.total_read) + '(' + convert(varchar(11), s1.total_read - s2.total_read) + ')', 1, 19), [Total Writes] = substring(convert(varchar(11), s1.total_write) + '(' + convert(varchar(11), s1.total_write - s2.total_write) + ')', 1, 19), [Total Errors] = substring(convert(varchar(11), s1.total_errors) + '(' + convert(varchar(11), s1.total_errors - s2.total_errors) + ')', 1, 19), [Connections] = substring(convert(varchar(11), s1.connections) + '(' + convert(varchar(11), s1.connections - s2.connections) + ')', 1, 18) from admin.dbo.spt_monitor_log s1 left join admin.dbo.spt_monitor_log s2 on s1.Id = s2.Id + 1 where s1.lastrun between @StartDate and coalesce(@EndDate,getdate()) and s1.cpu_busy > s2.cpu_busy and s1.io_busy > s2.io_busy and s1.idle > s2.idle order by s1.lastrun desc GO -- job to get so_monitor history daily and at each SQL Server startup SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO begin transaction DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 if (select count(*) from msdb.dbo.syscategories where name = N'Monitoring') < 1 execute msdb.dbo.sp_add_category @name = N'Monitoring' select @JobID = job_id from msdb.dbo.sysjobs where (name = N'get sp_monitor history') if (@JobID is not NULL) begin if (exists (select * from msdb.dbo.sysjobservers where (job_id = @JobID) AND (server_id <> 0))) begin RAISERROR (N'Unable to import job since there is already a multi-server job with this name.', 16, 1) goto QuitWithRollback end else execute msdb.dbo.sp_delete_job @job_name = N'get sp_monitor history' select @JobID = NULL end select @JobID = job_id from msdb.dbo.sysjobs where (name = N'get sp_monitor history') if (@JobID is NULL) BEGIN execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'get sp_monitor history', @enabled = 1, @start_step_id = 1, @notify_level_eventlog = 2, @notify_level_email = 0, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = N'No description available.', @category_name = N'Monitoring', @owner_login_name = N'sa' if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 4, @on_fail_step_id = 2, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 0, @step_name = N'step 1', @subsystem = N'TSQL', @command = N'exec admin.dbo.Get_sp_monitor_History ', @database_name = N'master' if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 2, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 1, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 0, @step_name = N'failure notification', @subsystem = N'TSQL', @command = N'exec admin.dbo.job_log_error', @database_name = N'master' if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'at startup', @enabled = 1, @freq_type = 64, @freq_interval = 0, @freq_subday_type = 0, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20030318, @active_end_date = 99991231, @active_start_time = 0, @active_end_time = 235959 if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'daily', @enabled = 1, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20020911, @active_end_date = 99991231, @active_start_time = 220000, @active_end_time = 235959 if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback execute @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback END commit transaction goto EndSave QuitWithRollback: if (@@TRANCOUNT > 0) rollback transaction EndSave: GO /* sample output from MonitoLogTrend Date Seconds CPU Busy I/O Busy Idle Packets Received Packets Sent Packet Errors Total Reads Total Writes Total Errors Connections ----------- ----------- ------------------------- ------------------------- ------------------------- ------------------------ ------------------------ ------------------------ ------------------- ------------------- ------------------- ------------------ Feb 28 2004 84457 1172164(60194)-71% 35219(2122)-2% 2733938(264947)-313% 147169220(9126873) 185263066(13039807) 1280(22) 25205322(1740107) 111320245(7478212) 0(0) 1063851(73183) Feb 27 2004 86400 1111970(92373)-106% 33096(2853)-3% 2468990(239562)-277% 138042347(13209764) 172223259(16381685) 1258(83) 23465215(2221812) 103842033(10042220) 0(0) 990668(80531) Feb 26 2004 86400 1019596(94192)-109% 30243(2870)-3% 2229428(237728)-275% 124832583(13414618) 155841574(16623477) 1175(105) 21243403(2326204) 93799813(9854245) 0(0) 910137(80992) Feb 25 2004 86400 925404(99199)-114% 27372(3108)-3% 1991699(232484)-269% 111417965(13813968) 139218097(17454242) 1070(139) 18917199(2637439) 83945568(10738827) 0(0) 829145(92372) Feb 24 2004 86401 826204(92383)-106% 24263(2969)-3% 1759214(239439)-277% 97603997(12857582) 121763855(16607319) 931(120) 16279760(2134725) 73206741(11138844) 0(0) 736773(95345) Feb 23 2004 86398 733820(85225)-98% 21294(2521)-2% 1519775(247035)-285% 84746415(11941917) 105156536(16198591) 811(87) 14145035(1822173) 62067897(7210392) 0(0) 641428(95224) Feb 22 2004 86401 648595(48301)-55% 18773(1694)-1% 1272740(284799)-329% 72804498(6622939) 88957945(9438719) 724(22) 12322862(795107) 54857505(7595942) 0(0) 546204(89108) Feb 21 2004 86401 600293(60808)-70% 17078(2243)-2% 987941(271742)-314% 66181559(10701505) 79519226(13074105) 702(32) 11527755(1673810) 47261563(8136268) 0(0) 457096(89143) Feb 20 2004 86400 539485(84550)-97% 14834(2836)-3% 716198(247405)-286% 55480054(13003304) 66445121(16146137) 670(90) 9853945(2243552) 39125295(9802351) 0(0) 367953(94545) Feb 19 2004 86400 454934(181136)-209% 11998(4850)-5% 468793(148804)-172% 42476750(14063008) 50298984(17291557) 580(152) 7610393(3304533) 29322944(10104186) 0(0) 273408(95868) spt_monitor_log data used to produce this output id lastrun cpu_busy io_busy idle pack_received pack_sent connections pack_errors total_read total_write total_errors ----------- ------------------------------------------------------ ----------- ----------- ----------- ------------- ----------- ----------- ----------- ----------- ----------- ------------ 570 2004-02-28 21:27:58.107 37811752 1136106 88191562 147169220 185263066 1063851 1280 25205322 111320245 0 569 2004-02-27 22:00:21.417 35870007 1067644 79644865 138042347 172223259 990668 1258 23465215 103842033 0 568 2004-02-26 22:00:21.853 32890213 975584 71917047 124832583 155841574 910137 1175 21243403 93799813 0 567 2004-02-25 22:00:21.453 29851742 882977 64248377 111417965 139218097 829145 1070 18917199 83945568 0 566 2004-02-24 22:00:21.003 26651759 782701 56748868 97603997 121763855 736773 931 16279760 73206741 0 565 2004-02-23 22:00:20.617 23671638 686924 49025014 84746415 105156536 641428 811 14145035 62067897 0 564 2004-02-22 22:00:22.973 20922426 605588 41056138 72804498 88957945 546204 724 12322862 54857505 0 563 2004-02-21 22:00:21.803 19364309 550922 31869069 66181559 79519226 457096 702 11527755 47261563 0 562 2004-02-20 22:00:20.687 17402744 478544 23103172 55480054 66445121 367953 670 9853945 39125295 0 561 2004-02-19 22:00:20.443 14675318 387046 15122360 42476750 50298984 273408 580 7610393 29322944 0 */