April 18, 2012

sp_dbmmonitorresults (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_dbmmonitorresults(nvarchar @database_name
, int @mode
, int @update_table)

MetaData:

 create procedure sys.sp_dbmmonitorresults   
(
@database_name sysname, -- name of database
@mode int = 0, -- 0 = last row, 1 last two hours, 2 last four, 3 last eight, 4 last day
-- 5 last two days, 6 last 100, 7 last 500, 8 last 1000, 9 all.
@update_table int = 0 -- if 1, then generate a row in the base table and then return.
-- Going to leave this as an int. Could be bit, but this is more flexible.
)
as
begin
set nocount on
if (is_srvrolemember(N'sysadmin') <> 1 and isnull(is_member(N'dbm_monitor'), 0) <> 1 )
begin
raiserror(32046, 16, 1)
return 1
end
if ( db_name() != N'msdb' )
begin
raiserror(32045, 16, 1, N'sys.sp_dbmmonitorresults')
return 1
end

if( @mode < 0 or @mode > 9 or @update_table < 0 or @update_table > 1 )
begin
raiserror( 32036, 16, 1 )
return 1
end
--
-- Check if the database specified exists
--
if not exists (select * from master.sys.databases where name = @database_name)
begin
raiserror(15010, 16, 1, @database_name)
return 1
end

declare
@database_id smallint,
@role bit,
@status tinyint,
@witness_status tinyint,
@log_flush_rate bigint ,
@send_queue_size bigint ,
@send_rate bigint ,
@redo_queue_size bigint ,
@redo_rate bigint ,
@transaction_delay bigint ,
@transactions_per_sec bigint ,
@time datetime ,
@end_of_log_lsn numeric(25,0),
@failover_lsn numeric(25,0),
@average_delay int,
@local_time datetime

declare
@role2 bit,
@status2 tinyint,
@witness_status2 tinyint,
@log_flush_rate2 bigint ,
@send_queue_size2 bigint ,
@send_rate2 bigint ,
@redo_queue_size2 bigint ,
@redo_rate2 bigint ,
@transaction_delay2 bigint ,
@transactions_per_sec2 bigint ,
@time2 datetime ,
@end_of_log_lsn2 numeric(25,0),
@failover_lsn2 numeric(25,0),
@diff_time bigint,
@local_time2 datetime,
@time_update_window datetime

declare
@time_behind datetime,
@temp_lsn numeric(25,0),
@time_cutoff datetime,
@rows_to_return int

declare @results table(
database_name sysname, -- Name of database
role tinyint, -- 1 = Principal, 2 = Mirror
mirroring_state tinyint, -- 0 = Suspended, 1 = Disconnected, 2 = Synchronizing, 3 = Pending Failover, 4 = Synchronized
witness_status tinyint, -- 1 = Connected, 2 = Disconnected
log_generation_rate int null, -- in kb / sec
unsent_log int, -- in kb
send_rate int null, -- in kb / sec
unrestored_log int, -- in kb
recovery_rate int null, -- in kb / sec
transaction_delay int null, -- in ms
transactions_per_sec int null, -- in trans / sec
average_delay int, -- in ms
time_recorded datetime,
time_behind datetime,
local_time datetime -- Added for UI
)

set @database_id = DB_ID( @database_name )
-- if we update the table anyway, the table will be created
if (@update_table = 1)
begin
if( is_srvrolemember(N'sysadmin') = 1 )
begin
-- Check if the base table is there
if object_id ( N'msdb.dbo.dbm_monitor_data', N'U' ) is null
begin
exec sys.sp_dbmmonitorupdate @database_name
end
else
begin -- UI wants us to update base table, but don't do it if less than 15 seconds since last update.
select top(1) @time_update_window = time from msdb.dbo.dbm_monitor_data
where database_id = @database_id order by time desc
if ( datediff( second, @time_update_window, getutcdate() ) > 15 or @time_update_window is null)
begin
exec sys.sp_dbmmonitorupdate @database_name -- replace(@database_name, N'''',N'''''')
end
end
end
else
begin
raiserror(32051, 10, 1) -- Inform user that although asked to update the table, the user does not have rights to do it.
end
end

-- Here's what we do
-- 1. create a cursor to loop over the rows in the data table
-- 2. get one row of data
-- 3. while (we should loop is true)
-- 4. get the next row of data might break out of the loop here.
-- 5. calculate differences between rates
-- 6. calculate the difference in "time" between the failover LSN (what the mirror has) and the end of log LSN (the latest value on the principal)
-- 7. insert the data into the table
-- 8. copy older values of data into newer values (basically we are doing step 2 here. or putting step 4's data into the original
-- variables since we are done with that information anyway.
-- 9. loop to 3.

declare mirroring_cursor cursor local
for select
role, status, witness_status, log_flush_rate, send_queue_size, send_rate, redo_queue_size, redo_rate, transaction_delay, transactions_per_sec, time, end_of_log_lsn, failover_lsn, local_time
from msdb.dbo.dbm_monitor_data
where database_id = @database_id order by time desc

open mirroring_cursor

fetch next from mirroring_cursor
into @role, @status, @witness_status, @log_flush_rate, @send_queue_size, @send_rate, @redo_queue_size,
@redo_rate, @transaction_delay, @transactions_per_sec, @time, @end_of_log_lsn, @failover_lsn, @local_time

set @rows_to_return = 0
set @time_cutoff = getutcdate()
if @mode = 0
begin
set @rows_to_return = 1
end
else if @mode = 1
begin
set @time_cutoff = getutcdate() - ( 2. / 24.)
end
else if @mode = 2
begin
set @time_cutoff = getutcdate() - ( 4. / 24.)
end
else if @mode = 3
begin
set @time_cutoff = getutcdate() - ( 8. / 24.)
end
else if @mode = 4
begin
set @time_cutoff = getutcdate() - ( 1.)
end
else if @mode = 5
begin
set @time_cutoff = getutcdate() - ( 2.)
end
else if @mode = 6
begin
set @rows_to_return = 100
end
else if @mode = 7
begin
set @rows_to_return = 500
end
else if @mode = 8
begin
set @rows_to_return = 1000
end
else if @mode = 9
begin
set @rows_to_return = 1000000
end

while (@time > @time_cutoff or @rows_to_return > 0)
begin
if @rows_to_return > 0
set @rows_to_return = @rows_to_return - 1

fetch next from mirroring_cursor
into @role2, @status2, @witness_status2, @log_flush_rate2, @send_queue_size2, @send_rate2, @redo_queue_size2,
@redo_rate2, @transaction_delay2, @transactions_per_sec2, @time2, @end_of_log_lsn2, @failover_lsn2, @local_time2

if( @@fetch_status <> 0 ) -- this is the fetch_status that we want to break out of the loop
break

set @diff_time = datediff( second, @time2, @time )
if (@diff_time = 0)
begin
set @log_flush_rate = 0
set @send_rate = 0
set @redo_rate = 0
set @transactions_per_sec = 0
set @transaction_delay = 0
end
else if (@role != @role2) -- if the role has changed then the rate counters are meaningless
begin
set @log_flush_rate = null
set @send_rate = null
set @redo_rate = null
set @transactions_per_sec = null
set @transaction_delay = null
end
else
begin
if (@log_flush_rate < @log_flush_rate2 )
begin
set @log_flush_rate = null
end
else
begin
set @log_flush_rate = (@log_flush_rate - @log_flush_rate2 ) / ( @diff_time * 1024) -- This is in kilobytes
end

if (@send_rate < @send_rate2)
begin
set @send_rate = null
end
else
begin
set @send_rate = (@send_rate - @send_rate2)/ ( @diff_time * 1024 )
end

if (@redo_rate < @redo_rate2)
begin
set @redo_rate = null
end
else
begin
set @redo_rate = (@redo_rate - @redo_rate2)/ ( @diff_time * 1024 )
end

if (@transactions_per_sec < @transactions_per_sec2 )
begin
set @transactions_per_sec = null
end
else
begin
set @transactions_per_sec = (@transactions_per_sec - @transactions_per_sec2 ) / @diff_time
end

if (@transaction_delay < @transaction_delay2)
begin
set @transaction_delay = null
end
else
begin
set @transaction_delay = (@transaction_delay - @transaction_delay2 ) / @diff_time
end
end

if (@transactions_per_sec = 0)
begin
set @average_delay = 0
end
else
begin
set @average_delay = @transaction_delay / @transactions_per_sec
end
-- Here we are going to match the failover lsn to time
if ((@failover_lsn > @end_of_log_lsn) and (@end_of_log_lsn <> 0) ) -- this should never happen
begin
return 1
end
else
begin
if ( (@failover_lsn = @end_of_log_lsn) or (@end_of_log_lsn = 0) ) -- I've seen cases after a failover or the system just start up
begin -- where the failover_lsn does not catch up to the end_of_log_lsn
set @time_behind = @time -- until some log is generated. we'll see how this works.
end
else
begin
select top(1) @time_behind = time from msdb.dbo.dbm_monitor_data
where end_of_log_lsn <= @failover_lsn and database_id = @database_id order by time desc
end
end

insert @results
values (
@database_name,
@role + 1,
@status,
@witness_status,
@log_flush_rate,
@send_queue_size,
@send_rate,
@redo_queue_size,
@redo_rate,
@transaction_delay,
@transactions_per_sec,
@average_delay,
@time,
@time_behind,
@local_time
)

set @role = @role2
set @status = @status2
set @witness_status = @witness_status2
set @send_queue_size = @send_queue_size2
set @redo_queue_size = @redo_queue_size2
set @end_of_log_lsn = @end_of_log_lsn2
set @failover_lsn = @failover_lsn2
set @log_flush_rate = @log_flush_rate2
set @send_rate = @send_rate2
set @redo_rate = @redo_rate2
set @transactions_per_sec = @transactions_per_sec2
set @transaction_delay = @transaction_delay2
set @time = @time2
set @local_time = @local_time2
end

close mirroring_cursor
deallocate mirroring_cursor

select * from @results
return 0
end

No comments:

Post a Comment

Total Pageviews