When BPC 5.1 or BPC 7.0 for Microsoft is not performing as expected, people uses variety ways of monitoring methods to identify where/what bottleneck is. Also there is always need of data sending monitoring tool.From BPC 5.0, there is a Windows service to control multiple data send requests in serialized process, Send Governor. It is using basically 2 tables to tracking current data sending status. The records in one of these tables can be used to Windows Performance Monitor counter through “User Settable” performance object of SQL Server. In this method, We are using one stored procedure, one trigger on sgQueue table.
1. Execute “CREATE PROCEDURE…” SQL statement to create stored procedure, BPC_ACT_MONITOR. That statement is in Appendix at end of document.
2. Execute “CREATE TRIGGER…” SQL statement to create trigger, Monitor on sgQueue table. That statement is in Appendix at end of document.
3. Run “perfmon” or open “Performance” in administrative tools in control panel. And add performance counters from “User counter 1” to “User count 4” in “SQLServer:User Settable” object of database server.
4. Do some performance testing of data send. You can see performance counters are changing.
5. Above picture, 4 performance counters are making trend graph of data.
User counter 1 : Number of user send data using Excel
User counter 2 : Number of cells in send using Excel
User counter 3 : Number of logic currently running
User counter 4 : Number of cells(intersections) submitting by logic execution
sgQueue table is storing header information of current data sending. It is including send sequence number, cells of send, issuer of send, module of send and current step. So, we can populate monitoring data from this table.
The ActCode is indicating which module is associated to send. In this document, we are only looking 1 and 4. 1 is send through Excel and 4 is send through script logic no matter default or manual.
In the stored procedure, following items are populating by SQL trigger of changing (INSERT, DELETE, UPDATE) of sgQueue table.
@intNumberOfUserSend : Number of record of ActCode=1
@intNumberOfSendRecord : Sum of “NumberOfRecord” column of ActCode=1
@intNumberOfLogicRun : Number of record of ActCode=4
@intNumberOfLogicRecord : Sum of “NumberOfRecord” column of ActCode=4
These variables are providing to “User settable” object of Windows Performance Counter. SQL Server has 10 counters to be used for custom monitoring purpose. In this document, we are using 4 of them. So, this procedure can be modified to more detail monitoring such as # of current user send from app server N, # of cells are sending through specific input template.
Any time that sgQueue table has been updated, “Monitor” trigger on sgQueue table immediately react to calculated and update performance counter. So, if there are too much overhead on sgQueue table, BPC_ACT_MONITORING stored procedure could be potential bottleneck.And don’t forget to remove “Monitor” trigger on sgQueue table when this monitoring is not required anymore.
Stored procedure
CREATE PROCEDURE [dbo].[BPC_ACT_MONITOR]
AS
/* Procedure to know how many users doing manual input */
DECLARE @intNumberOfUserSend INT
DECLARE @intNumberOfSendRecord INT
DECLARE @intNumberOfLogicRun INT
DECLARE @intNumberOfLogicRecord INT
SELECT @intNumberOfUserSend = COUNT(*) FROM sgQueue WHERE ActCode='1'
SELECT @intNumberOfSendRecord = ISNULL(SUM(NumberOfRecord),0) FROM sgQueue WHERE ActCode='1' AND NumberOfRecord > -1
SELECT @intNumberOfLogicRun = COUNT(*) FROM sgQueue WHERE ActCode='4'
SELECT @intNumberOfLogicRecord = ISNULL(SUM(NumberOfRecord),0) FROM sgQueue WHERE ActCode='4' AND NumberOfRecord > -1
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @intNumberOfUserSend)
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 2', @intNumberOfSendRecord)
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 3', @intNumberOfLogicRun)
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 4', @intNumberOfLogicRecord)
GO
Trigger
CREATE TRIGGER Monitor
ON sgQueue
AFTER INSERT, UPDATE, DELETE
AS
EXEC [BPC_ACT_MONITOR]
GO
1 Comment