Skip to Content

IDM performance metrics and counters

I’ve made an experimental procedure that collects a number of what I think are useful counters about the provisioning queue and the state of the IdM system. It can be used to detect bottlenecks, generate performance statistics, load predictions, and be a good utility for troubleshooting a system you have a halt situation or if the queue is moving along. It’s using stuff, pivot and some other query types I’ve been testing for my own sake, and as such it may not be optimized.

It is also currently SQL Server only and as I’m leaving SAP the chances that I will be porting this to Oracle, DB2 or HANA are minimal. You are most welcome to do so yourself, as well as modifying and improving it. The query this procedure uses are mostly already documented in my other blogs, and I’ve also added some that reads CPU usage that I found online at SQLPerformance.wordpress.com.

The idea is that this procedure is run every X seconds, minutes or hours and the result can be used to build graphs displaying what is going (if anything) on during a performance test, deployment or when troubleshooting system performance. I’ll to show how the results look first, then how it is used afterwards. Interpreting the results will be up to you and the situation you’re in, but in metrics like “ready to run” and “running” it is better to have many “running” and few “ready to run” etc.

This will be one of my very last blog posts here on SDN and I hope you liked it 🙂

A test scenario and example data

I have a test job that runs 1000 entries through an obstacle course of conditional, switch and ordered tasks, with some child tasks and wait for functions thrown in for good measure. . It takes about 4 minutes to complete so during the test I gather statistics every 5 seconds using this little statement that I ran in SQL Server Management Studio:


DECLARE @STOPTIME DATETIME
DECLARE @INTERVAL VARCHAR(8)
SET @STOPTIME = '2014-08-06 14:50:00'
SET @INTERVAL = '00:00:05'
WHILE GETDATE() < @STOPTIME
BEGIN
  BEGIN TRANSACTION
  exec mc_ProvQStats 0,0,0 -- Parameters for gather stats,no listing
  COMMIT
  WAITFOR DELAY @INTERVAL
END









You could also execute the procedure from a job scheduled to run every X seconds, but then you depend on the system actually running and having the capacity to run the job at the specific intervals during load, which might not work during a performance test or a halt situation.

The result produces “interesting” stuff like this when put into Excel:

statsTestQueueProcessing.png

Here is a sample run where I killed the dispatcher in the middle showing the stats flat lining as nothing was processed for a minute or two and that the active dispatcher count changed from 1 to 0in the a graph for the active dispatcher count metric…

statsTestQueueProcessingDeadDispatcher.png

Using the procedure

mc_ProvQStats <OP>, <LISTLEVEL>,<RETURN ROWS>

OP = 0 – Gather statistics only, return no data

OP = 1 – Gather statistics, return the values

OP = 2 – Don’t gather statistics, return data as specified by parameters LISTLEVEL and RETURN ROWS

LISTLEVEL = 0 – return nothing (mc_provqueuestats_desc.mShow = 0)

LISTLEVEL = 1 – return entries where mc_provqueuestats_desc.mShow <= 1

LISTLEVEL = 2 – return entries where mc_provqueuestats_desc.mShow <= 2

RETURN ROWS = Number of rows to return, newest collection first.

Examples:

Just collect stats: exec mc_ProvQStats 0,0,0

Collect stats and return them: exec mc_ProvQStats 1,0,0

List last 10 collections; exec mc_ProvQStats 2,1,10

statsUsageExample.png

The counters and Metrics collected

This is the list of metrics and counters it collects and can display. It is stored in the table mc_provqueuestats_desc

