-- Stored Procedure: dbo.SPTMonitorHistoryReport
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPTMonitorHistoryReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SPTMonitorHistoryReport]
GO




create procedure dbo.SPTMonitorHistoryReport
  @StartDate datetime = '1970-01-01',
  @EndDate datetime = null
as
/*******************************************************************************************************
* admin.dbo.SPTMonitorHistoryReport
* Creator: Bill Wunder
*
* Outline: Query the stored samples from the System Statistical Functions
*          to display the a performance behavior trend report
*   
* usage:
    EXECUTE admin.dbo.SPTMonitorHistoryReport 
* Notes:
*   
* Modifications   
* name     date        brief description
* -------  ----------  ------------------------------------------------------------
* 
********************************************************************************************************/
-- declare variables
declare @msPerTick bigint

-- set session
set nocount on


-- create temp tables

-- body of stored procedure
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(bigint, @@timeticks / 1000.0)

select
 [Date] = convert(varchar(20),s1.lastrun),
 [Seconds] = datediff(ss, s2.lastrun, s1.lastrun),
 [CPU Busy] = substring(convert(varchar(18),
  convert(bigint, ((s1.cpu_busy * @mspertick) / 1000)))
  + '('
  + convert(varchar(20), convert(int, (((s1.cpu_busy - s2.cpu_busy)
  * @mspertick) / 1000)))
  + ')'
  + '-'
  + convert(varchar(20), 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(20),
  convert(bigint, ((s1.io_busy * @mspertick) / 1000)))
  + '('
  + convert(varchar(20), convert(int, (((s1.io_busy - s2.io_busy)
  * @mspertick) / 1000)))
  + ')'
  + '-'
  + convert(varchar(20), convert(int, ((((s1.io_busy - s2.io_busy)
  * @mspertick) / 1000) * 100) / datediff(ss, s2.lastrun, s1.lastrun)))
  + '%',
  1, 25),
 [Idle] = substring(convert(varchar(20),
        convert(int, ((convert(bigint,s1.idle) * @mspertick) / 1000)))
  + '('
  + convert(varchar(20), convert(int, (((convert(bigint,s1.idle) - convert(bigint,s2.idle))
  * @mspertick) / 1000)))
  + ')'
  + '-'
  + convert(varchar(20), 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(20), s1.pack_received) + '(' +
  convert(varchar(20), s1.pack_received - s2.pack_received) + ')', 1, 25),
 [Packets Sent] = substring(convert(varchar(20), s1.pack_sent) + '(' +
  convert(varchar(20), s1.pack_sent - s2.pack_sent) + ')', 1, 25),
 [Packet Errors] = substring(convert(varchar(20), s1.pack_errors) + '(' +
  convert(varchar(20), s1.pack_errors - s2.pack_errors) + ')', 1, 25),
 [Total Reads] = substring(convert(varchar(20), s1.total_read) + '(' +
  convert(varchar(20), s1.total_read - s2.total_read) + ')', 1, 19),
 [Total Writes] = substring(convert(varchar(20), s1.total_write) + '(' +
  convert(varchar(20), s1.total_write - s2.total_write) + ')', 1, 19),
 [Total Errors] = substring(convert(varchar(20), s1.total_errors) + '(' +
  convert(varchar(20), s1.total_errors - s2.total_errors) + ')', 1, 19),
 [Connections] = substring(convert(varchar(20), s1.connections) + '(' +
  convert(varchar(20), s1.connections - s2.connections) + ')', 1, 18)
from admin.dbo.SPTMonitorHistory s1
left join admin.dbo.SPTMonitorHistory 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
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO