- SAP Managed Tags:
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:
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...
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
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 |
| 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
3 Comments