Starting analysis The datetime when this analysis-run was started and the parameters used. Not useful at the moment but I had plans 🙂
Host CPU usage Total CPU usage on the host computer. -1 indicates missing grant, GRANT VIEW SERVER STATE TO mxmc_rt
SQL Server CPU usage CPU usage by the SQL process. -1 indicates missing grant, GRANT VIEW SERVER STATE TO mxmc_rt
Provision queue size The total number of all entries in the queue
Provision queue unique audits The number of unique audits in the queue
Task execution delta Number of tasks executed/added to ext audit previous measurement
Ordered tasks in queue The total number of ordered tasks in the queue
Ordered tasks ready to run in queue The total number of ordered tasks ready to run  in the queue
Unordered tasks in queue The total number of unordered tasks in the queue
Unordered tasks ready to run in queue The total number of unordered tasks ready to run in the queue
Conditional tasks in queue The total number of conditional tasks in the queue
Conditional tasks ready to run in queue The total number of conditional tasks ready to run in the queue
Switch tasks in queue The total number of switch tasks in the queue
Switch tasks ready to run in queue The total number of switch tasks ready to run in the queue
Approval tasks in queue The total number of approval tasks in the queue
Approval tasks ready to run in queue The total number of approval tasks ready to run in the queue
Attestation tasks in queue The total number of attestation tasks in the queue
Attestation tasks ready to run in queue The total number of attestation tasks ready to run in the queue
Action tasks in queue Total number of tasks in the queue
Action tasks ready to run in queue Total number of tasks ready to run in the queue
Jobs ready to run The number of jobs that are ready to run
Jobs running The number of currently running jobs
Provision jobs running The number of currently running provision jobs
Provision jobs ready to run The number of provision jobs ready to run in the queue
Runtimes allowed by system The maximum number of runtimes allowed. 0 means no total system limit but it can still be limited per dispatcher
Runtimes active The number of jobs marked as running
Threshold violation delta The number of new entries in the execution threshold log since previous statistic collection
Jobs in error state The number of jobs that are in error state and cannot be run
Dispatchers active The number of dispatchers reported active the last <reload frequency> seconds
Dispatchers inactive The number of dispatchers not reported active the last <reload frequency> seconds

The mc_provqueuestats_desc table

Column Value
mItem The Item name
mType The item data type, C=Counter(INT), DT=DateTime, V=Varchar
mDesc Description of the item
mShow NULL=don’t show when listing results, numbers indicate the report level to include it on.
mOrder (identity), column order in listing

Sample data:

mItem mType mDesc mShow mOrder
Starting analysis DT
The datetime when this analysis-run was started
1 1
Host CPU usage C Total CPU usage on the host computer. -1 indicates missing grant 1 2
SQL Server CPU usage C CPU usage by the SQL process. -1 indicates missing grant, 1 3
Max auditid value C The maximum auditid value at the time of measurement NULL 4
Provision queue size C The total number of all entries in the queue 1 6

The mc_provqueuestats table

The metric data is collected in this table and it is also quite simple:

Column Value
mId ID for metric collection run
mDT DateTime for collection of value
mItem Item name for collected metric – MUST MATCH with mc_provqueuestats_desc.mItem
mValue Optional VARCHAR value collected
mCount Optional INT value collected
mDateTime Optional DATATIME value collected

Sample data:

mId mDT mItem mValue mCount mDateTime
1 2014-08-06 14:39:55.010 Starting analysis 0:0 NULL 2014-08-06 14:39:55.010
1 2014-08-06 14:39:55.063 Host CPU usage NULL 10 NULL
1 2014-08-06 14:39:55.063 Provision queue size NULL 0 NULL
2 2014-08-06 14:40:00.070 Starting analysis 0:0 NULL 2014-08-06 14:40:00.070
2 2014-08-06 14:40:00.110 Host CPU usage NULL 10 NULL
2 2014-08-06 14:40:00.110 Provision queue size NULL 0 NULL
3 2014-08-06 14:40:05.110 Starting analysis 0:0 NULL 2014-08-06 14:40:05.110
3 2014-08-06 14:40:05.150 Host CPU usage NULL 10 NULL
3 2014-08-06 14:40:05.150 Provision queue size NULL 0 NULL

(Note that each collection (mId) will really have about 33 rows of data)

