Performance issue for persistent manager of JBoss Messaging
gary.c.chen Jul 10, 2008 7:24 AMI am very confused by the internal behavior of persistent manager of jboss messaging with ms sql server.
I use event tracer of ms sql server to capture sql statements sent out from one jms message comes into jms queue to consumed by consumer.
The following sql statements shows one jms message persistent can cause about twenty insert statement to be executed, which cause excessive disk I/O activity.
Does anyone can help me explain why did it happen? And What's the solution to obtain higher performance?
Best Regards.
SET IMPLICIT_TRANSACTIONS ON go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854870163456, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854886809601, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854899261442, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854900899843, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854907125764, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854909747205, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854918266886, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854922854407, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854933012488, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854943825929, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854953984010, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854960537611, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854969057292, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854970368013, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854978560014, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854986424335, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832854998220816, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855000514577, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855007395858, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855020830739, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855023779860, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855029022741, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855034920982, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855045406743, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint ,@P2 bigint ,@P3 bigint ,@P4 nvarchar(4000) ,@P5 bigint ,@P6 bigint ,@P7 int ,@P8 bigint ', 2, 0, NULL, N'C', 39832855048683544, NULL, 0, 0 go IF @@TRANCOUNT > 0 ROLLBACK TRAN go