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

[gary.c.chen]

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