The procedures, tables and optional indexes and grants

Optional indexes

Some of the metrics require additional indexes to work. If you enable these, make sure that you change line 12 in the procedure mc_ProvQStats from

SET @P_GOTINDEXES = 0

To

SET @P_GOTINDEXES = 1

Don’t do this change without the following indexes as that will cause some of the metrics collection queries to do tablescans, which are bad.


------------------------------------
-- ADDITIONAL INDEXES             --
-- Update procedure mc_ProvQStats --
-- SET @P_GOTINDEXES = 1 to use   --
------------------------------------
CREATE NONCLUSTERED INDEX [IX_MXI_EXT_AUDIT_DATE] ON [dbo].[MXP_Ext_Audit]
(
  [Aud_datetime] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_MC_EXEC_STAT_DT] ON [dbo].[mc_exec_stat]
(
  [mcDateTime] ASC
)
GO



CPU Usage statistics, additional grant

To get the SQL Server hos CPU states the procedure must run as a user that has been granted VIEW SERVER STATE. So run the following for oper, RT or the logon you’re using


GRANT VIEW SERVER STATE TO mxmc_rt



Creating the tables


if exists (select * from sysobjects where id = object_id(N'[dbo].[mc_provqueuestats]') and
  OBJECTPROPERTY(id, N'IsTable') = 1)
    drop table [dbo].[mc_provqueuestats]
GO
CREATE TABLE [dbo].[mc_provqueuestats](
  [mId] [int] NOT NULL,
  [mDT] [datetime] NULL,
  [mItem] [varchar](255) NULL,
  [mValue] [varchar](512) NULL,
  [mCount] [int] NULL,
  [mDateTime] [datetime] NULL
) ON [PRIMARY]
GO
GRANT SELECT, INSERT, DELETE ON dbo.mc_provqueuestats TO mxmc_rt_role
GO
CREATE NONCLUSTERED INDEX [IX_MCPROVQSTAT_IDITDT] ON [dbo].[mc_provqueuestats]
(
  [mId] ASC,
  [mItem] ASC,
  [mDT] ASC
)
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[mc_provqueuestats_desc]') and
  OBJECTPROPERTY(id, N'IsTable') = 1)
    drop table [dbo].[mc_provqueuestats_desc]
