Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Having set Snapshot Isolation Level and Read Committed Snapshot on db , you do NOT need to have Select-Query-Hint: NOLOCK in ar.cfg From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of ravi rai Sent: Monday, October 08, 2012 10:25 PM To: arslist@ARSLIST.ORG Subject: Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver ** Howard, ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT Turning on these help Read operations retrieve a consistent snapshot of the database. SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa). SELECT statements can access the last committed value of the row, while other transactions update the row without being blocked. Fewer deadlocks and lock escalations occur. Fewer locks required by a transaction occur, which reduces the system overhead required to manage locks. Please refere the below white paper for othet Performance tuning options BMC Remedy AR System Server 7.6- Performance Tuning for Business Service Management Regards Ravi Rai Remedy Consultant HCL-America Date: Mon, 8 Oct 2012 12:44:19 -0400 From: hbr4...@gmail.com Subject: Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORG ** Ravi, We are on SP3 of the ARserver and SP2 of ITSM. I have asked my DB to look at the two snapshot values you talked about. By the way what should they be to prevent this type of issue. Thanks again and take care, Howard Sent from one of Howard's iPads On Oct 8, 2012, at 11:29 AM, ravi rai ravira...@hotmail.commailto:ravira...@hotmail.com wrote: ** Howard Which SP you are on 7604 Overview console 7604 default installation is single threaded causing perfomance issues. BMC has a hot fix for this. Also check SNAPSHOT ISOLATION and READ COMMIT SNAPSHOT value on DB Side Regards Ravi Rai Date: Mon, 8 Oct 2012 10:51:59 -0400 From: howard.rich...@coxinc.commailto:howard.rich...@coxinc.com Subject: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard image001.gifhttp://www.coxenterprises.com/ Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.commailto:howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) image002.pngimage003.pnghttp://innovation.coxenterprises.com/ideas/Badge%20Library/Badge20-Lg.pngimage004.pnghttp://innovation.coxenterprises.com/ideas/Badge%20Library/Badge50-Lg.png Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard [cid:image001.gif@01CDA542.A1DA1120]http://www.coxenterprises.com/ Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.commailto:howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) [cid:image002.png@01CDA542.A1DA1120][cid:image003.png@01CDA542.A1DA1120]http://innovation.coxenterprises.com/ideas/Badge%20Library/Badge20-Lg.png[cid:image004.png@01CDA542.A1DA1120]http://innovation.coxenterprises.com/ideas/Badge%20Library/Badge50-Lg.png Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are inline: image001.gifinline: image002.pnginline: image003.pnginline: image004.png
Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Hi, I'm no SQL Server expert ;) But you do have a snapshot functionnality that may be the source of your problems. Disabling or at least having it tuned could also benefit the whole application. You can use WITH NOLOCK with your select statement to force it at run time. You have the equivalent in AR System configuration with Select-Query-Hint: NOLOCK in ar.cfg, see configuration guide. HTH On Mon, Oct 8, 2012 at 4:51 PM, Richter, Howard (CEI - Atlanta) howard.rich...@coxinc.com wrote: ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Howard, When we performed the same upgrade, ironically from same app/db versions to same app/db versions, Part of the upgrade recommended running some commands to prevent deadlocksdid you by chance run those commands? You should be able to check the db guide for 7.6.04, or even the what's new. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Richter, Howard (CEI - Atlanta) Sent: Monday, October 08, 2012 8:52 AM To: arslist@ARSLIST.ORG Subject: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard Description: Description: Description: Description: Cox Enterprises, Inc http://www.coxenterprises.com/ Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.com mailto:howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) Description: Description: Description: http://innovation.coxenterprises.com/ideas/Badge%20Library/BadgeA-Md.pngDescription: Description: Description: http://innovation.coxenterprises.com/ideas/Badge%20Library/Badge20-Md.png http://innovation.coxenterprises.com/ideas/Badge%20Library/Badge20-Lg.png Description: Description: Description: http://innovation.coxenterprises.com/ideas/Badge%20Library/Badge50-Md.png http://innovation.coxenterprises.com/ideas/Badge%20Library/Badge50-Lg.png Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Howard Which SP you are on 7604 Overview console 7604 default installation is single threaded causing perfomance issues. BMC has a hot fix for this. Also check SNAPSHOT ISOLATION and READ COMMIT SNAPSHOT value on DB Side Regards Ravi Rai Date: Mon, 8 Oct 2012 10:51:59 -0400 From: howard.rich...@coxinc.com Subject: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORG ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are attachment: image001.gifattachment: image002.pngattachment: image003.pngattachment: image004.png
Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Ravi, We are on SP3 of the ARserver and SP2 of ITSM. I have asked my DB to look at the two snapshot values you talked about. By the way what should they be to prevent this type of issue. Thanks again and take care, Howard Sent from one of Howard's iPads On Oct 8, 2012, at 11:29 AM, ravi rai ravira...@hotmail.com wrote: ** Howard Which SP you are on 7604 Overview console 7604 default installation is single threaded causing perfomance issues. BMC has a hot fix for this. Also check SNAPSHOT ISOLATION and READ COMMIT SNAPSHOT value on DB Side Regards Ravi Rai Date: Mon, 8 Oct 2012 10:51:59 -0400 From: howard.rich...@coxinc.com Subject: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORG ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard image001.gif Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) image002.pngimage003.pngimage004.png Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Howard, ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT Turning on these help Read operations retrieve a consistent snapshot of the database. SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa). SELECT statements can access the last committed value of the row, while other transactions update the row without being blocked. Fewer deadlocks and lock escalations occur. Fewer locks required by a transaction occur, which reduces the system overhead required to manage locks. Please refere the below white paper for othet Performance tuning options BMC Remedy AR System Server 7.6- Performance Tuning for Business Service Management Regards Ravi Rai Remedy Consultant HCL-America Date: Mon, 8 Oct 2012 12:44:19 -0400 From: hbr4...@gmail.com Subject: Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORG ** Ravi, We are on SP3 of the ARserver and SP2 of ITSM. I have asked my DB to look at the two snapshot values you talked about. By the way what should they be to prevent this type of issue. Thanks again and take care, Howard Sent from one of Howard's iPads On Oct 8, 2012, at 11:29 AM, ravi rai ravira...@hotmail.com wrote: ** Howard Which SP you are on 7604 Overview console 7604 default installation is single threaded causing perfomance issues. BMC has a hot fix for this. Also check SNAPSHOT ISOLATION and READ COMMIT SNAPSHOT value on DB Side Regards Ravi Rai Date: Mon, 8 Oct 2012 10:51:59 -0400 From: howard.rich...@coxinc.com Subject: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORG ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard image001.gif Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) image002.pngimage003.pngimage004.png Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are__attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver
Ravi, Thanks I will. Howard Sent from one of Howard's iPads On Oct 8, 2012, at 12:55 PM, ravi rai ravira...@hotmail.commailto:ravira...@hotmail.com wrote: ** Howard, ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT Turning on these help Read operations retrieve a consistent snapshot of the database. SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa). SELECT statements can access the last committed value of the row, while other transactions update the row without being blocked. Fewer deadlocks and lock escalations occur. Fewer locks required by a transaction occur, which reduces the system overhead required to manage locks. Please refere the below white paper for othet Performance tuning options BMC Remedy AR System Server 7.6- Performance Tuning for Business Service Management Regards Ravi Rai Remedy Consultant HCL-America Date: Mon, 8 Oct 2012 12:44:19 -0400 From: hbr4...@gmail.commailto:hbr4...@gmail.com Subject: Re: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG ** Ravi, We are on SP3 of the ARserver and SP2 of ITSM. I have asked my DB to look at the two snapshot values you talked about. By the way what should they be to prevent this type of issue. Thanks again and take care, Howard Sent from one of Howard's iPads On Oct 8, 2012, at 11:29 AM, ravi rai ravira...@hotmail.commailto:ravira...@hotmail.com wrote: ** Howard Which SP you are on 7604 Overview console 7604 default installation is single threaded causing perfomance issues. BMC has a hot fix for this. Also check SNAPSHOT ISOLATION and READ COMMIT SNAPSHOT value on DB Side Regards Ravi Rai Date: Mon, 8 Oct 2012 10:51:59 -0400 From: howard.rich...@coxinc.commailto:howard.rich...@coxinc.com Subject: Getting deadlock issue when querying data directly, from a MSSQL 2008 DB and on 7.6.4 ARserver To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG ** Good morning, afternoon and evening all, Before we upgraded to 7.6.4 (ITSM and Arserver) we were on 7.5.6 and ITSM 7.6.1, and was able to query the MSSQL 2005 database directly, to pull some reporting data back. Since upgrading to 7.6.4 (ITSM and ARserver) and going to MSSQL 2008, when our user load is high we are getting deadlock issue when querying data directly. When the user load is low (like 3am) we have no issues. Any ideas? Take care, Howard image001.gifhttp://www.coxenterprises.com/ Howard Richter, Remedy Administrator 6205 Peachtree Dunwoody Road, Atlanta, GA 30328-4524 Email = howard.rich...@coxinc.commailto:howard.rich...@coxinc.com Office = 678.645.4633, Cell = 404.226.2745 Cox Innovation Agent (CIA) image002.pngimage003.pnghttp://innovation.coxenterprises.com/ideas/Badge%20Library/Badge20-Lg.pngimage004.pnghttp://innovation.coxenterprises.com/ideas/Badge%20Library/Badge50-Lg.png Submit your idea today for a chance to earn a badge and be entered into a monthly drawing for a $10 gift card. Submit your idea: http://innovation.coxenterprises.com/ideas View your badges: http://innovation.coxenterprises.com/ideas/SitePages/yourbadges.aspx _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.comhttp://www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
