5 Replies Latest reply on Jul 11, 2008 7:15 AM by timfox

    Performance issue for persistent manager of JBoss Messaging

    gary.c.chen

      I 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