GO
CREATE TABLE [dbo].[mc_provqueuestats_desc](
  [mItem] [varchar](255) NULL,
  [mType] [varchar](2) NULL,
  [mDesc] [varchar](512) NULL,
  [mShow] [int] NULL,
  [mOrder] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
GRANT SELECT ON dbo.mc_provqueuestats_desc TO mxmc_rt_role
GO
CREATE NONCLUSTERED INDEX [IX_MCPROVQSTATDESC_ITDE] ON [dbo].[mc_provqueuestats_desc]
(
  [mItem] ASC,
  [mDesc] ASC,
  [mShow] ASC
)
GO
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Starting analysis','DT','The datetime when this analysis-run was started','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Host CPU usage','C','Total CPU usage on the host computer. -1 indicates missing grant, GRANT VIEW SERVER STATE TO mxmc_rt','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('SQL Server CPU usage','C','CPU usage by the SQL process. -1 indicates missing grant, GRANT VIEW SERVER STATE TO mxmc_rt','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Max auditid value','C','The maximum auditid value at the time of measurement',NULL)
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc)   VALUES ('Max extaudit datetime value','DT','The maximum datetime in the extended audit at the time of measurement')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Provision queue size','C','The total number of all entries in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Provision queue unique audits','C','The number of unique audits in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Task execution delta','C','Number of tasks executed/added to ext audit previous measurement','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Ordered tasks in queue','C','The total number of ordered tasks in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Ordered tasks ready to run in queue','C','The total number of ordered tasks ready to run in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Unordered tasks in queue','C','The total number of unordered tasks in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Unordered tasks ready to run in queue','C','The total number of unordered tasks ready to run in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Conditional tasks in queue','C','The total number of conditional tasks in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Conditional tasks ready to run in queue','C','The total number of conditional tasks ready to run in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Switch tasks in queue','C','The total number of switch tasks in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Switch tasks ready to run in queue','C','The total number of switch tasks ready to run in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Approval tasks in queue','C','The total number of approval tasks in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Approval tasks ready to run in queue','C','The total number of approval tasks ready to run in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Attestation tasks in queue','C','The total number of attestation tasks in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Attestation tasks ready to run in queue','C','The total number of attestation tasks ready to run in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Action tasks in queue','C','Total number of tasks in the queue','2')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Action tasks ready to run in queue','C','Total number of tasks ready to run in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Jobs ready to run','C','The number of jobs that are ready to run','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Jobs running','C','The number of currently running jobs','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Provision jobs ready to run','C','The number of provision jobs ready to run in the queue','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Provision jobs running','C','The number of currently running provision jobs','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Runtimes allowed by system','C','The maximum number of runtimes allowed. 0 means no total system limit but it can still be limited per dispatcher','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Runtimes active','C','The number of jobs marked as running','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc)   VALUES ('Threshold violation max timestamp','DT','The highest datatime of execution threshold logged')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Threshold violation delta','C','The number of new entries in the execution threshold log since previous statistic collection','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Jobs in error state','C','The number of jobs that are in error state and can not be run','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Dispatchers active','C','The number of dispatchers reported active the last <reload frequency> seconds','1')
INSERT INTO [mc_provqueuestats_desc] (mItem,mType,mDesc,mShow) VALUES ('Dispatchers inactive','C','The number of dispatchers not reported active the last <reload frequency> seconds','2')
GO



Creating the procedure


CREATE PROCEDURE [dbo].[mc_ProvQStats]
    @P_OP int,       -- 0=Just add stats to table, 1=Add stats and list collected data, 2=List previous collected data
  @P_LISTLEVEL INT,-- 0=Dont list, 1=basic, 2=all
  @P_LISTROWS  INT -- Number of rows to list
