|
CREATE Procedure dbo.GenerateStatistics_Hourly
-- =============================================================
-- Procedure: GenerateStatistics_Hourly
-- Written by: (c) Colin Leversuch-Roberts
-- www.kelemconsulting.co.uk
--
-- Purpose: Part of set of procs and tables to monitor trends in sql server usage
--
-- System: DBA Monitoring - ServerAdmin
--
-- Input Paramters: none
--
-- Returns : none
--
-- Usage: Called by scheduled task
--
-- Notes: This proc is part of the set:- InitialiseMonitorStats
-- InitialiseStatistics
-- GenerateStatistics_Hourly
-- RollupDailyData
-- RollupDailyData8to8
--
-- This procedure generates the hourly data in ServerAdmin..Daily_Statistics from the ServerAdmin..TranTable
-- and ServerAdmin..PerfData_Wide tables
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 9 March 2004 Initial Release
-- =============================================================
as
set nocount on
--
-- this will run at every hour
--
declare @cpu_busy_init bigint,@io_busy_init bigint,@cpu_busy_upd bigint,@io_busy_upd bigint
declare @packet_received_init bigint,@packet_sent_init bigint,@packet_received_upd bigint,@packet_sent_upd bigint
declare @packet_error_init bigint, @connections_init bigint,@packet_error_upd bigint, @connections_upd bigint
declare @total_reads_init bigint,@total_writes_init bigint,@total_reads_upd bigint,@total_writes_upd bigint
declare @rw_errors_init bigint,@rw_errors_upd bigint
declare @cachehit real,@chachebase real,@userconnections bigint
declare @count tinyint,@counter varchar(100),@value bigint,@cmd nvarchar(250)
--
-- user database stats
--
declare @UserDB1Trans bigint
declare @UserDB2Trans bigint
declare @UserDB3Trans bigint
declare @UserDB4Trans bigint
--
declare @TempdbTrans bigint
declare @TotalTrans bigint
--
declare @LockRequests bigint,@LockTimeouts bigint,@Deadlocks bigint,@LockWaits bigint,@FullScans bigint,@Workfiles bigint
declare @Worktables bigint,@IndexSearches bigint,@PageSplits bigint,@Compilations bigint,@LatchWaits bigint
--
-- user connections - point in time value
select @userconnections=cntr_value from master.dbo.sysperfinfo with (nolock) where counter_name='user connections'
-- cache hit ratio - point in time value
select @cachehit=cntr_value from master.dbo.sysperfinfo with (nolock) where counter_name='cache hit ratio' and instance_name='_Total'
select @chachebase=cntr_value from master.dbo.sysperfinfo with (nolock) where counter_name='cache hit ratio base' and instance_name='_Total'
--
-- read the spt_monitor table in master then re-seed it
--
select
@cpu_busy_init = cpu_busy
,@io_busy_init = io_busy
,@packet_received_init = pack_received
,@packet_sent_init = pack_sent
,@connections_init = connections
,@packet_error_init = pack_errors
,@total_reads_init = total_read
,@total_writes_init = total_write
,@rw_errors_init = total_errors
from master.dbo.spt_monitor
--
exec master.dbo.sp_monitor
--
select
@cpu_busy_upd = (cpu_busy - @cpu_busy_init)
,@io_busy_upd = (io_busy - @io_busy_init)
,@packet_received_upd = (pack_received - @packet_received_init)/3600
,@packet_sent_upd = (pack_sent - @packet_sent_init)/3600
,@connections_upd = (connections - @connections_init)/3600
,@packet_error_upd = (pack_errors - @packet_error_init) -- this is a count not an average
,@total_reads_upd = (total_read - @total_reads_init)/3600
,@total_writes_upd = (total_write - @total_writes_init)/3600
,@rw_errors_upd = (total_errors - @rw_errors_init) -- this is a count not an average
from master.dbo.spt_monitor
--
-- clear the temporary storage table
--
truncate table ServerAdmin.dbo.TranTable
--
-- now populate the temporary storage table
--
-- NOTE
-- We have to store the values at hour intervals and then do the maths at each successive interval
-- this means the following are an average for the hour period
--
-- transactions per second for user databases, tempdb and total for server
--
insert into ServerAdmin.dbo.TranTable
select instance_name,cntr_value from master.dbo.sysperfinfo where counter_name ='transactions/sec'
and instance_name in('_Total',
'UserDB1',
‘UserDB2’,
‘UserDB3’,
‘UserDB4’,
'tempdb')
--
-- locks -- requests timeouts deadlocks lock waits
-- here we will store the number of requests, waits and timeouts for the period then average per/sec for the period
-- deadlocks we will store the actual number within the period
--
insert into ServerAdmin.dbo.TranTable
select counter_name,cntr_value from master.dbo.sysperfinfo where counter_name in('Lock Requests/sec','Lock Timeouts/sec','Number of Deadlocks/sec','Lock Waits/sec')
and instance_name='_Total'
--
-- scans -- tables scans index scans page splits workfiles and tables compilations
-- averaged for the period per sec
--
insert into ServerAdmin.dbo.TranTable
select counter_name,cntr_value from master.dbo.sysperfinfo where counter_name in ('Full Scans/sec','Index Searches/sec','Page Splits/sec','Workfiles Created/sec',
'Worktables Created/sec','SQL Compilations/sec','latch waits/sec')
--
--
Select
--
@UserDB1Trans = UserDB1,
@UserDB2Trans = UserDB2,
@UserDB3Trans = UserDB3,
@UserDB4Trans = UserDB4,
--
@tempdbTrans = [tempdb],
@TotalTrans = [_Total],
--
@LockRequests = [Lock Requests/sec],
@LockTimeouts = [Lock Timeouts/sec],
@Deadlocks = [Number of Deadlocks/sec],
@LockWaits = [Lock Waits/sec],
@LatchWaits = [Latch waits/sec],
@FullScans = [Full Scans/sec],
@Workfiles = [Workfiles Created/sec],
@Worktables = [Worktables Created/sec],
@IndexSearches = [Index Searches/sec],
@PageSplits = [Page Splits/sec],
@Compilations = [SQL Compilations/sec]
from ServerAdmin.dbo.PerfData_Wide
where numkey = 1
--
-- Now "cross tab" to a wide table so i can read the values hourly
--
set @count=1
while @count<=(select max(NumKey) from ServerAdmin.dbo.TranTable)
BEGIN
select @counter=quotename(rtrim(counter)),@value=isnull(value,0) from ServerAdmin.dbo.TranTable where NumKey=@count
set @cmd= 'Update ServerAdmin.dbo.PerfData_wide set '+@counter+' = '+convert(varchar,@value)+' where numkey=1'
exec (@cmd)
--print @cmd
set @count=@count+1
END
--endwhile
--
-- Now write this data to the daily stats table
--
insert into ServerAdmin.dbo.Daily_Statistics
(
CacheHitRatio,
cpu_busy,io_busy,
Logins,
UserDB1Trans,
UserDB2Trans,
UserDB3Trans,
UserDB4Trans,
TempdbTransactions,
TotalTransactions,
LockRequests,LockTimeouts,Deadlocks,LockWaits,latchWaits,
FullScans,WorkFiles,WorkTables,IndexSearches,PageSplits,Compilations,Connections,PacketsReceived,PacketsSent,PacketErrors,Reads,Writes,RWErrors)
Select
(@cachehit/@chachebase *100.00),
abs(@cpu_busy_upd *32), -- see KB813199
abs(@io_busy_upd*32), -- see KB813199
@userconnections,
abs(([AspState]-@AspStateTrans)/3600),
abs(([PerformanceTest]-@PerformanceTestTrans)/3600),
abs(([TitanPilot]-@TitanPilotTrans)/3600),
abs(([TitanProduction]-@TitanProductionTrans)/3600),
abs(([tempdb]-@tempdbTrans)/3600),
abs(([_Total]-@TotalTrans)/3600),
abs(([Lock Requests/sec]-@LockRequests)/3600),
abs(([Lock Timeouts/sec]-@LockTimeouts)/3600),
abs([Number of Deadlocks/sec]-@Deadlocks),
abs(([Lock Waits/sec]-@LockWaits)/3600),
abs(([Latch waits/sec]-@LatchWaits)/3600),
abs(([Full Scans/sec]-@FullScans)/3600),
abs(([Workfiles Created/sec]-@Workfiles)/3600),
abs(([Worktables Created/sec]-@Worktables)/3600),
abs(([Index Searches/sec]-@IndexSearches)/3600),
abs(([Page Splits/sec]-@PageSplits)/3600),
abs(([SQL Compilations/sec]-@Compilations)/3600),
abs(@connections_upd),
abs(@packet_received_upd),
abs(@packet_sent_upd),
abs(@packet_error_upd),
abs(@total_reads_upd),
abs(@total_writes_upd),
abs(@rw_errors_upd)
from ServerAdmin.dbo.PerfData_Wide
where numkey = 1
GO
|