[jboss-user] [Microcontainer] - MSSQL Persistent Manager issue

gary.c.chen do-not-reply at jboss.com
Wed Jul 9 07:59:34 EDT 2008


I am so confused on the sql trace when I open persistent mechanism for jms message with mssql server database.

When one jms message is produced and consumed,  the following sql statements has been captured. You can see, one jms message cause 25 times insert statement execution, and It already result in serious performance issue. Does anyone can help me explain what's the root cause and how to avoid it for better performance.

  | 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874033217536, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874036756481, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874038394882, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874040360963, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874041344004, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874051829765, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874059694086, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874070835207, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874079682568, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874083614729, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874097049610, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874112122891, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874121953292, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874137026573, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874146529294, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874151772175, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874164224016, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874166517777, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874173399058, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874181591059, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874187816980, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874194698261, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874203217942, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874205511703, 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 , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 13, 0, NULL, N'C', 39832874216980504, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | 

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4163275#4163275

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4163275



More information about the jboss-user mailing list