AS
  DECLARE
  @L_C INT,@L_I INT, @L_V VARCHAR(255), @L_DT DATETIME, @L_MID INT, @L_QUEUESIZE INT, @L_QS_ATTST INT, @L_QS_APPR INT, @L_QS_SW INT, @L_QS_CDTL INT, @L_QS_UNORD INT, @L_QS_ORDRD INT, @L_QS_ACT INT,
  @L_AUDITID INT, @L_CHILDAUDIT INT, @L_CHILDAUDITINQUEUE INT, @L_CHILDAUDITMSG VARCHAR(255),@L_LEVEL VARCHAR(255),
  @L_QAUDITREF INT,@L_QACTIONID INT,@L_QACTIONTYPE INT,@L_QMSKEY INT,@L_QWAITCOUNT INT,@L_QEXECTIME DATETIME,@L_QUPDTIME DATETIME,@L_QREPOSITORYID INT,@L_QMSG VARCHAR(255),
  @L_COLUMNS NVARCHAR(MAX), @L_QUERY NVARCHAR(MAX), @L_LIMITER VARCHAR(255), @ts_now bigint, @L_HOSTCPU INT, @L_SQLCPU INT, @P_GOTINDEXES INT
  SET @P_GOTINDEXES = 0
    SET NOCOUNT ON
  SET @L_MID = (SELECT ISNULL(MAX(MID),0)+1 FROM mc_provqueuestats WITH(NOLOCK))
  IF @P_OP NOT IN (0,1,2) AND @P_LISTLEVEL NOT IN (1,2)
  BEGIN
  print 'Illegal operation ' + convert(varchar,@P_OP) + ' - 0=List only)' --, 1 (retry without provisioning), or 2 (retry with provisioning))'
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mValue) VALUES (@L_MID,getdate(),'Invalid parameter(s)',cast(@P_OP as varchar)+':'+cast(@P_LISTROWS as varchar))
  RETURN
  END
  IF @P_OP IN (0,1)
  BEGIN
  -----------------------
  -- GATHER STATISTICS --
  -----------------------
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mValue,mDateTime) VALUES (@L_MID,getdate(),'Starting analysis',cast(@P_OP as varchar)+':'+cast(@P_LISTROWS as varchar),getdate())
  --------------------------------
  -- SETTINGS AND MISC COUNTERS --
  --------------------------------
  -- Get max allowed runtimes
  SET @L_C = (SELECT CAST(VARVALUE as INT) FROM MC_GLOBAL_VARIABLES WITH(NOLOCK) WHERE VARNAME = 'MX_MAX_CONCURRENT_RUNTIME_ENGINES')
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Runtimes allowed by system',@L_C)
  -- Get total running runtimes
  SET @L_C = (SELECT COUNT(*) FROM MC_JOBS WITH(NOLOCK) WHERE STATE=2)
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Runtimes active',@L_C)
  -- THESE REQUIRE ADDITIONAL INDEXES...
  IF @P_GOTINDEXES=1
  BEGIN
  --Execution threshold violation delta
  SET @L_DT = (SELECT mDateTime FROM mc_provqueuestats WITH(NOLOCK) WHERE mid = @L_MID -1 AND mItem = 'Threshold violation max timestamp')
  IF @L_DT IS NOT NULL SET @L_C = (SELECT COUNT(mcDateTime) FROM mc_exec_stat WITH(NOLOCK) WHERE mcDateTime > @L_DT)
  ELSE SET @L_C = 0
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Threshold violation delta',@L_C)
  --Execution threshold violation max timestamp
  SET @L_DT = (SELECT MAX(mcDateTime) FROM mc_exec_stat)
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mDateTime) VALUES (@L_MID,getdate(),'Threshold violation max timestamp',@L_DT)
  -- Get current max ext-audit time
  SET @L_DT = (SELECT MAX(AUD_DATETIME) FROM MXP_EXT_AUDIT WITH(NOLOCK))
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mDateTime) VALUES (@L_MID,getdate(),'Max extaudit datetime value',@L_DT)
  SET @L_DT = (SELECT mDateTime FROM mc_provqueuestats WITH(NOLOCK) WHERE mid = @L_MID -1 AND mItem = 'Max extaudit datetime value')
  IF @L_DT IS NOT NULL SET @L_C = (SELECT COUNT(AUD_DATETIME) FROM MXP_EXT_AUDIT WITH(NOLOCK) WHERE AUD_DATETIME > @L_DT)
  ELSE SET @L_C = 0
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Task execution delta',@L_C)
  END
  -- JOBS IN ERROR STATE
  SET @L_C = (SELECT COUNT(*) FROM MC_JOBS WITH(NOLOCK) WHERE STATE = -1)
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Jobs in error state',@L_C)
  -- JOBS READY TO RUN
  SET @L_C = (SELECT COUNT(*) FROM MC_JOBS WITH(NOLOCK) WHERE STATE = 1 AND PROVISION = 0 AND ScheduledTime < getdate())
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Jobs ready to run',@L_C)
  -- RUNNING JOBS
  SET @L_C = (SELECT COUNT(*) FROM MC_JOBS WITH(NOLOCK) WHERE STATE = 2 AND PROVISION = 0)
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Jobs running',@L_C)
  -- PROVISION JOBS READY TO RUN
  SET @L_C = (SELECT COUNT(*) FROM MC_JOBS J WITH(NOLOCK)WHERE STATE=1 AND PROVISION = 1 AND JOBGUID IN
  (SELECT JOBGUID FROM MXP_TASKS T WITH(NOLOCK)WHERE T.TASKID IN (SELECT DISTINCT(P.ActionID) FROM MXP_PROVISION P WITH(NOLOCK) WHERE P.ActionType=0 AND P.State=2) )  )
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Provision jobs ready to run',@L_C)
  -- RUNNING PROVISION JOBS
  SET @L_C = (SELECT COUNT(*) FROM MC_JOBS WHERE STATE = 2 AND PROVISION = 1)
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Provision jobs running',@L_C)
  -- DISPATCHERS APPEARING TO BE INACTIVE. Does not refresh exactly at reload time, so double it
  SET @L_C = (SELECT count(*) from MC_Dispatcher WITH(NOLOCK) WHERE last_visited < dateadd(ss,(RELOAD_FREQUENCY*-2),getdate()) )
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Dispatchers inactive',@L_C)
  -- DISPATCHERS APPEARING TO BE ACTIVE.
  SET @L_C = (SELECT count(*)  from MC_Dispatcher WITH(NOLOCK) WHERE last_visited > dateadd(ss,(RELOAD_FREQUENCY*-2),getdate()) )
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Dispatchers active',@L_C)
  -- CPU Usage
  BEGIN TRY
  SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
  SELECT top 1
  @L_HOSTCPU = CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END, -- AS system_cpu_utilization,
  @L_SQLCPU = CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END-- AS sql_cpu_utilization
  FROM
  (
  SELECT record.value('(Record/@id)[1]', 'int') AS record_id, DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
  100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
  record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
  100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
  record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
  FROM (SELECT timestamp, CONVERT (xml, record) AS record FROM sys.dm_os_ring_buffers
  WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS t
  ) AS t ORDER BY record_id desc
  END TRY
  BEGIN CATCH
  SET @L_HOSTCPU = -1
  SET @L_SQLCPU = -1
  END CATCH
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Host CPU usage',@L_HOSTCPU)
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'SQL Server CPU usage',@L_SQLCPU)
  -----------------
  -- PROVQ STUFF --
  -----------------
  -- Get current max auditid
  SET @L_C = ( SELECT MAX(AUDITID) FROM MXP_AUDIT WITH(NOLOCK) )
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Max auditid value',@L_C)
  SET @L_QUEUESIZE = ( SELECT COUNT(*) FROM MXP_PROVISION WITH(NOLOCK) )
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Provision queue size',@L_QUEUESIZE)
  SET @L_C = ( SELECT COUNT(DISTINCT(AUDITREF)) FROM MXP_PROVISION WITH(NOLOCK) )
  INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Provision queue unique audits',@L_C)
  DECLARE C_PQ_ALL CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  SELECT COUNT(P.MSKEY) C,T.ActType ActionType FROM MXP_ActionType T WITH (NOLOCK) LEFT OUTER JOIN MXP_PROVISION P  WITH (NOLOCK)ON T.ActType=P.ACTIONTYPE AND P.MSKEY IS NOT NULL GROUP BY T.ActType
  OPEN C_PQ_ALL
  FETCH NEXT FROM C_PQ_ALL INTO @L_QUEUESIZE, @L_I
  WHILE (@@Fetch_status = 0)
  BEGIN
  IF @L_I =  0 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Action tasks in queue',@L_QUEUESIZE)
  IF @L_I = -1 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Ordered tasks in queue',@L_QUEUESIZE)
  IF @L_I = -2 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Unordered tasks in queue',@L_QUEUESIZE)
  IF @L_I = -3 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Conditional tasks in queue',@L_QUEUESIZE)
  IF @L_I = -4 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Switch tasks in queue',@L_QUEUESIZE)
  IF @L_I = -5 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Approval tasks in queue',@L_QUEUESIZE)
  IF @L_I = -6 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Attestation tasks in queue',@L_QUEUESIZE)
  FETCH NEXT FROM C_PQ_ALL INTO @L_QUEUESIZE, @L_I
  END
  CLOSE C_PQ_ALL
  DEALLOCATE C_PQ_ALL
  DECLARE C_PQ_ALL CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  SELECT COUNT(P.MSKEY) C,T.ActType ActionType FROM MXP_ActionType T WITH (NOLOCK) LEFT OUTER JOIN MXP_PROVISION P  WITH (NOLOCK) ON T.ActType=P.ACTIONTYPE AND P.STATE=2 AND P.MSKEY IS NOT NULL GROUP BY T.ActType
  --SELECT COUNT(*) c ,ActionType FROM MXP_PROVISION WITH(NOLOCK) WHERE STATE=2 GROUP BY ActionType
  OPEN C_PQ_ALL
  FETCH NEXT FROM C_PQ_ALL INTO @L_QUEUESIZE, @L_I
  WHILE (@@Fetch_status = 0)
  BEGIN
  IF @L_I = 0 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Action tasks ready to run in queue',@L_QUEUESIZE)
  IF @L_I = -1 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Ordered tasks ready to run in queue',@L_QUEUESIZE)
  IF @L_I = -2 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Unordered tasks ready to run in queue',@L_QUEUESIZE)
  IF @L_I = -3 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Conditional tasks ready to run in queue',@L_QUEUESIZE)
  IF @L_I = -4 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Switch tasks ready to run in queue',@L_QUEUESIZE)
  IF @L_I = -5 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Approval tasks ready to run in queue',@L_QUEUESIZE)
  IF @L_I = -6 INSERT INTO mc_provqueuestats (mid,mdt,mitem,mCount) VALUES (@L_MID,getdate(),'Attestation tasks ready to run in queue',@L_QUEUESIZE)
  FETCH NEXT FROM C_PQ_ALL INTO @L_QUEUESIZE, @L_I
  END
  CLOSE C_PQ_ALL
  DEALLOCATE C_PQ_ALL
  END
  ---------------------
  -- LIST STATISTICS --
  ---------------------
  IF @P_OP IN (1,2) AND @P_LISTLEVEL > 0 -- List current or all
  BEGIN
  SET @L_QUERY = 'SELECT @L_COLUMNS = STUFF((SELECT '','' + QUOTENAME(mItem) FROM [mc_provqueuestats_desc] where mShow <= '+CAST(@P_LISTLEVEL as varchar)+' order by mOrder FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,1,'''') '
  --print @L_QUERY
  exec sp_executeSQL @L_QUERY,N'@L_COLUMNS NVARCHAR(MAX) OUT',@L_COLUMNS OUT
  --PRINT @L_COLUMNS
  IF @P_OP = 1 SET @L_LIMITER = 'WHERE mId = '+cast(@L_MID as varchar)+' AND'
  ELSE SET @L_LIMITER = ' WHERE '
  set @L_QUERY = 'SELECT TOP '+CAST(ISNULL(@P_LISTROWS,1) AS VARCHAR)+' mId, ' + @L_COLUMNS + ' FROM
            (select S.mId,S.mItem,
  case
  when D.mType = ''V'' then cast(mValue as varchar)
  when D.mType = ''C'' then cast(mCount as varchar)
  when D.mType = ''DT'' then convert(varchar,mDateTime,120)
  else ''0''
  end as mValue
                from mc_provqueuestats S left outer join mc_provqueuestats_desc D on S.mItem = D.mItem '+ISNULL(@L_LIMITER,' ')+' D.mShow <= '+CAST(ISNULL(@P_LISTLEVEL,0) as varchar)+'
           ) main
            pivot
            ( max(mValue)
              for mItem in (' + @L_COLUMNS + ')
            ) piv order by mid desc';
  print @L_QUERY
  execute(@L_QUERY);
  END
GO
To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Steffi Warnecke

    I’m with Laurent… we use Oracle. Maybe if Chris gets bored… 😀

    This is great nontheless, because it shows me again, what can be done with such tools.

    Thank you for sharing this!

    (1) 

Leave a Reply